Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update | Delete Rows using DataAdapter.Update()

Posted on 2006-05-19
7
Medium Priority
?
405 Views
Last Modified: 2010-04-23
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!
0
Comment
Question by:billymcqueary
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:proten
ID: 16719870
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
 

Author Comment

by:billymcqueary
ID: 16720217
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
 
LVL 5

Expert Comment

by:proten
ID: 16720268
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:billymcqueary
ID: 16720530
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
 
LVL 5

Accepted Solution

by:
proten earned 2000 total points
ID: 16720622
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
 

Author Comment

by:billymcqueary
ID: 16720712
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
 
LVL 5

Expert Comment

by:proten
ID: 16721470
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question