Updating SQL Server 2012 with datagridview in vb.NET

[Venkatesh_6]

I have a problem updating table from datagridview in vb.net to sql server. I have posted the code below. I am importing connectionstring from connection module. It used to be working but I dont know what went wrong and now it isnt.

Imports System.Data.SqlClient

Public Class frmreportsp

Dim cn As New SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet
Dim cmd As New SqlCommand
Dim dt As New DataTable
Dim query As String
Dim reader As SqlDataReader

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




        cn = Connection.connectionstringESLC()
        cn.Open()
        If Connection.flag = "sp" Then
            query = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='School Psychology " & FrmSem.cmbSem.Text & "'"
        ElseIf Connection.flag = "sa" Then
            query = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='School Admin " & FrmSem.cmbSem.Text & "'"
        ElseIf Connection.flag = "hdl" Then
            query = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='HDL " & FrmSem.cmbSem.Text & "'"
        ElseIf Connection.flag = "sc" Then
            query = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='School Counselling " & FrmSem.cmbSem.Text & "'"
        End If

        cmd = New SqlCommand()
        cmd.CommandType = CommandType.Text

        cmd.CommandText = query

        cmd.Connection = cn
        reader = cmd.ExecuteReader()
        cmbSearch.Items.Clear()

        If reader.HasRows = True Then
            While reader.Read()
                cmbSearch.Items.Add(reader("COLUMN_NAME"))
            End While
        End If
        reader.Close()
        cn.Close()
        cmd.Dispose()
        cmd = Nothing
        cn.Open()
        cmd = New SqlCommand()
        cmd.CommandType = CommandType.Text
        If Connection.flag = "sp" Then
            cmd.CommandText = "select * from [dbo].[School Psychology " & FrmSem.cmbSem.Text & "]"
        ElseIf Connection.flag = "sa" Then
            cmd.CommandText = "select * from [dbo].[School Admin " & FrmSem.cmbSem.Text & "]"
        ElseIf Connection.flag = "hdl" Then
            cmd.CommandText = "select * from [dbo].[HDL " & FrmSem.cmbSem.Text & "]"
        ElseIf Connection.flag = "sc" Then
            cmd.CommandText = "select * from [dbo].[School Counselling " & FrmSem.cmbSem.Text & "]"

        End If
        cmd.Connection = cn
        dt = New DataTable()
        dt.Load(cmd.ExecuteReader)
        ' da = New SqlDataAdapter()
        'da.Fill(ds, "School Psychology Aug 2012")
        dgvschpsy.AutoGenerateColumns = True
        dgvschpsy.DataSource = dt
        'DataGridView1.DataSource = ds
        cmd.Dispose()
        cmd = Nothing
        cn.Close()
        cn = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnOK.Click
    Try
        cn = Connection.connectionstringESLC()
        cn.Open()
        cmd = New SqlCommand()
        cmd.CommandType = CommandType.Text
        If Connection.flag = "sp" Then
            cmd.CommandText = "select * from [dbo].[School Psychology " & FrmSem.cmbSem.Text & "] where [" & cmbSearch.Text & "] LIKE '%" & txtKeyword.Text & "%'"
        ElseIf Connection.flag = "sa" Then
            cmd.CommandText = "select * from [dbo].[School Admin " & FrmSem.cmbSem.Text & "] where [" & cmbSearch.Text & "] LIKE '%" & txtKeyword.Text & "%'"
        ElseIf Connection.flag = "hdl" Then
            cmd.CommandText = "select * from [dbo].[HDL " & FrmSem.cmbSem.Text & "] where [" & cmbSearch.Text & "] LIKE '%" & txtKeyword.Text & "%'"
        ElseIf Connection.flag = "sc" Then
            cmd.CommandText = "select * from [dbo].[School Counselling " & FrmSem.cmbSem.Text & "] where [" & cmbSearch.Text & "] LIKE '%" & txtKeyword.Text & "%'"
        End If

        cmd.Connection = cn
        dt = New DataTable()
        dt.Load(cmd.ExecuteReader)
        ' da = New SqlDataAdapter()
        'da.Fill(ds, "School Psychology Aug 2012")
        dgvschpsy.AutoGenerateColumns = True
        dgvschpsy.DataSource = dt
        'DataGridView1.DataSource = ds
        cmd.Dispose()
        cmd = Nothing
        cn.Close()
        cn = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click
    dgvschpsy.DataSource = Nothing
    frmreportsp_Load(sender, e)
    cmbSearch.SelectedItem = ""
    txtKeyword.Text = " "

End Sub

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles btnUpdate.Click

    Dim query1 As String
    If Connection.flag = "sp" Then
        query1 = "select * from [dbo].[School Psychology Aug 2012]"
    ElseIf Connection.flag = "sa" Then
        query1 = "select * from [dbo].[School Admin Aug 2012]"
        'ElseIf Connection.flag = "hdl" Then
        '    query1 = "select * from [dbo].[HDL Aug 2012]"
    End If
    cn = Connection.connectionstringESLC()
    cmd = New SqlCommand(query1, cn)
    cn.Open()
    da = New SqlDataAdapter(cmd)
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
    'dt = New DataTable()
    da.Fill(ds, "School Admin Aug 2012")
    Me.Validate()
    Me.da.Update(Me.ds.Tables(0))
    Me.ds.AcceptChanges()
    cn.Close()
    cn = Nothing
End Sub
End Class

[Santanu Das]

The most easiest way is Update your table programmatically at DatagridView1.CellEndEdit event. This event will raise when you try to leave the Cell.
Another Process:
Add a DataGridViewCheckBoxColumn, it will be checked or unchecked when you try to edit the Cell. This will be checked/unchecked at DatagridView1.CellBeginEdit event.
Now at btnUpdate.Click event run a loop through the rows, check the condition for edit. if the checkbox checked then update the database for that row programmatically.