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  2289 
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jimserac |
last post by:
I had previously posted this in an Access forum
with negative results so will try here.
Although this question specifies an Access database,
I also wish to accomplish this with a large MS SQL...
|
by: Niyazi |
last post by:
Hi,
I have not understand the problem. Before all the coding with few
application everything worked perfectly. Now I am developing Cheque Writing
application and when the cheque is clear the...
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the...
|
by: Darn |
last post by:
Hi all
How do i solve this problem. I'm web developer from Malaysia, the
problem occurs when i ftp an Access database which i'm using for this
particular web site for it's database.The form...
|
by: Roy |
last post by:
Hi Access gurus,
I have a A2K application.The data in the database is updated daily by a
excel download.I have a master n related tables keyed in by a OrderID.I
have a problem in updating data.If...
|
by: John |
last post by:
Hi
I had a working vs 2003 application with access backend. I added a couple
fields in a table in access db and then to allow user to have access to
these fields via app I did the following;
...
|
by: Scotty |
last post by:
I get stuck to write an update, insert and delete command, i am looking for
some help to start
Whats the best way to update 2 tables toe the database (Access)
below my code used to load my...
|
by: mwenz |
last post by:
I am trying to update an Access table using OLEDB in VB.Net 2005. I can add rows but I cannot update them.
Code to instantiate the Access database and table...
Dim conn As New...
|
by: Terry Olsen |
last post by:
I'm using OleDb to connect with an Access Database. I have anywhere from
10 to over 100 records that I need to either INSERT if the PK doesn't
exist or UPDATE if the PK does exist, all in a single...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |
