Updating access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top