Update Access data

[Guest]

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

 

[Chris]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?