Updating SQL server table with vb.net 2010 datagridview

Question

I am having trouble updating my database table with datagridview, I've read some thread about the problem and nothing seem to work in my case. Here is the code i use when populating the datagridview.

    Public Sub filldatagridserver(ByRef SQL As String, ByRef dg As DataGridView)
    Dim myDataset As DataSet = New DataSet
    myAdptr.SelectCommand = New SqlCommand(SQL, cnSQL)
    myAdptr.Fill(myDataset)

    dg.DataSource = myDataset.Tables(0)
    dg.AutoResizeColumns()

    myAdptr.Dispose()
    myDataset.Dispose()
    cnSQL.Close()

    End Sub

then i call this sub procedure with the ff code:

    Call filldatagridserver("SELECT * FROM tblQuarterCollection", dgQuarter)

i already used myAdptr.update but i keep getting error about table mapping etc. I also tried to quote out the dispose and close in that sub procedure but it still doesn't work.

is there anyway to properly populate the datagridview and update changes made to database. I think my last resort here would be looping thru each record the update the data according to current value in the datagridview. Please help.

It will be useful to see the code that calls the update, however if you destroy the adapter then you could not update anything with that — Steve
@steve this is what i use when doing the update. myAdptr.Update(myDataset, "tblPayMTC") — Hideki
Then add the name of the table also when you fill the dataset myAdptr.Fill(myDataset, "tblPayMTC"). Another important point to get the automatica update working is to have a primary key returned by the SELECT query. Did you have a primary key defined for your table? — Steve

Unknownymous Unknownymous · Accepted Answer · 2014-02-17T08:29:44

try this :

 Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
Dim addcmd as new sqlcommand("INSERT INTO TableName VALUES(@col1,@col2)",YourConnectionString)
addcmd.parameters.addwithvalue("@col1",txtCol1.text)
addcmd.parameters.addwithvalue("@col2",txtCol2.text)
addcmd.executenonQuery


End Sub

Updating SQL server table with vb.net 2010 datagridview

3 Answers