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 4 2073
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
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
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
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
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?
"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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
12 posts
views
Thread by jimserac |
last post: by
|
2 posts
views
Thread by Niyazi |
last post: by
|
16 posts
views
Thread by robert |
last post: by
|
1 post
views
Thread by Darn |
last post: by
|
3 posts
views
Thread by Roy |
last post: by
|
11 posts
views
Thread by John |
last post: by
|
2 posts
views
Thread by Scotty |
last post: by
| |
13 posts
views
Thread by Terry Olsen |
last post: by
| | | | | | | | | | |