asked on

How to update a DataGridview

Hi experts, I get an error " Update requires a valid UpdateCommand when passed DataRow collection with modified rows. " when updating.

Imports System.Data.SqlClient
Public Class frmLoginTable
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim SqlStr As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyCommand.Update(DS, "tblRpw")
Me.Hide()
End Sub

Private Sub frmLoginTable_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
SqlStr = "select * from tblRpw"
MyConnection = New SqlConnection("Data Source=SERVER;Initial Catalog=AL;Integrated Security=True")
MyCommand = New SqlDataAdapter(SqlStr, MyConnection)
MyCommand.AcceptChangesDuringFill = True
DS = New DataSet()
MyCommand.Fill(DS, "tblRpw")
DataGridView1.DataSource = DS.Tables("tblRpw").DefaultView
End Sub
End Class

Dirk Haest

Probably there is no MyCommand.UpdateCommand !

http://support.microsoft.com/kb/308055

Dirk Haest

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

Netlink2

ASKER

This seems a bit old fashioned, with VB 6 it was SO easy. How could a new system be worse ? I'm guessing there must be a way or did the MS team get too rich and lazy :) ?.

ASKER CERTIFIED SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Netlink2

ASKER

Thanks for that Turbo, The following code still gives me an error, probably due to the connector being wrong (cb.GetUpdateCommand.Connection = MyConnection), wondering if you could help me again.

Dim cb As New SqlClient.SqlCommandBuilder(MyCommand)
cb.GetUpdateCommand.Connection = MyConnection
MyCommand.Update(DS, "tblRpw")

The error is " Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. "

SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbturbo

A small example , though this is with ole.db but is easily modified to Sql

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim daParent As OleDb.OleDbDataAdapter

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & _myApplicationpath & "\database\Test.mdb"
sql = "SELECT * FROM Parent"
con.Open()
'read the connection data ect....
daParent = New OleDb.OleDbDataAdapter(sql, con)
'read the tables schema data
daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
'fill the all the data from a table named "Parent" into the dataset
daParent.Fill(ds, "Parent")
con.Close()
'bind the grid to the dataset table
Parentgrdview.DataSource = ds.Tables("Parent")
End Sub

Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click
Dim cb As New OleDb.OleDbCommandBuilder(daParent)
'the adapter has all the data it needs to establish the connection to the datasource and all the crud commands
daParent.Update(ds, "Parent")
End Sub

Netlink2

ASKER

Absolutely marvelous :)

vbturbo

Something is tough gotten better with .NET - even me as an earlier core vb6 fan -:) have to acknowledge that.