VB.Net, Problem updating table from DataGridView

Question

This is my first post so forgive me if I goof! I am trying to update myself from VBA to VB.Net. Using loads of help from Google etc I am doing Ok except when I try to update my table from a DataGridView. It just does not update. What I would like is that a cell is update on change. My code so far is shown (I have tried all sorts of builder, tables etc so my code may have a smattering of these that are redundant):

Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.SqlServer

Public Class FrmData
Private dsS As DataSet = New DataSet
Private adpS As SqlDataAdapter
Private builder As SqlCommandBuilder
Private bsource = New BindingSource

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

    Dim sqlS = "SELECT   [Data].[Date] AS [Date] ,
        [Data].[PaidBy] AS [Paid By] ,
        [Data].[PaidAmount] AS [Paid Amount (£)],
        [Data].[AmountLeft] AS [Amount Left (£)] 
        FROM [Data] WHERE [Data].[Name]= '" & strName & "'
        ORDER BY [Data].[Date] DESC"
    Dim adpS As SqlDataAdapter
    adpS = New SqlDataAdapter(sqlS, connection)
    builder = New SqlCommandBuilder(adpS)
    Dim dTable As New DataTable

    bsource.DataSource = dTable
    bsource.EndEdit()
    adpS.Fill(dTable)
    connection.Close()
    DataGridView1.DataSource = dTable

End Sub

Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    DataGridView1.EndEdit()
    Dim dt As DataTable
    dt = TryCast(DataGridView1.DataSource, DataTable)

    Dim x As Integer = 0

    If dt.GetChanges() Is Nothing Then
        MessageBox.Show("The table contains no changes to save.")
    Else
        Dim builder = New SqlCommandBuilder(adpS)
        Dim rowsAffected As Integer = adpS.Update(dt)
        If rowsAffected = 0 Then
            MessageBox.Show("No rows were affected by the save operation.")
        Else
            MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
        End If
    End If
End Sub
End Class

Any help would be appreciated.

Answer 1

After two days working on this, I finally got it right for myself! Also, I had an eye on @ryguy72 codes as well. these are the steps you can take to get there:

Step 1: Drag and drop DataGridView into your form

Step 2: In the App.config add this between configuration:

     <connectionStrings>
        <add name="ehsanConnection" connectionString="Data Source=XXX 
         ; User= XX; Password= XXX"  ProviderName="System.Data.SqlClient"/>
     </connectionStrings>

Step 3: The code below shows how you can get the DataGridView programmatically right, it worked perfectly fine for me.

     Dim sCommand As SqlCommand
     Dim sAdapter As SqlDataAdapter
     Dim sBuilder As SqlCommandBuilder
     Dim sDs As DataSet
     Dim sTable As DataTable

    Dim connStr As String = 
    ConfigurationManager.ConnectionStrings("ehsanConnection").ToString

    Dim connStri = New SqlConnection(connStr)
    Dim sql As String = "SELECT * FROM [Ehsan].[dbo].[Data]"

    sCommand = New SqlCommand(sql, connStri)
    sAdapter = New SqlDataAdapter(sCommand)
    sBuilder = New SqlCommandBuilder(sAdapter)

    sDs = New DataSet()
    sAdapter.Fill(sDs, "Data")
    sTable = sDs.Tables("Data")
    connStri.Close()

    DataGridView1.DataSource = sDs.Tables("Data")

The main point here was that I had to use [Ehsan].[dbo].[Data] not only the name of the table, "Data". Actually, it didn't work for me that way and it kept complaining!

Step 4: If you want to update your database after you changed some of the records in datagridview, use this code :

   sAdapter.Update(sDs.Tables(0))

Main important point is that: "you have to set a primary key in your table first otherwise it won't work!"

Answer 2

This is for SQL Server, right. Try it like this.

Imports System.Data.SqlClient
Public Class Form1
    Dim sCommand As SqlCommand
    Dim sAdapter As SqlDataAdapter
    Dim sBuilder As SqlCommandBuilder
    Dim sDs As DataSet
    Dim sTable As DataTable

    Private Sub load_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles load_btn.Click
        Dim connectionString As String = "Data Source=.;Initial Catalog=pubs;Integrated Security=True"
        Dim sql As String = "SELECT * FROM Stores"
        Dim connection As New SqlConnection(connectionString)
        connection.Open()
        sCommand = New SqlCommand(sql, connection)
        sAdapter = New SqlDataAdapter(sCommand)
        sBuilder = New SqlCommandBuilder(sAdapter)
        sDs = New DataSet()
        sAdapter.Fill(sDs, "Stores")
        sTable = sDs.Tables("Stores")
        connection.Close()
        DataGridView1.DataSource = sDs.Tables("Stores")
        DataGridView1.ReadOnly = True
        save_btn.Enabled = False
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

    End Sub

    Private Sub new_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles new_btn.Click
        DataGridView1.[ReadOnly] = False
        save_btn.Enabled = True
        new_btn.Enabled = False
        delete_btn.Enabled = False
    End Sub

    Private Sub delete_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delete_btn.Click
        If MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            DataGridView1.Rows.RemoveAt(DataGridView1.SelectedRows(0).Index)
            sAdapter.Update(sTable)
        End If
    End Sub

    Private Sub save_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save_btn.Click
        sAdapter.Update(sTable)
        DataGridView1.[ReadOnly] = True
        save_btn.Enabled = False
        new_btn.Enabled = True
        delete_btn.Enabled = True
    End Sub
End Class