Update | Delete Rows using DataAdapter.Update()

Try this:

        Dim conn As New SqlClient.SqlConnection("Data Source=MCQDC2; Initial Catalog=INTERNALASP; User Id=sa; Password=br0thers;")
        Dim ds As New DataSet
        Dim da As New SqlClient.SqlDataAdapter

        da = New SqlClient.SqlDataAdapter("SELECT * FROM TestTable", conn)
        da.Fill(ds, "RS")

        Label1.Text = ds.Tables("RS").Rows(0).Item("FirstName")

        Dim cmd As New SqlCommand ("UPDATE TestTable SET FirstName = 'JOHNNY'", conn)
        da.UpdateCommand = cmd

        da.Update(ds.Tables("RS"))

        ds.AcceptChanges()

I don't get any execution errors with that code, but it is not affecting any values in the database.  I double checked and tried changing my SQL statement to make sure all was well.  What do I do different for an INSERT and DELETE Command.

I would assume you would want to add parameters to your commands for the "WHERE" sql statement.  

What exactly are you trying to update?

What are you trying to do altogether?  Is it as simple as the statements above?

If you have a primary key defined on the sql table you can use a sqlcommandbuilder to build the statements.  But that all depends on what you are trying to do.

Give me more info and I'll try to help!

G'Luck.

I tried it with:

***
        Dim cmd As New SqlCommand ("UPDATE TestTable SET FirstName = 'JOHNNY' WHERE LastName = 'Jones'", conn)
***

FirstName is my primary key.  I have a lot of experience with SQL statements, so if I can get a simple INSERT and UPDATE and DELETE statement to run on my database, I'll be home free.  I am used to ASP and scripting style VB when working with databases.

I appreciate it!

PERFECT!!!!!!!!!!!!!!!

That is the simple answer I was looking for.  I couldn't believe that with all the fancy, time-saving features on VS2005, that a simple database command would be so ridiculously hard.  Is the only real advantage to doing it the other way that the software will write & execute your SQL commands for you?

Thanks again!!!

There are major advantages for the update statement.

When you have update lots of rows, you dont have to call the database on each update, add, or delete.  You simply read the data into a dataset. Then you do whatever processing to update, add, and delete different data and criteria on hundreds (or thousands) of rows. When you are all done, you can use one line of code to save all the changes to the underlying database.  

Before the update statement you can either build the pertinent commands yourself using parameters for the where statement values or use the sqlcommandbuilder to build them for you.

The dataset is an in-memory version of you database data.  Any changes that are made can be accessed and changed or abandoned in your code before the database is actually updated.  When using the dataset you can see the state of a row, i.e. if a cell was modified you can access the original value as well the changes.  You can manipulate the data this way until the actual update statement is used.

There are many major benefits related to this method, too many to write here.