Update | Delete Rows using DataAdapter.Update()

I am trying to simply edit an SQL database using VB.NET 2005.   I feel like I am close, but I still get 2 errors I can't resolve.

*******************************

        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")

        'ERROR 1 IS HERE.  SOMETHING ABOUT ASKING FOR A NEW INSTANCE
        da.UpdateCommand.CommandText = "UPDATE TestTable SET FirstName = 'JOHNNY'"

        da.Update(ds)

        'ERROR 2 IS HERE.  This says 'Unable to find TableMapping or DataTable
        ds.AcceptChanges()


*****************************

Exact Code would be appreciated.  THANKS!
billymcquearyAsked:
Who is Participating?
 
protenCommented:
I think you are misunderstanding what the update statement does.  

All it does ist take the updates you've already made to the dataset (i.e add a new datarow, delete one, or update a cell) after the initial select and saves them to the db.  For that, if you have a primary key defined, you can use a sqlcommandbuilder to build the command statements (you need the primary key for the delete statement.)  

Since you have not changed the actual dataset your code will run, but no actual updates will be made.

What you are doing is a simple sql statement.  There is no need to first read the data, just use a  would work very simply like this:

                cmd = New SqlCommand("UPDATE TestTable SET FirstName = 'JOHNNY' WHERE LastName = 'Jones'", conn)
                cmd.ExecuteNonQuery()

0
 
protenCommented:
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()
0
 
billymcquearyAuthor Commented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
protenCommented:
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.
0
 
billymcquearyAuthor Commented:
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!
0
 
billymcquearyAuthor Commented:
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!!!
0
 
protenCommented:
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.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.