Update Records through DataGridView VB.net

Question

Hi I am developing WinForm Application in vb.net and MS access back end. Using datagridview i can able to display the records by simply setting the datasource property of the datagridview. But I don't know how to update the records through datagridview, in vb6 it was very easy to update the records itself it will update the data in database, but in vb.net how to update the records? The following codes i used to display the records in datagridview..

Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim adpt As OleDbDataAdapter
Dim rs As DataTable

    conn = New OleDbConnection
    conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Application.StartupPath & "\sample.mdb"
    conn.Open()


    adpt = New OleDbDataAdapter("select * from table1", conn)
    rs = New DataTable
    adpt.Fill(rs)
    Me.dgv.DataSource = rs

If I modify the data in datagridview it will not affect the databaserecords. Please Help me...

What kind of index field do you have? Sometimes, if it's a text field and spaces get appended, updates will fail because the key fields do not match and the record cannot be found. This will not raise an error. — Steve Wellens
U mean database fields? I do have Text, Date and Number fields. And Number is the primary key of type auto number. — Vignesh

Lars Mekes Lars Mekes · Accepted Answer · 2017-10-19T10:18:24

In order to update the records in my database through my datagridview, I used a DataSet.

This is the code I used to fill a Dataset:

Public Cnn As New OleDb.OleDbConnection
Public Cmd As New OleDb.OleDbCommand
Public DR As OleDbDataReader
Public DA As OleDbDataAdapter
Public DS As DataSet
Public DT As DataTable
Public cmdBld As OleDbCommandBuilder

Cmd = New OleDbCommand("SELECT * FROM table1", Cnn)

Cnn.Open()

DA = New OleDbDataAdapter(Cmd)

cmdBld = New OleDbCommandBuilder(DA)

DS = New DataSet()

DA.Fill(DS, "table1")

DataGridView1.DataSource = DS.Tables("table1").DefaultView

Cnn.Close()

And this is the code for saving the changes from the datagridview to the database:

Me.Validate()

Me.DA.Update(Me.DS.Tables("table1"))

Me.DS.AcceptChanges()

Update Records through DataGridView VB.net

1 Answers