Update Database with Changes made to the DataGridView

[Yogesh Sharma]

hey frnds, Firstly I pick the records from the databse into the DataGridView, Then I want that If we made changes to the records in the GridView & Click on Update Button, Changes are Reflected into the Datbase .Plz do reply back the ..

[tuse]

You should use a Command Builder to update records so that changes made to the dataset can be reflected into the database. Here is some sample code-

Public Class Form1

    Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=shreyas; User=root;Password=;")
    Dim cmd As Odbc.OdbcCommand
    Dim adp As Odbc.OdbcDataAdapter
    Dim ds As New DataSet

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        cn.Close()
    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Fill the data grid viewer
        cn.Open()
        cmd = New Odbc.OdbcCommand("Select * from trial1", cn)
        adp = New Odbc.OdbcDataAdapter(cmd)
        adp.Fill(ds, "trial1")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "trial1"
       
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Update records using a Command Builder
        ' The variable i gives the number of records updated
        Dim cmdbuilder As New Odbc.OdbcCommandBuilder(adp)
        Dim i As Integer
        Try
            i = adp.Update(ds, "trial1")
            MsgBox("Records Updated= " & i)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

   
End Class

[tuse]

button 1 is the update button.
let me know if this helped you.

[deyust]

button 1 is the update button. let me know if this helped you.

Hello, thanks for the code. Its almost working, I received this message. can you help me?!

"Dynamic SQL generation for the UpdateCommand is not supported against a SelecCommand that does not return any key column information"

[Comatose]

Let's try not to post to threads that are almost a year old....mkay?

[av1963]

thanks for your efficient, straightforward solution to updating user input to a datagridview in VB.NET - I'm new to .NET and had spent several hours trying to find code for what I had thought should be a simple procedure - I was ready to give up; thanks again -

[kartheeee]

Hi, im new for .net.
im am using same code, but only connection string is changed,
Dim cn As New Odbc.OdbcConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\OFC\aadata\wms1.mdb")

it showing error,
A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

what is that?

You should use a Command Builder to update records so that changes made to the dataset can be reflected into the database. Here is some sample code-

Public Class Form1

    Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=shreyas; User=root;Password=;")
    Dim cmd As Odbc.OdbcCommand
    Dim adp As Odbc.OdbcDataAdapter
    Dim ds As New DataSet

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        cn.Close()
    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Fill the data grid viewer
        cn.Open()
        cmd = New Odbc.OdbcCommand("Select * from trial1", cn)
        adp = New Odbc.OdbcDataAdapter(cmd)
        adp.Fill(ds, "trial1")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "trial1"
       
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Update records using a Command Builder
        ' The variable i gives the number of records updated
        Dim cmdbuilder As New Odbc.OdbcCommandBuilder(adp)
        Dim i As Integer
        Try
            i = adp.Update(ds, "trial1")
            MsgBox("Records Updated= " & i)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

   
End Class

[jayesh87]

getting the error message "value cannot be null parameter : dataset"