Problem with Update Adapter

[Steve Murphy]

I'm new to .NET, and I'm having problems creating an Adapter update command
for a simple data set. I'm using the following code which compiles and runs,
but does not update the database.

This is just a barebones test using the Northwind database. Since this is a
learning exercies, I'm not worried about concurrency at this time.
I'm updating the database from bound textboxes. The data table contains one
row in the dataset. The connection, adapter, and dataset are already
created.

Thanks in advance.



// Create and configure command object

SqlCommand cmdUpdateCust = cnCust.CreateCommand();

cmdUpdateCust.CommandText = "UPDATE Customers SET CompanyName =
@NewCompanyName " +
"WHERE CustomerID = @OldCustomerID";

cmdUpdateCust.Parameters.Add(new SqlParameter("@OldCustomerID",
SqlDbType.NChar, 5, "CustomerID"));
cmdUpdateCust.Parameters.Add(new SqlParameter("@NewCompanyName",
SqlDbType.NVarChar, 40, "CompanyName"));

cmdUpdateCust.Parameters["@OldCustomerID"].SourceVersion =
DataRowVersion.Original;
cmdUpdateCust.Parameters["@NewCompanyName"].SourceVersion =
DataRowVersion.Proposed;

daCust.UpdateCommand = cmdUpdateCust;


// Push the update to the datebase

cnCust.Open();
int NumRows = daCust.Update(dsCust.Tables["Customers"]);
cnCust.Close();

 

[W.G. Ryan eMVP]

Assuming you're getting no exceptions- I'd verify first that you have
changes... Right before you call Update, insert
Debug.Assert(dsCust.HasChanges()); //If the assertion fails, it's b/c
there's no changess to send back to the db. Remember that the way the
adapter works is it iterates the Rows collection, checking the rowstate-
mapping the rowstate to the respective command (ie Modified - would map to
your update command). and then fires the command accordingly.

BTW, I'd let the adapter handle opening and closing the connection - if not,
then wrap it in a Try/catch/Finally block so you know it gets closed

Let me know if you have changes or not and we'll take it from there.

 

[Steve Murphy]

The assertion fails.

Assuming you're getting no exceptions- I'd verify first that you have
changes... Right before you call Update, insert
Debug.Assert(dsCust.HasChanges()); //If the assertion fails, it's b/c
there's no changess to send back to the db. Remember that the way the
adapter works is it iterates the Rows collection, checking the rowstate-
mapping the rowstate to the respective command (ie Modified - would map to
your update command). and then fires the command accordingly.

BTW, I'd let the adapter handle opening and closing the connection - if not,
then wrap it in a Try/catch/Finally block so you know it gets closed

Let me know if you have changes or not and we'll take it from there.


--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I'm new to .NET, and I'm having problems creating an Adapter update command
for a simple data set. I'm using the following code which compiles and runs,
but does not update the database.

This is just a barebones test using the Northwind database. Since this

is

a
learning exercies, I'm not worried about concurrency at this time.
I'm updating the database from bound textboxes. The data table contains one
row in the dataset. The connection, adapter, and dataset are already
created.

Thanks in advance.



// Create and configure command object

SqlCommand cmdUpdateCust = cnCust.CreateCommand();

cmdUpdateCust.CommandText = "UPDATE Customers SET CompanyName =
@NewCompanyName " +
"WHERE CustomerID = @OldCustomerID";

cmdUpdateCust.Parameters.Add(new SqlParameter("@OldCustomerID",
SqlDbType.NChar, 5, "CustomerID"));
cmdUpdateCust.Parameters.Add(new SqlParameter("@NewCompanyName",
SqlDbType.NVarChar, 40, "CompanyName"));

cmdUpdateCust.Parameters["@OldCustomerID"].SourceVersion =
DataRowVersion.Original;
cmdUpdateCust.Parameters["@NewCompanyName"].SourceVersion =
DataRowVersion.Proposed;

daCust.UpdateCommand = cmdUpdateCust;


// Push the update to the datebase

cnCust.Open();
int NumRows = daCust.Update(dsCust.Tables["Customers"]);
cnCust.Close();

 

[W.G. Ryan eMVP]

Then none of the changes are taking - you may need to call EndCurrentEdit if
you're using a BindingManager --- manually set a row/column value and see if
that works.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

The assertion fails.

Assuming you're getting no exceptions- I'd verify first that you have
changes... Right before you call Update, insert
Debug.Assert(dsCust.HasChanges()); //If the assertion fails, it's b/c
there's no changess to send back to the db. Remember that the way the
adapter works is it iterates the Rows collection, checking the rowstate-
mapping the rowstate to the respective command (ie Modified - would map to
your update command). and then fires the command accordingly.

BTW, I'd let the adapter handle opening and closing the connection - if not,
then wrap it in a Try/catch/Finally block so you know it gets closed

Let me know if you have changes or not and we'll take it from there.


--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I'm new to .NET, and I'm having problems creating an Adapter update command
for a simple data set. I'm using the following code which compiles and runs,
but does not update the database.

This is just a barebones test using the Northwind database. Since this

is

a
learning exercies, I'm not worried about concurrency at this time.
I'm updating the database from bound textboxes. The data table

contains
one

row in the dataset. The connection, adapter, and dataset are already
created.

Thanks in advance.



// Create and configure command object

SqlCommand cmdUpdateCust = cnCust.CreateCommand();

cmdUpdateCust.CommandText = "UPDATE Customers SET CompanyName =
@NewCompanyName " +
"WHERE CustomerID = @OldCustomerID";

cmdUpdateCust.Parameters.Add(new SqlParameter("@OldCustomerID",
SqlDbType.NChar, 5, "CustomerID"));
cmdUpdateCust.Parameters.Add(new SqlParameter("@NewCompanyName",
SqlDbType.NVarChar, 40, "CompanyName"));

cmdUpdateCust.Parameters["@OldCustomerID"].SourceVersion =
DataRowVersion.Original;
cmdUpdateCust.Parameters["@NewCompanyName"].SourceVersion =
DataRowVersion.Proposed;

daCust.UpdateCommand = cmdUpdateCust;


// Push the update to the datebase

cnCust.Open();
int NumRows = daCust.Update(dsCust.Tables["Customers"]);
cnCust.Close();

 

[Steve Murphy]

I set up the binding using simple DataBindings.Add calls like this:

txtCompany.DataBindings.Add(new Binding("Text", dsCust,
"Customers.CompanyName"));

Is use of a BindingManager required?

 

[Steve Murphy]

Then none of the changes are taking - you may need to call EndCurrentEdit
if

you're using a BindingManager --- manually set a row/column value and see if
that works.

If I call EndCurrentEdit on the BindingManager, I get the following
exception when I call the adapter update method:

An unhandled exception of type 'System.Data.VersionNotFoundException'
occurred in system.data.dll
Additional information: There is no Proposed data to access.

I'm loading a single record at a time into the data set, to get the most
current version for data entry. My textbox controls are bound to that one
record in that dataset.

When I attempt to update the data table after modifications, the changes are
not recognized. Although I can see the change between DataRowVersions, the
RowState remains Unchanged.

Any ideas?

Thanks,
Steve Murphy