|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
GRMPH!!!!!!@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 Obviously!
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. Show quote "Mike Labosh" <mlab***@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)); Show quote "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/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp AMB Show quote "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. Show quote "Alejandro Mesa" <AlejandroM***@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/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.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 >> >> >> 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 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/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp AMB Show quote "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" <AlejandroM***@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/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.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 are correct.. Sorry about that miss post. I did not read it correct.
Show quote "Alejandro Mesa" <AlejandroM***@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. > >> 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 > 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/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.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" <AlejandroM***@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/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.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 >> >> >> >> >> >> >> >> >>  
Other interesting topics
|
|||||||||||||||||||||||
