Re: Updating access database by glenn
glenn
Sun Mar 26 16:44:55 CST 2006
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
"Norman Yuan" wrote:
> 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.
>
> "glenn" <glenn@discussions.microsoft.com> wrote in message
> news:9FC560EE-7603-4634-85C1-12834929E2E9@microsoft.com...
> > 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.
>
>
>