GRMPH!

CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
@.description NVARCHAR(70),
@.jobTitleKey INT
) AS
UPDATE msl_JobTitle
SET Description = @.description
WHERE JobTitleKey = @.jobTitleKey
GO
Then I have some VB.NET code that runs it:
Private Sub fixJobTitles()
Dim cnstr As String =
ConfigurationSettings.AppSettings("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
Dim dt As New DataTable()
cmGet.CommandType = CommandType.Text
cmSave.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmGet)
Dim tx As New Transform()
With cmSave
.Parameters.Add("@.description", "")
.Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
End With
da.UpdateCommand = cmSave
Try
da.Fill(dt)
Catch exc As Exception
Console.WriteLine(exc.Message)
End Try
For Each dr As DataRow In dt.Rows
dr("Description") =
tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
Next
Try
da.Update(dt)
Catch exc As Exception
'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
Console.WriteLine(exc.Message)
End Try
End Sub
AM I RETARDED OR SOMETHING?!?!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldObviously!
How do you expect the dataadapter object to know which columns in your
datatable object to use to satisfy the parameters of the updatecommand
object.
You need to do some table/column mapping to bind the correct parameter to
the correct column.
Check out the overloads of the sqlparameter object constructor.
In addition, you datatable object will not have a schema until you have
'filled' it, so you won't be able to map the columns until after the fill.
You could, however, explicitly define the schema so that the datatable is
ready for column mapping at an earlier stage.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uswh4P9rFHA.332@.tk2msftngp13.phx.gbl...
> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||per BOL
Use caution when using this overload of the Add method to specify integer
parameter values. Because this overload takes a value of type Object, you
must convert the integral value to an Object type when the value is zero, as
the following C# example demonstrates.
parameters.Add("@.pname", Convert.ToInt32(0));
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||Mike,
I think you should set the sourceColumn for that parameter.
Using Parameters with a DataAdapter
http://msdn.microsoft.com/library/d...ataadapters.asp
AMB
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||You should not half to do any of that. What you have posted as code i used
daily. With out mapping the source column.
There is 1 thing that I have noticed with out mapping the source is that
your parameters order in your stored procedure must match your parameters
order in your code.
should be as so
dim cn as new sqlclient.sqlconnection("Provider string")
dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
cm.commandtype = commandtype.storedprocedure
cm.parameters.add("@.Parmname","parmvalue")<-- in order here
cn.open
cm.executenonquery
cn.close
that will work every time. I have never had a problem with not mapping the
source.
post your database class.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
> Mike,
> I think you should set the sourceColumn for that parameter.
> Using Parameters with a DataAdapter
> http://msdn.microsoft.com/library/d...ataadapters.asp
>
> AMB
> "Mike Labosh" wrote:
>|||Chris,
I do not know if you got the problem here. The code you posted has nothing
to do with the OP problem. He is using this command for the
SqlDataAdapter.UpdateCommand.

> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
That is not necessary because ado.net call the sp using named parameters by
default, and not by position as ado used to do it (that is the reason why ad
o
command object has a property NamedParameters). What should match is the nam
e
of the command parameter with the name of the sp parameter. See "Using
Parameters with a SqlCommand" in the following link.
Using Stored Procedures with a Command
http://msdn.microsoft.com/library/d...withcommand.asp
AMB
"Chris" wrote:

> You should not half to do any of that. What you have posted as code i used
> daily. With out mapping the source column.
> There is 1 thing that I have noticed with out mapping the source is that
> your parameters order in your stored procedure must match your parameters
> order in your code.
>
> should be as so
> dim cn as new sqlclient.sqlconnection("Provider string")
> dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
> cm.commandtype = commandtype.storedprocedure
> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
> cn.open
> cm.executenonquery
> cn.close
> that will work every time. I have never had a problem with not mapping the
> source.
> post your database class.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
>
>|||you are correct.. Sorry about that miss post. I did not read it correct.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1F7A7D17-B6DE-4203-834A-19D7E3226F62@.microsoft.com...
> Chris,
> I do not know if you got the problem here. The code you posted has nothing
> to do with the OP problem. He is using this command for the
> SqlDataAdapter.UpdateCommand.
>
> That is not necessary because ado.net call the sp using named parameters
> by
> default, and not by position as ado used to do it (that is the reason why
> ado
> command object has a property NamedParameters). What should match is the
> name
> of the command parameter with the name of the sp parameter. See "Using
> Parameters with a SqlCommand" in the following link.
> Using Stored Procedures with a Command
> http://msdn.microsoft.com/library/d...withcommand.asp
>
> AMB
> "Chris" wrote:
>