"Dave" <notva***@hotmail.com> wrote in message
news:eqyyW0RPGHA.3556@TK2MSFTNGP10.phx.gbl...
> Hello All,
>                I hope that someone might be kind enough to take a look at
> the following code, I am a novice when it comes to VB .net, and am trying
> to grasp ADO .net and SQL server. I'm using VB .Net 2003 and SQL2005
> Express edition, the Code Sample is mostly from David Sceppa's Microsoft
> ADO .net book. I have created the stored procedures that the code calls.
> The problem is that I end up with an error if I change the data in the
> data table and click the update button, "Update requires a valid
> UpdateCommand when passed DataRow collection with modified rows."
>
> I have been struggling with this for the past few days, any help would be
> greatly appreciated.
>
> Thanks Dave.
>
> -----Start Code ------
>
> Imports System.Data.SqlClient
> Public Class Form1
>    Inherits System.Windows.Forms.Form
>    Dim cn As SqlConnection
>    Dim da As SqlDataAdapter
>    Dim tbl As DataTable = GenTable()
>
>    Private Sub SubmitChangesViaStoredProcedures()
>        da.UpdateCommand = CreateUpdateViaSPCommand()
>        da.InsertCommand = CreateInsertViaSPCommand()
>        da.DeleteCommand = CreateDeleteViaSPCommand()
>
>    End Sub
>
>    Private Function CreateUpdateViaSPCommand() As SqlCommand
>        Dim cmd As New SqlCommand("spUpdateDetail", cn)
>        cmd.CommandType = CommandType.StoredProcedure
>
>        Dim pc As SqlParameterCollection = cmd.Parameters
>        pc.Add("OrderID_New", SqlDbType.Int, 0, "OrderID")
>        pc.Add("ProductID_New", SqlDbType.Int, 0, "ProductID")
>        pc.Add("Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
>        pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")
>
>        Dim param As SqlParameter
>        param = pc.Add("OrderID_Orig", SqlDbType.Int, 0, "OrderID")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("ProductID_Orig", SqlDbType.Int, 0, _
>                       "ProductID")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("Quantity_Orig", SqlDbType.SmallInt, 0, _
>                       "Quantity")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("UnitPrice_Orig", SqlDbType.Money, 0, _
>                       "UnitPrice")
>        param.SourceVersion = DataRowVersion.Original
>
>        Return cmd
>    End Function
>
>    Private Function CreateInsertViaSPCommand() As SqlCommand
>        Dim cmd As New SqlCommand("spInsertDetail", cn)
>        cmd.CommandType = CommandType.StoredProcedure
>
>        Dim pc As SqlParameterCollection = cmd.Parameters
>        pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
>        pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
>        pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
>        pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")
>
>        Return cmd
>    End Function
>
>    Private Function CreateDeleteViaSPCommand() As SqlCommand
>        Dim cmd As New SqlCommand("spDeleteDetail", cn)
>        cmd.CommandType = CommandType.StoredProcedure
>
>        Dim pc As SqlParameterCollection = cmd.Parameters
>        Dim param As SqlParameter
>        param = pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
>        param.SourceVersion = DataRowVersion.Original
>        param = pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")
>        param.SourceVersion = DataRowVersion.Original
>
>        Return cmd
>    End Function
>
>    Public Function GenTable() As DataTable
>        Dim tbl As New DataTable("Order Details")
>        Dim col As DataColumn
>        With tbl.Columns
>            col = .Add("OrderID", GetType(Integer))
>            col.AllowDBNull = False
>            col = .Add("ProductID", GetType(Integer))
>            col.AllowDBNull = False
>            col = .Add("Quantity", GetType(Int16))
>            col.AllowDBNull = False
>            col = .Add("UnitPrice", GetType(Decimal))
>            col.AllowDBNull = False
>        End With
>        tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), _
>                                           tbl.Columns("ProductID")}
>         Return tbl
>    End Function
>
>    Sub LoadData()
>        Dim strConn, strSQL As String
>        strConn = "integrated security=SSPI;data source=XXXXAPTIVA;" & _
>            "persist security info=False;initial catalog=Northwind"
>        strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
>                 "FROM [Order Details] WHERE OrderID = 10503 " & _
>                 "ORDER BY ProductID"
>        cn = New SqlConnection(strConn)
>        da = New SqlDataAdapter(strSQL, cn)
>
>        cn.Open()
>
>        da.Fill(tbl)
>        Dim MyDv As New DataView(tbl)
>        DataGrid1.DataSource = MyDv
>        cn.Close()
>    End Sub
>
>    Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e _
>        As System.EventArgs) Handles btnLoadData.Click
>        LoadData()
>    End Sub
>
>    Private Sub btnUpDate_Click(ByVal sender As System.Object, ByVal e  _
>        As System.EventArgs) Handles btnUpDate.Click
>
>         SubmitChangesViaStoredProcedures()
>        Try
>
>            da.Update(tbl)
>
>        Catch ex As System.InvalidOperationException
>
>            MessageBox.Show(ex.Message)
>
>        End Try
>
>    End Sub
> End Class
>
>
>

"W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message
news:ubxIXbTPGHA.3984@TK2MSFTNGP14.phx.gbl...
> Dave - I'm guessing you're using the Visual Tools to generate this code?
> If so, just rerun the wizard to regen the commands.  Check the
> CreateUpdateViaSPCommand for instance and check that all the paramaters
> are there. Also, ensure that your table has a PK on it, otherwise you'll
> have issues generating the new update logic.
> "Dave" <notva***@hotmail.com> wrote in message
> news:eqyyW0RPGHA.3556@TK2MSFTNGP10.phx.gbl...