How to update dataset?

[CM]

Hi there:
I am working on a dataset, changed its rows, however, when I try to update
the changes to database, using the following code (Dataset1 is untyped
dataset):
sqlDbDataAdapter1.Update(Dataset1, "Customers")
but got the error:
---------------------------------------
An unhandled exception of the type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Update requires a valid UpdateCommand when passed
DataRow collection with modified rows.
---------------------------------------
What I am missing?

Thanks!

CM

 

[Terry Burns]

Use the following code for more info

Try

sqlDbDataAdapter1.Update(Dataset1, "Customers")

Catch ex as System.InvalidOperationException

Messagebox.show( ex.message )

End Try

 

[CM]

Thanks for reply!
I tried your code, the error message is: "Update requires a valid
UpdateCommand when passed DataRow collection with modified rows."

I tired adding this line before update:
Dataset1.AcceptChanges()

sqlDbDataAdapter1.Update(Dataset1, "Customers")

Then this time no error, but the database was not updated.

Any more help?

CM

 

[William Ryan]

Did you use the DataAdapter configuratino wizard or did
you use a command builder?

It doesn't look like you have the udpate command
set...remember you only need a SELECT command to populate
a DataTable/Set, but you need logic to handle inserts,
updates etc.

I'm guessing there's no commandbuilder in place and you
may not have used the configuration wizard (which writes
it for you if it can).

Either way, the solution will be simple, just let me know.

Good Luck

Bill

(e-mail address removed)
www.knowdotnet.com

-----Original Message-----
Hi there:
I am working on a dataset, changed its rows, however, when I try to update
the changes to database, using the following code (Dataset1 is untyped
dataset):
sqlDbDataAdapter1.Update(Dataset1, "Customers")
but got the error:

type 'System.InvalidOperationException'

 

[CM]

Thank for the reply!
But I still not clear how to apply change made in a dataset to database via
dataAdapter.
There are thousands of rows in the dataset were modified, I need apply these
change to database.

CM

 

[Michael Lang]

You didn't specify an UpdateCommand as the error states!

The DataAdapter has no magical way to know how to update your datasource.
IE.

sample UpdateCommand.CommandText...
"UPDATE Contact SET "
+ "FirstName = @FirstName"+ ", "
+ "LastName = @LastName"+ " WHERE ContactID = @ContactID"

sample DeleteCommand.CommandText...
"DELETE FROM Contact WHERE ContactID = @ContactID"

sample InsertCommand.CommandText...
"INSERT INTO Contact("
+ "ContactID, FirstName, LastName"
+ ") VALUES ("
+ "@ContactID, @FirstName, @LastName"
+ ")"

When you create your DataAdapter you must specify all 4 CRUD commands
(Create, Read, Update, Delete)

The constructor can take in the SelectCommand which is what you've probably
done. But you must set the UpdateCommand, DeleteCommand, and InsertCommand
properties separately. If you don't want to create the contents of the
commands on your own you could use the SqlCommandBuilder or
OdbcCommandBuilder. However, I don't recommend using it. Just try it once
and compare the CommandText to the samples I provide above!

 

[CM]

Could you please show me how to use sqlCommand to update a dataset to
database instead of excute a sql query?
Thanks!
CM

 

[CM]

Hi, William:
I did not use commandbuilder, I just want to understand how hard code
working.
I think I may need give all code to make my problem clear:

-------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
system.EventArgs) Handles Button2.Click
Dim strCon As String
Dim dbCon As Data.SqlClient.SqlConnection
Dim dbAdapter As Data.SqlClient.SqlDataAdapter
Dim DataSet1 As Data.DataSet
Dim strSql As String
Dim iRowCount As Integer, k As Integer

strCon = "server=PIV;database=Client;uid=cm;pwd=abc"
dbCon = New SqlConnection(strCon)
dbCon.Open()
strSql = "Select * From Customers "
dbAdapter = New SqlClient.SqlDataAdapter(strSql, dbCon)
DataSet1 = New Data.DataSet()
dbAdapter.Fill(DataSet1, "Customers")
iRowCount = DataSet1.Tables("Customers").Rows.Count - 1
For k = 0 To iRowCount
ProcessRow (DataSet1.Tables("Customers").Rows(k).Item("FLNameTitle"))
Next k
DataSet1.AcceptChanges()
Try
dbAdapter.Update(DataSet1, "Customers")
Catch ex As System.InvalidOperationException
MessageBox.Show(ex.Message)
End Try
End Sub

-----------------------------------

Now there is no error message, but no update on database.

What's wrong?

CM

 

[Michael Lang]

Once you've defined the commands all you do is call update on your
dataAdapter, passing in the dataset that contains the changes. But that is
what you are doing in your original code sample. What you are missing is
that you did not define the actual command to execute when a row has a
change in it!

SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.CommandText = "UPDATE Contact SET "
+ "FirstName = @FirstName"+ ", "
+ "LastName = @LastName"+ " WHERE ContactID = @ContactID"
.... also define "SqlParameter" objects for each parameter (IE. @FirstName)

sqlDbDataAdapter1.UpdateCommand = cmdUpdate;

 

[Michael Lang]

If you want to see how it goes in context of an entire application see my
code generator project in my signature below... use "Template05c" to
generate code for your SQL database.

But here is a summary...
=====================================================
SqlConnection _myConnection; //assume you've already instatiated earlier

SqlCommand cmdSelect = new SqlCommand(
"SELECT ContactID, FirstName, LastName FROM Contact",
_myConnection); //this could be a stored procedure instead...
// cmdSelect.CommandType = CommandType.StoredProcedure
// uncomment above line if calling a stored procedure instead
// default is "CommandType.Text"
SqlDataAdapter _da = new SqlDataAdapter(cmdSelect);

// create insert command
SqlCommand cmdInsert = new SqlCommand();
cmdInsert.Connection = _myConnection;
cmdInsert.CommandText = "INSERT INTO Contact("
+ "ContactID, FirstName, LastName"
+ ") VALUES ("
+ "@ContactID, @FirstName, @LastName"
+ ")";
SqlParameter prm0 = new SqlParameter("@ContactID" ,
SqlDbType.UniqueIdentifier);
prm0.SourceColumn = "ContactID";
cmdInsert.Parameters.Add(prm0);
SqlParameter prm1 = new SqlParameter("@FirstName" , SqlDbType.NVarChar);
prm1.SourceColumn = "FirstName";
cmdInsert.Parameters.Add(prm1);
SqlParameter prm2 = new SqlParameter("@LastName" , SqlDbType.NVarChar);
prm2.SourceColumn = "LastName";
cmdInsert.Parameters.Add(prm2);

// set this command as the data adapters insert command
_da.InsertCommand = cmdInsert;
.... same steps for UpdateCommand, and DeleteCommand here ...

DataSet dsContainer = new DataSet();
_myConnection.Open();
_da.Fill(dsContainer);
_myConnection.Close();

.... bind dsContainer to your grid or Fill business objects with it, let user
or business objects edit dsContainer as desired ...
.... then later when database update is requsted ...

_da.Update(dsContainer); //this is all you need..
=====================================================

 

[CM]

Thanks for your help.
The last code is just I expected, but, actually, it is similar to what I am
using now (see above my code above). I modified the update line, but non is
working.

// this line does not update database
dbAdapter.Update(DataSet1, "Customers")

// this line caused an error: cannot find table
dbAdapter.Update(DataSet1)

// this line does not update database
dbAdapter.Update(DataSet1.Tables("Customers"))

Where is the problem?

BTW, I downloaded your source code (genDB_1_0.zip), but cannot open it
because the your VS' version is newer than mine.

Thanks!

CM

 

[Michael Lang]

The d/l includes a VS 2002 solution also... "GenDB.sln.old" Even if that
does not work, delete the solution file, and just copy the code files into a
new VS 2002 solution. The solution file doesn't store any important
information anyway (in this case)... I have not changed any solution
settings from the default solution.

Maybe your problem is something to do with TableMappings...

....
if (_da.TableMappings != null)
{
_da.TableMappings.Add("Table", "Contact");
}
_da.Fill(dsContainer);

When you fill a dataset, it does not set the name of the DataTable from the
SQL query string. I still don't have a full grasp on why, I just accept it.
You must specify the table names yourself using a TableMapping. Do not just
change the "TableName" directly. The DataSet indexes each DataTable by
their given name. So if you rename the DataTable, updates fail.

 

[tperovic]

Do you still have Dataset1.AcceptChanges() before the DataAdapter.Update()?
If so, delete that line. You don't need it because the DataAdapter will call
it before returning.

Here is what DataAdapter.Update() does:

- It scans the table(s) looking for added, deleted and updated rows
- If the row was added then it uses the InsertCommand to insert the new
row into the database
- If the row was deleted then it uses the DeleteCommand to delete the
row in the database
- If the row was updated then it uses the UpdateCommand to update the
database
- If all the added, deleted and updated rows have been successfully
processed then call AcceptChanges()

TP

 

[CM]

Thanks so far for your help!
Now, by checking the code generated by wizard, I realized the points you
mentioned. It is necessary to create a sqlUpdateCommand and assigned it to
the Adapter:

Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1

And there are a lot of things to do before running Adapter.Update such as
Me.SqlUpdateCommand1.CommandText ="Update TableName Set ..."

Me.SqlUpdateCommand1.Connection = Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ColumnName1",
System.Data.SqlDbType.VarChar, 30, "ColumnName1"))

.......

As my table actually has more than 40 columns to update, its not good to
write hard code for the above settings, I think its better to use wizard to
generate code (It will generate all insert, update, delete and select
command) and then delete those not in use.

Thanks for your source code, too!

Have a nice day!

CM

 

[CM]

Thanks so far for your reply! They are helpful.
I have got the answer, see my post followed Micheal.
Have a good day!
CM

 

[Michael Lang]

You are welcome. Check the project site back every once and a while. I am
working on a new set of templates for the generator that act properly for
tables that are indexed with int primary keys. The previous templates work
best with GUID primary keys on tables.