Save DatagridView Changes to Database

I have a windows forms application that has a DatagridView control that I am populating from an SQL Database.  I am using the following code to populate the DataGridView:

  Dim sSQL As String = "SELECT * FROM tblStatus WHERE REC_ID = '100'"

        Dim cn As New SqlConnection(....Connection String Info)
        daGrid_Data = New SqlDataAdapter(sSQL, cn)
        dsGrid_Data = New Data.DataSet

        daGrid_Data.TableMappings.Add("Table", "tblStatus")
        daEDI_Data.Fill(dsGrid_Data, "search_result")

        datGridView.DataSource = dsGrid_Data
        datGridView.DataMember = "search_result"

This works fine populating the grid.  I have the following code in the click event of a button on the form.

            dsGrid_Data.AcceptChanges()
            daGrid_Data.Update(dsGrid_Data, "search_result")

When the user deletes a row from the grid the delete row is not removed from the dataset and therefore not removed from the database.   I do i update the database to be the same as what is in the datagridview?
Mike_StevensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
Try this way: http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23292122.html
The example is for Access, but is the same for SQL (just change to the right classes)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jorge PaulinoIT Pro/DeveloperCommented:
You have a primary key field on the database, right?
0
CodeCruiserCommented:
Try removing the

dsGrid_Data.AcceptChanges()

line.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mike_StevensAuthor Commented:
1.) The table does a have a primary key

2.) I removed the "dsGrid_Data.acceptchanges".   Still does not save records
0
CodeCruiserCommented:
Confirm that the Update, Delete, and Insert commands are generated for the adapter.

Use this syntax

If IsNothing(daGrid_Data.DeleteCommand) Then
   Messagebox.Show("Delete command not generated.")
End If
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> The table does a have a primary key
You need to have a primary key! Check the example that I have showed you and use a SqlCommandBuilder.
0
Mike_StevensAuthor Commented:
I am getting the error message "Update requires valid delete command when passed datarow collection with deleted rows" so I am assuming the delete command has not been created.  How do i create it?
0
CodeCruiserCommented:
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> Use the command builder
Like I suggested already :)
0
CodeCruiserCommented:
>Like I suggested already :)
Exactly. Without an example though :-)
0
Mike_StevensAuthor Commented:
The actual SQL select statement I am using has a join to another table.   I changed it to a single simple select in the example for simpliity.   I added the following to my code and now i get the following message:  "Dynamic SQL Generation is not supported against multible base tables".   I am guessing i am getting this because of the two tables in the SELECT statement.   I guess that means i have to create the deletecommand without using a command builder?

Dim cmdBuilder As New SqlCommandBuilder(daGrid_Data)
daGrid_Data.DeleteCommand = cmdBuilder.GetDeleteCommand()
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> Exactly. Without an example though :-)
Check the link I provided http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23292122.html
Sorry Mike_Stevens for the off-topic ... just having fun on a monday :-)
0
Jorge PaulinoIT Pro/DeveloperCommented:
To use an SqlCommandBuilder and the way you want it, you cannot have tables joined and you must have a primary key on the table.
If you want to show information that way, you must check the changes that was made and updated manually.
0
CodeCruiserCommented:
@jpaulino
Missed that single line in the example. Too tired on this monday evening.


@Mike_Stevens
You would probably need to add the commands manually.

Here is an example

http://www.vb-helper.com/howto_net_update_without_primary_key.html
0
Jorge PaulinoIT Pro/DeveloperCommented:
@angelIII
I think that I and CodeCruiser have provided valid and correct support for this question.
0
CodeCruiserCommented:
Accepted: jpaulino
Assisted: CodeCruiser
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.