Updating SQL database with datagridview contents

Question

Let me briefly state my problem and then show the code.

I want to extract data from a SQL database, display it using datagridview in Visual Basic, allow the user to alter the contents in the datagridview, and then post any changes back into the database. I have tried multiple approaches using advice from stackoverflow and other forums, but nothing works. Here is the approach that made the most sense to me, but again if there is a better or easier way to do it, I am all ears.

I have removed other parts of the code that are irrelevant. The code properly gets data from the database; puts it into datagridview and displays it. When I enter a cell to change contents, and then try to save the results back into the database, that is where I get the run-time error.

Imports System.IO
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class FrmRunTerm

    Private connectionString As String
    Private ControlResultsConnection As New SqlConnection
    Private ReadDS As New SqlDataAdapter
    Private ControlResultsDS As New DataSet


    Private Sub FrmRunTerm_Load(sender As Object, e As EventArgs) Handles MyBase.Load


        CboWeek.Items.Add("Week 1 - Tuesday, November 15, 2016")
        CboWeek.Items.Add("Week 2 - Tuesday, November 22, 2016")
        CboWeek.Items.Add("Week 3 - Tuesday, November 29, 2016")
        CboWeek.Items.Add("Week 4 - Tuesday, December 6, 2016")
        CboWeek.Items.Add("Week 5 - Tuesday, December 13, 2016")
        CboWeek.Items.Add("Week 6 - Tuesday, December 20, 2016")
        CboWeek.Items.Add("Week 7 - Tuesday, December 27, 2016")
        CboWeek.Items.Add("Week 8 - Tuesday, January 3, 2017")
        CboWeek.Items.Add("Week 9 - Tuesday, January 10, 2017")
        CboWeek.Items.Add("Week 10 - Tuesday, January 17, 2017")
        CboWeek.Items.Add("Week 11 - Tuesday, January 24, 2017")
        CboWeek.Items.Add("Week 12 - Tuesday, January 31, 2017")

        If CboWeek.Text = "" Then
            MessageBox.Show("Please select a week to process.", "Termination Control Utility Set", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub Btn_CWA_Click(sender As Object, e As EventArgs) Handles Btn_CWA.Click

        If CboWeek.Text <> "" Then
            ListBox1.Items.Add("Processing continuation of weekly analysis...")
            Application.DoEvents()

            connectionString = "<a lot of stuff – but it works>"
            Dim strControlResultsSql As String = "SELECT * FROM dbo.RG_Control_Results_Cumulative"

            Using ControlResultsConnection As New SqlConnection(connectionString)
                ControlResultsConnection.Open()
                Using ReadDS As New SqlDataAdapter(strControlResultsSql, ControlResultsConnection)
                    ReadDS.Fill(ControlResultsDS)
                End Using
            End Using
            Dim SQLCommandBlder As New SqlCommandBuilder(ReadDS)

            DataGridView1.DataSource = ControlResultsDS.Tables(0)
            Application.DoEvents()

        End If

    End Sub

    Private Sub Btn_InterimSave_Click(sender As Object, e As EventArgs) Handles Btn_InterimSave.Click

        ListBox1.Items.Add("Saving data...")
        Application.DoEvents()

        Dim SQLCB As New SqlCommandBuilder(ReadDS)
        ReadDS.Update(ControlResultsDS)
        ControlResultsDS.AcceptChanges()
        SQLCB.Dispose()        
        ControlResultsConnection.close()

    End Sub

End Class

The error I get is on the line ReadDS.Update(ControlResultsDS). The error is:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: The DataAdapter.SelectCommand property needs to be initialized.

Any advice would be greatly appreciated. Thank you all in advance!

---

Per Rahul's suggestion, I altered the code as follows:

Dim SQLCB As New SqlCommandBuilder(ReadDS)
ReadDS.SelectCommand = New SqlCommand("Select * From dbo.RG_Control_Results_Cumulative")
ReadDS.SelectCommand.Connection = New SqlConnection(connectionString)
ReadDS.Update(ControlResultsDS)
ControlResultsDS.AcceptChanges()
SQLCB.Dispose()

And got the following run-time error at the ReadDS.Update line:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Thank you and again sorry for the newbie questions.

Answer 1

What suggestion you need? Suggestion is present in the error statement itself. You are using SQLCommandBlder class and it generates UPDATE command automatically provided if you have binded the SELECT command to it ans that's what the error message says actually The DataAdapter.SelectCommand property needs to be initialized.

You should initialize the select command of your data adapter saying

ReadDS.SelectCommand = new SqlCommand("SELECT * FROM dbo.RG_Control_Results_Cumulative");

Answer 2

O.k. got it to work - thank you again Rahul for your guidance! The problem I had which I finally figured out was that my table lacked a primary key column, hence the adapater update was failing; once I added a column (which I called "Sequence_Number"), designated it as an ID column with simple sequential numbering, and further designated it as the primary key, everything worked perfectly. Again sorry for being such a noob on this but it was certainly a learning experience. Even old programmers have those!