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(....Connecti
daGrid_Data = New SqlDataAdapter(sSQL, cn)
dsGrid_Data = New Data.DataSet
daGrid_Data.TableMappings.
daEDI_Data.Fill(dsGrid_Dat
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_
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?
Dim sSQL As String = "SELECT * FROM tblStatus WHERE REC_ID = '100'"
Dim cn As New SqlConnection(....Connecti
daGrid_Data = New SqlDataAdapter(sSQL, cn)
dsGrid_Data = New Data.DataSet
daGrid_Data.TableMappings.
daEDI_Data.Fill(dsGrid_Dat
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_
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jorge Paulino
You have a primary key field on the database, right?
Try removing the
dsGrid_Data.AcceptChanges(
line.
dsGrid_Data.AcceptChanges(
line.
ASKER
1.) The table does a have a primary key
2.) I removed the "dsGrid_Data.acceptchanges
2.) I removed the "dsGrid_Data.acceptchanges
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
You need to have a primary key! Check the example that I have showed you and use a SqlCommandBuilder.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Use the command builder
Like I suggested already :)
Like I suggested already :)
>Like I suggested already :)
Exactly. Without an example though :-)
Exactly. Without an example though :-)
ASKER
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_D
daGrid_Data.DeleteCommand = cmdBuilder.GetDeleteComman
Dim cmdBuilder As New SqlCommandBuilder(daGrid_D
daGrid_Data.DeleteCommand = cmdBuilder.GetDeleteComman
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If you want to show information that way, you must check the changes that was made and updated manually.
@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
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
@angelIII
I think that I and CodeCruiser have provided valid and correct support for this question.
I think that I and CodeCruiser have provided valid and correct support for this question.
Accepted: jpaulino
Assisted: CodeCruiser
Assisted: CodeCruiser