0

Once again i am turning to you for help. I am a little stuck when trying to save data entered in a DataGridView back to the SQL table.

I have followed a number of posts but just cant seam to figure it out

I declare the following variables globally on the form

Dim SQLAdaptor As New SqlClient.SqlDataAdapter
Dim Con As New SqlClient.SqlConnection
Dim builder As SqlClient.SqlCommandBuilder

I call this when the form loads

Private Sub SetTicketList()

     Con.ConnectionString = CropTrackMod.strConn
     SQLAdaptor.SelectCommand = New SqlClient.SqlCommand("SELECT StockRef, Weight, EstimatedPrice, EstimatedPrice, DespatchedQuantity, EstimatedTransport, EstimatedLineTotal FROM TicketDetail", Con)
     builder = New SqlClient.SqlCommandBuilder(SQLAdaptor)
     Con.Open()

     Dim myTable As DataTable = New DataTable
     SQLAdaptor.Fill(myTable)

     dgvTicketDetail.DataSource = myTable   
End Sub

I call this when the user leaves a row on the data grid view which should save back to the sql table

Private Sub dgvTicketDetail_RowLeave
    ' at grid save'

    Dim myTable = CType(dgvTicketDetail.DataSource, DataTable)
    SQLAdaptor.Update(myTable)
End Sub

When the form is loaded the data grid view is populated with the correct columns so i think that the first part is ok. The problem comes when saving the data back. When I run it the first time there is no error message. When I try a second row I get the following error:

Update requires a valid InsertCommand when passed DataRow collection with new rows.

From what I can figure the problem is generated because there is no update command set on the data adaptor.

Chris
  • 8,527
  • 10
  • 34
  • 51
PowerMan2015
  • 1,307
  • 5
  • 19
  • 40
  • 1
    The CommandBuilder should generate the UPDATE/INSERT and DELETE command for a single table select statement like yours. Do you change something on the variable SQLAdaptor after the first call? Does your table have a primary key defined and returned by the select statement? – Steve Sep 03 '13 at 19:33
  • The sqladaptor is only referenced in the above two subs once as the form loads and secondly as the update command is called. The table does have a primary key but this is not called in the select statement as i dont want the column to show – PowerMan2015 Sep 03 '13 at 19:40
  • And the missing primary key is your problem because the SqlCommandBuilder doesn't 'build' the INSERT and UPDATE commands for you See the [REMARKS](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx) on MSDN references – Steve Sep 03 '13 at 19:42
  • i have changed my select statement to include the "ID" column which is set as my primary key and i am still getting the same results. Is there something else that i need to do? – PowerMan2015 Sep 03 '13 at 19:48
  • That should work, but your statement `When I run it the first time there is no error message. When I try a second row I get the following error` is puzzling me. Something changes after the first run. I would try to put a breakpoint onf the Update line and when hit I would check if the SQLAdaptor has all the correct commands defined during the two steps – Steve Sep 03 '13 at 19:51
  • This is something i have looked at. I can confirm that the "UpdateCommand" is set to "nothing" when i put a breakpoint on the update command – PowerMan2015 Sep 03 '13 at 19:57
  • Then add another breakpoint just after the initialization of the SqlCommandBuilder and check if the SQLAdaptor has the correct commands. Just to find if it is a problem with the SqlCommandBuilder or something else. – Steve Sep 03 '13 at 20:05
  • Yey, prior to your last comment i noticed that the error had changed when adding the second row, indicating that i had a duplicate column in the select statement. I amended this an i am now able to add data to the database. However on the second row it adds the data in row 1 of the datagridview, third attempt it adds the second row and so on. Any Ideas? Thanks for your help on this Steve. Very much appreciated – PowerMan2015 Sep 03 '13 at 20:11
  • I have no idea of the reason of this behavior. As a last suggestion and if it is acceptable for you I would put a command button on the form and use it to update just one time and not at every RowLeave event. – Steve Sep 03 '13 at 20:30

0 Answers0