Thanks in advance for any help.

I am using code that is directly from David Sceppa's book from MS Press
titled "ADO.NET".

I have walked completely though the code and I find values are what they
should be until I reach the following subroutine.

     Private Sub SubmitChangesByHand()
        Dim cmdUpdate As System.Data.OleDb.OleDbCommand =
CreateUpdateCommand()

        Dim row As System.Data.DataRow
        Dim intRowsAffected As Integer
        Dim dvrs As System.Data.DataViewRowState
        dvrs = System.Data.DataViewRowState.ModifiedCurrent Or
System.Data.DataViewRowState.Deleted Or System.Data.DataViewRowState.Added
        For Each row In tbl.Select ("", "", dvrs)
        msgbox("in the for loop")
          intRowsAffected = SubmitUpdate(row, cmdUpdate)
          msgbox(intRowsAffected)
          if intRowsAffected = 1 then
            row.AcceptChanges()
          else
            row.RowError = "Update attempt failed"
          End If
        Next row
     End Sub

For some reason I am not getting inside the For loop.  Only thing I can see
is that the DataViewRowState is returning a value of 28 which should be 16
(for updating a database).  I invoke the function by a button click event
elsewhere in the code.

Other than that, the table has records in it which is declared elsewhere as:
Dim tbl As New System.Data.DataTable("categories")

I am using the Northwind database in my trial efforts.

Since your MsgBox inside the For loop did not get shown, it is obvious that
tblSelect() does not return any changed DataRow, so, there is no change in
the table to be updated to database.

You can re-write it to make it clearer:

dvrs = System.Data.DataViewRowState.ModifiedCurrent Or
System.Data.DataViewRowState.Deleted Or System.Data.DataViewRowState.Added
Dim drs as DataRow()=tbl.Select("","",dvrs)
If drs.Length=0 Then
    MsgBox("There is no change in DataTable. No update is needed!")
Else
    For Each row in drs
        ''''do update here
    Next row
End If

You can also use DataTable.GetChange() to make sure there is change before
updating data to database.

Show quoteHide quote

Thanks for your reply.

I am now successfully getting into the For loop.

When I hit the SubmitUpdate function, I get a compiler error with an
Identifier expected for a line of code in the SubmitUpdate function as
follows:

pc.("CategoryID_Orig").Value = row("CategoryID", DataRowVersion.Original)

The following code snippets contain three functions or subroutines for your
insight.

           Function CreateUpdateCommand() As OleDbCommand
            Dim StrSQL As String
            Dim connectionString As String = "Provider=Microsoft.Jet.4.0;
Ole DB Services=-4; Data Source=C:\Program File"& _

"s\Microsoft.NET\SDK\v2.0\QuickStart\aspnet\samples\data\App_Data\Northwind.mdb"
            Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)


            strSQL = "UPDATE [Categories] SET CategoryName=?, Description=?
WHERE CategoryID=?"

            Dim cmd As New OleDbCommand (strSQL, dbConnection)
            Dim pc As OleDbParameterCollection = cmd.Parameters

            pc.Add("CategoryID_New", OleDbType.Integer)
            pc.Add("CategoryName_New", OleDbType.BSTR)
            pc.Add("Description_New", OleDbType.BSTR)

            pc.Add("CategoryID_Orig", OleDbType.Integer)
            pc.Add("CategoryName_Orig", OleDbType.BSTR)
            pc.Add("Description_Orig", OleDbType.BSTR)

            Return cmd
         End Function


          Sub SubmitChangesByHand()
            Dim cmdUpdate As OleDbCommand = CreateUpdateCommand()

            Dim row As DataRow
            Dim intRowsAffected As Integer
            Dim dvrs As DataViewRowState
            dvrs = DataViewRowState.ModifiedCurrent Or
DataViewRowState.Deleted _
        Or DataViewRowState.Added Or DataViewRowState.ModifiedOriginal _
        Or DataViewRowState.CurrentRows Or DataViewRowState.None _
        Or DataViewRowState.OriginalRows Or DataViewRowState.Unchanged

            For Each row In tbl.Select ("", "", dvrs)
            msgbox("in the for loop")
              intRowsAffected = SubmitUpdate(row, cmdUpdate)
              msgbox(intRowsAffected)
              if intRowsAffected = 1 then
                row.AcceptChanges()
              else
                row.RowError = "Update attempt failed"
              End If
            Next row
         End Sub

          Function SubmitUpdate(ByVal row As DataRow, ByVal cmd As
OleDbCommand) As Integer

            Dim pc As OleDbParameterCollection = cmd.Parameters

            pc("CateroryID_New").Value = row("CategoryID")
            pc("CateroryName_New").Value = row("CategoryName")
            pc("Description_New").Value = row("Description")


            pc.("CategoryID_Orig").Value = row("CategoryID",
DataRowVersion.Proposed)
            pc.("CategoryName_Orig").Value = row("CategoryName",
DataRowVersion.Original)
            pc.("Description_Orig").Value = row("Description",
DataRowVersion.Original)


            Return cmd.ExecuteNonQuery
         End Function



Show quoteHide quote

Equivalent to VCL's DataModule
Creating simple exe that runs without framework?
Decimal/Single/...: which Numeric-type to use?
WebRequest & proxy problem
are System.Data.SqlClient.SqlConnection thread safe? can many threads share a System.Data.SqlClient.