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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jorge PaulinoConnect With a Mentor IT 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
 
Jorge PaulinoIT Pro/DeveloperCommented:
You have a primary key field on the database, right?
0
 
CodeCruiserCommented:
Try removing the

dsGrid_Data.AcceptChanges()

line.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
CodeCruiserConnect With a Mentor Commented:
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
 
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 PaulinoConnect With a Mentor IT 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
All Courses

From novice to tech pro — start learning today.