Update Access data

29 Mar 2006 8:24 PM

George

Hi all,

I am having trouble with updating my data in an Access database. here is my
code:

Imports System.Data.OleDb

    Dim AppPath As String = Mid(Application.ExecutablePath, 1,
Len(Application.ExecutablePath) - 14)
    Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
d:\comic2006\comic.mdb"
    Dim dbConn As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection(strConn)

        Dim DSet As New DataSet, SQLStr As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
        dbConn.Open()

        Dim tRow As DataRow, tTbl As DataTable
        With dbAdaptr
            .TableMappings.Add("Table", "issues")
            SQLStr = "Select * from issues WHERE series = " &
CType(cmbSeries.SelectedItem, ComboItem).ItemData & " AND issuea = '" &
CType(cmbIssues.SelectedItem, ComboItem).Item & "' AND issuen = " &
CType(cmbIssues.SelectedItem, ComboItem).ItemData
            cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
            cmd.CommandType = CommandType.Text
            .SelectCommand = cmd
            .Fill(DSet)
            '           .Dispose()
        End With

        '     DSet.AcceptChanges()
        tTbl = DSet.Tables.Item(0)
        '       DSet.Dispose()
        dbConn.Close()

        ' Load the issue information into the form
        For Each tRow In tTbl.Rows
            tRow("month") = txtMM.Text
            tRow("day") = txtDD.Text
            tRow("year") = txtYY.Text
            tRow("pages") = txtPages.Text
            tRow("ad pages") = txtAdPages.Text
            tRow("price") = txtCoverPrice.Text
            tRow("stories") = txtStories.Text
            tRow("cover caption") = txtCoverCaption.Text
            tRow("notes") = txtIssueNotes.Text
        Next

        dbAdaptr.Update(DSet)

        dbAdaptr.Dispose()
        tTbl.Dispose()

The error I get is:
Update requires a valid UpdateCommand when passed DataRow collection with
modified rows.

I am trying to re-write an app from VB6 to vb.net and this is all very new
to me, especially the database access, so forgive me if the error is obvious.

Thanks in advance for your help,

George

29 Mar 2006 8:54 PM

Chris

George wrote:
Show quoteHide quote

> Hi all,
>
> I am having trouble with updating my data in an Access database. here is my
> code:
>
> Imports System.Data.OleDb
>
>
>     Dim AppPath As String = Mid(Application.ExecutablePath, 1,
> Len(Application.ExecutablePath) - 14)
>     Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
> d:\comic2006\comic.mdb"
>     Dim dbConn As System.Data.OleDb.OleDbConnection = New
> System.Data.OleDb.OleDbConnection(strConn)
>
>         Dim DSet As New DataSet, SQLStr As String
>         Dim cmd As System.Data.OleDb.OleDbCommand
>         Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
> System.Data.OleDb.OleDbDataAdapter
>         dbConn.Open()
>
>         Dim tRow As DataRow, tTbl As DataTable
>         With dbAdaptr
>             .TableMappings.Add("Table", "issues")
>             SQLStr = "Select * from issues WHERE series = " &
> CType(cmbSeries.SelectedItem, ComboItem).ItemData & " AND issuea = '" &
> CType(cmbIssues.SelectedItem, ComboItem).Item & "' AND issuen = " &
> CType(cmbIssues.SelectedItem, ComboItem).ItemData
>             cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
>             cmd.CommandType = CommandType.Text
>             .SelectCommand = cmd
>             .Fill(DSet)
>             '           .Dispose()
>         End With
>
>         '     DSet.AcceptChanges()
>         tTbl = DSet.Tables.Item(0)
>         '       DSet.Dispose()
>         dbConn.Close()
>
>         ' Load the issue information into the form
>         For Each tRow In tTbl.Rows
>             tRow("month") = txtMM.Text
>             tRow("day") = txtDD.Text
>             tRow("year") = txtYY.Text
>             tRow("pages") = txtPages.Text
>             tRow("ad pages") = txtAdPages.Text
>             tRow("price") = txtCoverPrice.Text
>             tRow("stories") = txtStories.Text
>             tRow("cover caption") = txtCoverCaption.Text
>             tRow("notes") = txtIssueNotes.Text
>         Next
>
>         dbAdaptr.Update(DSet)
>
>         dbAdaptr.Dispose()
>         tTbl.Dispose()
>
>
> The error I get is:
> Update requires a valid UpdateCommand when passed DataRow collection with
> modified rows.
>
> I am trying to re-write an app from VB6 to vb.net and this is all very new
> to me, especially the database access, so forgive me if the error is obvious.
>
> Thanks in advance for your help,
>
> George
>

The message is telling you that the dataadapter does not know how to
update the database because you did not supply it an updatecommand. I
would do some reading on dataadapters and updatecommands. Or you could
make a new command object and send it an "Update ...." sql statement.

Chris

29 Mar 2006 11:51 PM

Rich

Just sharing my 2Â¢ worth here. I have not had much luck with dataAdapters
except for filling dataTables in datasets. I just use a dataAdapter to fill
a dataset, usually for a datagrid or a bunch of textboxes on a form. Then I
use a command object for inserting, deleting, updating as follows.

Dim DA As SqlDataAdapter, DS As DataSet
Dim cmdSel, cmdIns, cmdDel, cmdUpdate As SqlCommand
Dim curMgr As CurrencyManager
Dim strSqlUpdate, str0, str1, str2, str3, str4, str5 As String
Dim dt As DataTable, i, j As Integer

conn1.Open()
strSqlSel = "Select * From tblXYZ Order By rowID"
cmdSel = New SqlCommand(strSqlSel, conn1)
DA = New SqlDataAdapter
DA.SelectCommand = cmdSel
DS = New DataSet
DS.Clear()
DA.Fill(DS, "tbl1")
dgr1.SetDataBinding(DS, "tbl1")
curMgr = CType(dgr1.BindingContext(DS, "tbl1"), CurrencyManager)
cmdUpdate = New SqlCommand
cmdUpdate.CommandType = CommandType.Text
'--the datarows here belong to the dataTable that the DataGrid is bound to
dt = DS.Tables(0)
j = 0
For i = 0 To curMgr.Count
If dgr1.IsSelected(i) Then
    str0 = dt.Rows(i).Item(0).ToString
    str1 = dt.Rows(i).Item(1).ToString
    str2 = dt.Rows(i).Item(2).ToString
    str3 = dt.Rows(i).Item(3).ToString
    str4 = dt.Rows(i).Item(4).ToString
    str5 = dt.Rows(i).Item(5).ToString
    strSqlUpdate = "Update tblXYZ Set fld1 = '" & str1 & "', "
    strSqlUpdate += "fld2 = '" & str2 & "', fld3 = '" & str3 & "', fld4 = '"
& str4 & "', "
    strSqlUpdate += "fld5 = '" & str5 & "' Where rowID = " & str0
    cmdUpdate.CommandText = strSqlUpdate
    cmdUpdate.Connection = conn1
    cmdUpdate.ExecuteNonQuery()
End If
Next

The For Loop will iterate through the dataTable and update each row
individually. If you need to update lots of rows in one shot, just use a
basic sql statement with a command object:

strSql = "Update tbl1 Set fldx = 'xyz'"
cmd.CommandText = strSql
cmd.ExecuteNonQuery()

HTH
Rich

Show quoteHide quote

"George" wrote:

> Hi all,
>
> I am having trouble with updating my data in an Access database. here is my
> code:
>
> Imports System.Data.OleDb
>
>
>     Dim AppPath As String = Mid(Application.ExecutablePath, 1,
> Len(Application.ExecutablePath) - 14)
>     Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
> d:\comic2006\comic.mdb"
>     Dim dbConn As System.Data.OleDb.OleDbConnection = New
> System.Data.OleDb.OleDbConnection(strConn)
>
>         Dim DSet As New DataSet, SQLStr As String
>         Dim cmd As System.Data.OleDb.OleDbCommand
>         Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
> System.Data.OleDb.OleDbDataAdapter
>         dbConn.Open()
>
>         Dim tRow As DataRow, tTbl As DataTable
>         With dbAdaptr
>             .TableMappings.Add("Table", "issues")
>             SQLStr = "Select * from issues WHERE series = " &
> CType(cmbSeries.SelectedItem, ComboItem).ItemData & " AND issuea = '" &
> CType(cmbIssues.SelectedItem, ComboItem).Item & "' AND issuen = " &
> CType(cmbIssues.SelectedItem, ComboItem).ItemData
>             cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
>             cmd.CommandType = CommandType.Text
>             .SelectCommand = cmd
>             .Fill(DSet)
>             '           .Dispose()
>         End With
>
>         '     DSet.AcceptChanges()
>         tTbl = DSet.Tables.Item(0)
>         '       DSet.Dispose()
>         dbConn.Close()
>
>         ' Load the issue information into the form
>         For Each tRow In tTbl.Rows
>             tRow("month") = txtMM.Text
>             tRow("day") = txtDD.Text
>             tRow("year") = txtYY.Text
>             tRow("pages") = txtPages.Text
>             tRow("ad pages") = txtAdPages.Text
>             tRow("price") = txtCoverPrice.Text
>             tRow("stories") = txtStories.Text
>             tRow("cover caption") = txtCoverCaption.Text
>             tRow("notes") = txtIssueNotes.Text
>         Next
>
>         dbAdaptr.Update(DSet)
>
>         dbAdaptr.Dispose()
>         tTbl.Dispose()
>
>
> The error I get is:
> Update requires a valid UpdateCommand when passed DataRow collection with
> modified rows.
>
> I am trying to re-write an app from VB6 to vb.net and this is all very new
> to me, especially the database access, so forgive me if the error is obvious.
>
> Thanks in advance for your help,
>
> George
>

30 Mar 2006 12:14 AM

Kerry Moorman

Rich,

Your code does not seem to deal with concurrency issues.

I'm pretty sure that even the generated UpdateCommands, from a command
builder for example, have the code to deal with concurrency. That's a huge
plus for the dataadapter since it uses the table's original values for
concurrency testing.

Kerry Moorman

Show quoteHide quote

"Rich" wrote:

> Just sharing my 2Â¢ worth here. I have not had much luck with dataAdapters
> except for filling dataTables in datasets. I just use a dataAdapter to fill
> a dataset, usually for a datagrid or a bunch of textboxes on a form. Then I
> use a command object for inserting, deleting, updating as follows.
>
>
> Dim DA As SqlDataAdapter, DS As DataSet
> Dim cmdSel, cmdIns, cmdDel, cmdUpdate As SqlCommand
> Dim curMgr As CurrencyManager
> Dim strSqlUpdate, str0, str1, str2, str3, str4, str5 As String
> Dim dt As DataTable, i, j As Integer
>
> conn1.Open()
> strSqlSel = "Select * From tblXYZ Order By rowID"
> cmdSel = New SqlCommand(strSqlSel, conn1)
> DA = New SqlDataAdapter
> DA.SelectCommand = cmdSel
> DS = New DataSet
> DS.Clear()
> DA.Fill(DS, "tbl1")
> dgr1.SetDataBinding(DS, "tbl1")
> curMgr = CType(dgr1.BindingContext(DS, "tbl1"), CurrencyManager)
> cmdUpdate = New SqlCommand
> cmdUpdate.CommandType = CommandType.Text
> '--the datarows here belong to the dataTable that the DataGrid is bound to
> dt = DS.Tables(0)
> j = 0
> For i = 0 To curMgr.Count
>   If dgr1.IsSelected(i) Then
>     str0 = dt.Rows(i).Item(0).ToString
>     str1 = dt.Rows(i).Item(1).ToString
>     str2 = dt.Rows(i).Item(2).ToString
>     str3 = dt.Rows(i).Item(3).ToString
>     str4 = dt.Rows(i).Item(4).ToString
>     str5 = dt.Rows(i).Item(5).ToString
>     strSqlUpdate = "Update tblXYZ Set fld1 = '" & str1 & "', "
>     strSqlUpdate += "fld2 = '" & str2 & "', fld3 = '" & str3 & "', fld4 = '"
> & str4 & "', "
>     strSqlUpdate += "fld5 = '" & str5 & "' Where rowID = " & str0
>     cmdUpdate.CommandText = strSqlUpdate
>     cmdUpdate.Connection = conn1
>     cmdUpdate.ExecuteNonQuery()
>   End If
> Next
>
> The For Loop will iterate through the dataTable and update each row
> individually. If you need to update lots of rows in one shot, just use a
> basic sql statement with a command object:
>
> strSql = "Update tbl1 Set fldx = 'xyz'"
> cmd.CommandText = strSql
> cmd.ExecuteNonQuery()
>
> HTH
> Rich
>
> "George" wrote:
>
> > Hi all,
> >
> > I am having trouble with updating my data in an Access database. here is my
> > code:
> >
> > Imports System.Data.OleDb
> >
> >
> >     Dim AppPath As String = Mid(Application.ExecutablePath, 1,
> > Len(Application.ExecutablePath) - 14)
> >     Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
> > d:\comic2006\comic.mdb"
> >     Dim dbConn As System.Data.OleDb.OleDbConnection = New
> > System.Data.OleDb.OleDbConnection(strConn)
> >
> >         Dim DSet As New DataSet, SQLStr As String
> >         Dim cmd As System.Data.OleDb.OleDbCommand
> >         Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
> > System.Data.OleDb.OleDbDataAdapter
> >         dbConn.Open()
> >
> >         Dim tRow As DataRow, tTbl As DataTable
> >         With dbAdaptr
> >             .TableMappings.Add("Table", "issues")
> >             SQLStr = "Select * from issues WHERE series = " &
> > CType(cmbSeries.SelectedItem, ComboItem).ItemData & " AND issuea = '" &
> > CType(cmbIssues.SelectedItem, ComboItem).Item & "' AND issuen = " &
> > CType(cmbIssues.SelectedItem, ComboItem).ItemData
> >             cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
> >             cmd.CommandType = CommandType.Text
> >             .SelectCommand = cmd
> >             .Fill(DSet)
> >             '           .Dispose()
> >         End With
> >
> >         '     DSet.AcceptChanges()
> >         tTbl = DSet.Tables.Item(0)
> >         '       DSet.Dispose()
> >         dbConn.Close()
> >
> >         ' Load the issue information into the form
> >         For Each tRow In tTbl.Rows
> >             tRow("month") = txtMM.Text
> >             tRow("day") = txtDD.Text
> >             tRow("year") = txtYY.Text
> >             tRow("pages") = txtPages.Text
> >             tRow("ad pages") = txtAdPages.Text
> >             tRow("price") = txtCoverPrice.Text
> >             tRow("stories") = txtStories.Text
> >             tRow("cover caption") = txtCoverCaption.Text
> >             tRow("notes") = txtIssueNotes.Text
> >         Next
> >
> >         dbAdaptr.Update(DSet)
> >
> >         dbAdaptr.Dispose()
> >         tTbl.Dispose()
> >
> >
> > The error I get is:
> > Update requires a valid UpdateCommand when passed DataRow collection with
> > modified rows.
> >
> > I am trying to re-write an app from VB6 to vb.net and this is all very new
> > to me, especially the database access, so forgive me if the error is obvious.
> >
> > Thanks in advance for your help,
> >
> > George
> >

30 Mar 2006 5:47 AM

Rich

You got me on this one, I was in Sql Server mode where dealing with deadlock
isn't so bad - just set the order of precedence. How do you do that with
Access? There in lies the difference between Access and Sql Server. This
is my workaround for using the command builder. I just don't really know how
to set up the command builder. I think I have tried it only once or twice.
That goes for dataAdapters - except for using the wizards (hate those
wizards) I don't really know how to set up Update/Insert statements with the
? param marker. May I ask for a sample? Say, with the command builder?

Show quoteHide quote

"Kerry Moorman" wrote:

> Rich,
>
> Your code does not seem to deal with concurrency issues.
>
> I'm pretty sure that even the generated UpdateCommands, from a command
> builder for example, have the code to deal with concurrency. That's a huge
> plus for the dataadapter since it uses the table's original values for
> concurrency testing.
>
> Kerry Moorman
>
>
> "Rich" wrote:
>
> > Just sharing my 2Â¢ worth here. I have not had much luck with dataAdapters
> > except for filling dataTables in datasets. I just use a dataAdapter to fill
> > a dataset, usually for a datagrid or a bunch of textboxes on a form. Then I
> > use a command object for inserting, deleting, updating as follows.
> >
> >
> > Dim DA As SqlDataAdapter, DS As DataSet
> > Dim cmdSel, cmdIns, cmdDel, cmdUpdate As SqlCommand
> > Dim curMgr As CurrencyManager
> > Dim strSqlUpdate, str0, str1, str2, str3, str4, str5 As String
> > Dim dt As DataTable, i, j As Integer
> >
> > conn1.Open()
> > strSqlSel = "Select * From tblXYZ Order By rowID"
> > cmdSel = New SqlCommand(strSqlSel, conn1)
> > DA = New SqlDataAdapter
> > DA.SelectCommand = cmdSel
> > DS = New DataSet
> > DS.Clear()
> > DA.Fill(DS, "tbl1")
> > dgr1.SetDataBinding(DS, "tbl1")
> > curMgr = CType(dgr1.BindingContext(DS, "tbl1"), CurrencyManager)
> > cmdUpdate = New SqlCommand
> > cmdUpdate.CommandType = CommandType.Text
> > '--the datarows here belong to the dataTable that the DataGrid is bound to
> > dt = DS.Tables(0)
> > j = 0
> > For i = 0 To curMgr.Count
> >   If dgr1.IsSelected(i) Then
> >     str0 = dt.Rows(i).Item(0).ToString
> >     str1 = dt.Rows(i).Item(1).ToString
> >     str2 = dt.Rows(i).Item(2).ToString
> >     str3 = dt.Rows(i).Item(3).ToString
> >     str4 = dt.Rows(i).Item(4).ToString
> >     str5 = dt.Rows(i).Item(5).ToString
> >     strSqlUpdate = "Update tblXYZ Set fld1 = '" & str1 & "', "
> >     strSqlUpdate += "fld2 = '" & str2 & "', fld3 = '" & str3 & "', fld4 = '"
> > & str4 & "', "
> >     strSqlUpdate += "fld5 = '" & str5 & "' Where rowID = " & str0
> >     cmdUpdate.CommandText = strSqlUpdate
> >     cmdUpdate.Connection = conn1
> >     cmdUpdate.ExecuteNonQuery()
> >   End If
> > Next
> >
> > The For Loop will iterate through the dataTable and update each row
> > individually. If you need to update lots of rows in one shot, just use a
> > basic sql statement with a command object:
> >
> > strSql = "Update tbl1 Set fldx = 'xyz'"
> > cmd.CommandText = strSql
> > cmd.ExecuteNonQuery()
> >
> > HTH
> > Rich
> >
> > "George" wrote:
> >
> > > Hi all,
> > >
> > > I am having trouble with updating my data in an Access database. here is my
> > > code:
> > >
> > > Imports System.Data.OleDb
> > >
> > >
> > >     Dim AppPath As String = Mid(Application.ExecutablePath, 1,
> > > Len(Application.ExecutablePath) - 14)
> > >     Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
> > > d:\comic2006\comic.mdb"
> > >     Dim dbConn As System.Data.OleDb.OleDbConnection = New
> > > System.Data.OleDb.OleDbConnection(strConn)
> > >
> > >         Dim DSet As New DataSet, SQLStr As String
> > >         Dim cmd As System.Data.OleDb.OleDbCommand
> > >         Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
> > > System.Data.OleDb.OleDbDataAdapter
> > >         dbConn.Open()
> > >
> > >         Dim tRow As DataRow, tTbl As DataTable
> > >         With dbAdaptr
> > >             .TableMappings.Add("Table", "issues")
> > >             SQLStr = "Select * from issues WHERE series = " &
> > > CType(cmbSeries.SelectedItem, ComboItem).ItemData & " AND issuea = '" &
> > > CType(cmbIssues.SelectedItem, ComboItem).Item & "' AND issuen = " &
> > > CType(cmbIssues.SelectedItem, ComboItem).ItemData
> > >             cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
> > >             cmd.CommandType = CommandType.Text
> > >             .SelectCommand = cmd
> > >             .Fill(DSet)
> > >             '           .Dispose()
> > >         End With
> > >
> > >         '     DSet.AcceptChanges()
> > >         tTbl = DSet.Tables.Item(0)
> > >         '       DSet.Dispose()
> > >         dbConn.Close()
> > >
> > >         ' Load the issue information into the form
> > >         For Each tRow In tTbl.Rows
> > >             tRow("month") = txtMM.Text
> > >             tRow("day") = txtDD.Text
> > >             tRow("year") = txtYY.Text
> > >             tRow("pages") = txtPages.Text
> > >             tRow("ad pages") = txtAdPages.Text
> > >             tRow("price") = txtCoverPrice.Text
> > >             tRow("stories") = txtStories.Text
> > >             tRow("cover caption") = txtCoverCaption.Text
> > >             tRow("notes") = txtIssueNotes.Text
> > >         Next
> > >
> > >         dbAdaptr.Update(DSet)
> > >
> > >         dbAdaptr.Dispose()
> > >         tTbl.Dispose()
> > >
> > >
> > > The error I get is:
> > > Update requires a valid UpdateCommand when passed DataRow collection with
> > > modified rows.
> > >
> > > I am trying to re-write an app from VB6 to vb.net and this is all very new
> > > to me, especially the database access, so forgive me if the error is obvious.
> > >
> > > Thanks in advance for your help,
> > >
> > > George
> > >