update database when I change in dataGridView

Question

I m new in VB. I want to update my database when I make any change in dataGridView. Can anyone give me details about this? the scenario is I will change value from datagridview and after I click update button I should change the database value.code is

Private Sub btnModify_Click(sender As Object, e As EventArgs) Handles btnModify.Click

    Dim cmdbuilder As New Odbc.OdbcCommandBuilder(da)

    ds = gridDisplay(cmbxState.SelectedItem)

    Try
        da.Update(ds.Tables("districtMaster"))

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

is giving error like Error 2 Value of type 'System.Data.DataTable' cannot be converted to 'System.Data.DataSet'. — Santosh Patil
my gridDisplay function is Function gridDisplay(stateName) Dim query As String = "select row_number() OVER (ORDER BY d.district_name) AS SrNo, d.district_name as District, s.state_name as State,d.status as Status from sg_state_mst s, sg_district_mst d where s.state_code=d.state_code and s.state_name = '" & stateName & "' order by(district_name)" ds = obj.displyQuery(query, "districtMaster") DataGridView1.DataSource = ds.Tables("districtMaster") ' Assigning dataset as source for the dataGridView Return ds End Function — Santosh Patil

ɐsɹǝʌ ǝɔıʌ ɐsɹǝʌ ǝɔıʌ · Accepted Answer · 2013-12-03T10:07:27

You must define an InsertCommand in your DataAdapter. Then you'll need to call the Update method of the adapter when you want to save the changes.

Populating the DataGridView on Form Load

Private myConString As String
Private con As OleDbConnection = New OleDbConnection
Private Dadapter As OleDbDataAdapter
Private DSet As DataSet

Private Sub Form_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    myConString = "Your connection string here"
    con.ConnectionString = myConString
    con.Open()
    Dadapter = New OleDbDataAdapter("SELECT * from Table", con)
    DSet = New DataSet
    Dadapter.Fill(DSet, "Table")
    DataGridView1.DataSource = DSet.Tables("Table")
    con.Close()
End Sub

Updating the database

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Using con = new New OleDbConnection(myConString)
    con.Open()
    Dadapter.Update(DSet, "Table")
End Using
End Sub

From the Docs:

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update%28v=VS.100%29.aspx

update database when I change in dataGridView

1 Answers