SQL Stored Procedure not updating.

  • Thread starter Thread starter Kevin Hodgson
  • Start date Start date
K

Kevin Hodgson

Hoping someone can provide some insight into what I'm doing wrong here.

I have a Win.Form with a bunch of text controls, bound to a Dataset
(DsCustomerInfo1), and a ComboBox bound to the ClientID Field of that
Dataset (DsCustomerInfo1.cw_clientinfo.ClientID) so that I can select a
client ID, and get all the text boxes filled with data for the selected
client. That all works beautifully.

I edit the address field for instance, and hit my Update Client Button.


Private Sub btnUpdateClient_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdateClient.Click

Me.connExpress.Open() 'Open the connection

Try

Debug.Assert(Me.DsCustomerInfo1.HasChanges, "No Changes Present")

Me.daCustomerInfo.Update(Me.DsCustomerInfo1, "CW_customerinfo")

Catch ex As Exception

MsgBox(ex.ToString, MsgBoxStyle.Critical, "SQL Error")

End Try

Me.connExpress.Close() 'Close the connection

End Sub

The Assert says that there are no changed Rows.

I have a breakpoint on the daCustomerInfo.Update line, and I have a watch on

CType(CType(DsCustomerInfo1.cw_customerinfo.rowCollection.Item(0),System.Dat
a.DataRow),SupportTool.dsCustomerInfo.CW_customerinfoRow) Which is the row
I'm changing.

The Address field shows my new edited address, but the Rowstate property is
Unchanged.

I can select a new client from the Combobox, and come back to this one, and
my new edited address is still displayed, but it hasn't been reflected back
to the SQL Database. If I log out of the application and come back in, I'm
back to my original address as stored in the SQL DB.
 
If HasChanges is false, then calling update isn't going to do anything. All
Update does is loop through the table looking for changed rows to send back
to the db. The inner workings of Update and HasChanges have much in common
in that regard.

I think the probem may be that you need to call EndCurrentEdit on the
bindingContext/BindingManager Base b/c it may be thinking that the row is in
edit mode (BeginEdit/EndEdit for a datagrid) which won't push back the
changes.

The problme isn't the proc if HasChanges is false, it's not even getting
called.

-- http://www.knowdotnet.com/articles/efficient_pt4.html

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Ok, I tried adding:
Me.BindingContext(Me.DsCustomerInfo1.CW_customerinfo).EndCurrentEdit()
Me.daCustomerInfo.Update(Me.DsCustomerInfo1, "CW_customerinfo")

Seems to have worked, the Rowstate is now "Modified", but I'm getting a
different error about some of my original parameters being missing. I'll
have to try and sort those out now.

Thanks for the help.
 
Now I'm getting an error on the Update for each of the
@Original_<Parameters>. in my query. Aren't these values set/tracked by the
data binding, or do I need to set each of these Parameters when I do my
update? Am I going to need to set all the new parameters manually as well?
 
Here's my error and the definition for the Stored Procedure.

System.Data.SqlClient.SqlException: Procedure 'cw_customerupdate' expects
parameter'@Original_EndUserID', which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at MyApp.Form.btnUpdateClient_Click(Object sender, EventArgs e) in
\path\form.vb: line 1154

This is my call to do the update:

Me.BindingContext(Me.DsCustomerInfo1.CW_customerinfo).EndCurrentEdit()
Me.daCustomerInfo.Update(Me.DsCustomerInfo1, "CW_customerinfo")

This is my declaration for the Stored Procedure, which was setup by the
Designer when I added the SqlDataAdapter to my Form.
Me.SqlUpdateCommand1.CommandText = "[cw_customerupdate]"
Me.SqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlUpdateCommand1.Connection = Me.connExpress
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EndUserID", System.Data.SqlDbType.Int,
4, "EndUserID"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ClientNumber",
System.Data.SqlDbType.VarChar, 50, "ClientNumber"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyName",
System.Data.SqlDbType.VarChar, 50, "CompanyName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ShortName",
System.Data.SqlDbType.VarChar, 10, "ShortName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Address",
System.Data.SqlDbType.VarChar, 255, "Address"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.VarChar,
50, "City"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProvinceOrState",
System.Data.SqlDbType.VarChar, 20, "ProvinceOrState"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Country",
System.Data.SqlDbType.VarChar, 50, "Country"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PostalCode",
System.Data.SqlDbType.VarChar, 20, "PostalCode"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone1", System.Data.SqlDbType.VarChar,
40, "Phone1"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone2", System.Data.SqlDbType.VarChar,
40, "Phone2"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FaxNumber",
System.Data.SqlDbType.VarChar, 40, "FaxNumber"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.VarChar, 30, "FirstName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.VarChar, 30, "LastName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EmailAddress",
System.Data.SqlDbType.VarChar, 50, "EmailAddress"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Active", System.Data.SqlDbType.Int, 4,
"Active"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DistributorID",
System.Data.SqlDbType.Int, 4, "DistributorID"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_EndUserID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Active",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_DistributorID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Address",
System.Data.SqlDbType.VarChar, 255))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_City",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ClientNumber",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CompanyName",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Country",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_EmailAddress",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_FaxNumber",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_FirstName",
System.Data.SqlDbType.VarChar, 30))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastName",
System.Data.SqlDbType.VarChar, 30))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Phone1",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Phone2",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_PostalCode",
System.Data.SqlDbType.VarChar, 20))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProvinceOrState",
System.Data.SqlDbType.VarChar, 20))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ShortName",
System.Data.SqlDbType.VarChar, 10))
 
Hmm, almost looks like a column mapping is missing... Here's typically what
it will look like
"@Original_EmplNum", System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EmplNum", System.Data.DataRowVersion.Original, Nothing))

Where "EmplNum" is the colummapping and the DataRowVersion.Original maps
back to the original value of the row for the concurrency test. It looks
like it was reconfigured and the mapping were left out. It should have
warned you though. Run back through the wizard, choose existing stored
procedures, and make sure all the fields are mapped for you Insert Command.
If not it should throw up a warning..

I can recreate this exactly if i reconfigure and don't specify the
columnMappings, but if I do, all is well. The First pass through the Wizard
takes care of it for you, but I'm guessing this was done more than once.

Let me know.

Bill
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
Kevin Hodgson said:
Here's my error and the definition for the Stored Procedure.

System.Data.SqlClient.SqlException: Procedure 'cw_customerupdate' expects
parameter'@Original_EndUserID', which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at MyApp.Form.btnUpdateClient_Click(Object sender, EventArgs e) in
\path\form.vb: line 1154

This is my call to do the update:

Me.BindingContext(Me.DsCustomerInfo1.CW_customerinfo).EndCurrentEdit()
Me.daCustomerInfo.Update(Me.DsCustomerInfo1, "CW_customerinfo")

This is my declaration for the Stored Procedure, which was setup by the
Designer when I added the SqlDataAdapter to my Form.
Me.SqlUpdateCommand1.CommandText = "[cw_customerupdate]"
Me.SqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlUpdateCommand1.Connection = Me.connExpress
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EndUserID", System.Data.SqlDbType.Int,
4, "EndUserID"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ClientNumber",
System.Data.SqlDbType.VarChar, 50, "ClientNumber"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyName",
System.Data.SqlDbType.VarChar, 50, "CompanyName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ShortName",
System.Data.SqlDbType.VarChar, 10, "ShortName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Address",
System.Data.SqlDbType.VarChar, 255, "Address"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.VarChar,
50, "City"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProvinceOrState",
System.Data.SqlDbType.VarChar, 20, "ProvinceOrState"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Country",
System.Data.SqlDbType.VarChar, 50, "Country"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PostalCode",
System.Data.SqlDbType.VarChar, 20, "PostalCode"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone1", System.Data.SqlDbType.VarChar,
40, "Phone1"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone2", System.Data.SqlDbType.VarChar,
40, "Phone2"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FaxNumber",
System.Data.SqlDbType.VarChar, 40, "FaxNumber"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.VarChar, 30, "FirstName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.VarChar, 30, "LastName"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EmailAddress",
System.Data.SqlDbType.VarChar, 50, "EmailAddress"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Active", System.Data.SqlDbType.Int, 4,
"Active"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DistributorID",
System.Data.SqlDbType.Int, 4, "DistributorID"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_EndUserID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Active",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_DistributorID",
System.Data.SqlDbType.Int, 4))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Address",
System.Data.SqlDbType.VarChar, 255))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_City",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ClientNumber",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CompanyName",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Country",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_EmailAddress",
System.Data.SqlDbType.VarChar, 50))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_FaxNumber",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_FirstName",
System.Data.SqlDbType.VarChar, 30))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastName",
System.Data.SqlDbType.VarChar, 30))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Phone1",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Phone2",
System.Data.SqlDbType.VarChar, 40))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_PostalCode",
System.Data.SqlDbType.VarChar, 20))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProvinceOrState",
System.Data.SqlDbType.VarChar, 20))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ShortName",
System.Data.SqlDbType.VarChar, 10))

William Ryan eMVP said:
If a parameter is missing then you need to look to the stored proc or your
update command and see what its expecting. What is the exact error you are
getting?

--

W.G. Ryan, eMVP

by
the
well?
Click to expand...
Click to expand...
 
Back
Top