?
Solved

How to update a DataGridview

Posted on 2007-07-25
9
Medium Priority
?
253 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:Netlink2
  • 4
  • 3
  • 2
9 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 19572277
Probably there is no MyCommand.UpdateCommand !

http://support.microsoft.com/kb/308055
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 19572280
0
 
LVL 1

Author Comment

by:Netlink2
ID: 19572340
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 :) ?.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Accepted Solution

by:
vbturbo earned 2000 total points
ID: 19573392
Hi

Try use the commandbuilder object

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cb As New SqlClient.SqlCommandBuilder(MyCommand )
        MyCommand .Update(DS, "tblRpw")
        Me.Hide()
    End Sub

What the CommandBuilder Is Supposed to Do.

SelectCommand. Specifies the SQL code and parameters needed to fetch the rowset(s) based on one or more SELECT queries.
InsertCommand. Specifies the SQL code and parameters needed to insert a new row in the database.
DeleteCommand. Specifies the SQL code and parameters needed to delete a specific row from a database table.
UpdateCommand. Specifies the SQL code and parameters needed to change a specific row in a database table.

Commandbuilder object contains these 4 commands.

vbturbo
0
 
LVL 1

Author Comment

by:Netlink2
ID: 19579030
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. "
0
 
LVL 18

Assisted Solution

by:vbturbo
vbturbo earned 2000 total points
ID: 19579129
Hi

In order for the SqlCommandBuilder object to your table must have a defined/setup with -  primary key.
Else your adapter will only work with the " select and insert commands " since  there would be a posibillity for abigous management of the records.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cb As New SqlClient.SqlCommandBuilder(MyCommand )
        MyCommand .Update(DS, "tblRpw")
        Me.Hide()
    End Sub

This line you dont need .

cb.GetUpdateCommand.Connection = MyConnection

Just use the above button click event.

a link to study
http://www.developer.com/db/article.php/10920_3441241_2

and here is the link to msdn
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(vs.71).aspx

vbturbo
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 19579169
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
0
 
LVL 1

Author Comment

by:Netlink2
ID: 19579181
Absolutely marvelous :)
0
 
LVL 18

Expert Comment

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question