ADO.net update problem with VB and MS Access

[CPEdison]

Greetings all,

This is a repost. I had to put this down for a while, but now I have to
get it running.

I am using an Access Database to store question information for a
questionnaire application. I am using VB.net as the front end, and
ADO.net to retrieve the information from the database. My data is bring
displayed in a DataGrid bound to a dataset. All of my calls to the
database are to parameter queries.

The problem that I am having is with the update command. When I call
the update method for my data adapter, and pass a dataset containing
just the changed records, only one record shows the change. I know that
parameter queries work because data is being returned and update, just
not in the quantities that I want.

From what I have read, the update method in ADO.NET should be able to
process a batch change, but the only specific information I have been
able to find concerns SQL Server and not MS Access. The only
information I have been able to find regarding the update method and MS
Access were for single records.

Does ADO.net send each update request separately, waiting for
conformations between requests, or does it send all of the changes at
once? I know, if you write the queries properly, SQL server can process
the data in a lump sum, but I do not believe Access has that ability.

Below is a copy of the offending code. Any help on this would be
greatly appreciated.

Thanks in advance,
Edison

Private Sub SaveData()
Dim lRetVal As Long
Dim oQuestions As Questions = New Questions
Dim oDS_Changes As DataSet = New DataSet
Dim sMsg As String

'Stop current edits
'Me.BindingContext(m_oDS).EndCurrentEdit()

'Grab all changed rows
oDS_Changes = m_oDS.GetChanges

'Check to see if any changes have been made
If (Not (oDS_Changes) Is Nothing) Then
sMsg = "Are you sure you want to save these " & _
oDS_Changes.Tables(0).Rows.Count() & " rows to the database?" _
lRetVal = MsgBox(sMsg, Microsoft.VisualBasic.MsgBoxStyle.Question + _
Microsoft.VisualBasic.MsgBoxStyle.YesNo, "Save Records")
Select Case lRetVal
Case vbYes
Try
' Save all changes
oQuestions.SaveData(oDS_Changes)
m_oDS.AcceptChanges()
Catch updateException As System.Exception
Throw updateException
Catch e As Exception
Throw e
End Try
Case vbNo
'Do nothing
End Select
End If
End Sub

Public Function SaveData(ByVal oDS As DataSet) As String
Dim strMsg As String
Dim lngRecsAffected As Long

Try
If (Not (oDS) Is Nothing) Then
'open connection to Database
m_oCn.Open()
'attempt to update the data source
da.Update(oDS, strClassName)
End If
Catch updateException As System.Exception
Throw updateException
Finally
'Close the Database Connection
m_oCn.Close()
End Try

End Function

Dim cmdSel As OleDbCommand
Dim cmdIns As OleDbCommand
Dim cmdUpd As OleDbCommand
Dim cmdDel As OleDbCommand
Dim strSQL As String

'Setup Select Command
strSQL = "QryQuestion"
cmdSel = New OleDbCommand(strSQL, m_oCn)
cmdSel.CommandType = CommandType.StoredProcedure

'Setup Insert Command
strSQL = "addqryQuestion"
cmdIns = New OleDbCommand(strSQL, m_oCn)
cmdIns.CommandType = CommandType.StoredProcedure
cmdIns.Parameters.Add("QuestionText", OleDbType.LongVarWChar,
255, "Question_Text")
cmdIns.Parameters.Add("CorrectAnswer", OleDbType.BigInt, 1,
"Correct_Answer")
cmdIns.Parameters.Add("QuestionNumber", OleDbType.BigInt, 1,
"Question_Number")

'Setup Update Command
strSQL = "updqryQuestion"
cmdUpd = New OleDbCommand(strSQL, m_oCn)
cmdUpd.CommandType = CommandType.StoredProcedure
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionNumber",
OleDbType.BigInt, 50, "Question_Number"))
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionText",
OleDbType.LongVarWChar, 255, "Question_Text"))
cmdUpd.Parameters.Add(New OleDbParameter("@CorrectAnswer",
OleDbType.BigInt, 50, "Correct_Answer"))
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionID",
OleDbType.BigInt, 50, "Question_ID")).SourceVersion =
DataRowVersion.Original

'Setup Delete Command
strSQL = "delqryQuestion"
cmdDel = New OleDbCommand(strSQL, m_oCn)
cmdDel.CommandType = CommandType.StoredProcedure
cmdDel.Parameters.Add("QuestionID", OleDbType.BigInt, 50,
"Question_ID")

initalizeDataAdapter(cmdSel, cmdIns, cmdUpd, cmdDel)

End Sub

Protected Sub initalizeDataAdapter(ByVal cmdSel As OleDbCommand, ByVal
cmdIns As OleDbCommand, ByVal cmdUpd As OleDbCommand, ByVal cmdDel As
OleDbCommand)

'Create and Setup Data Adapter
da = New OleDbDataAdapter

'Setup Select Command
da.SelectCommand = cmdSel

'Setup Insert Command
da.InsertCommand = cmdIns

'Setup Update Command
da.UpdateCommand = cmdUpd

'Setup Delete Command
da.DeleteCommand = cmdDel

'Destroy connection object
' m_oCn.Close()

End Sub

 

[William Ryan eMVP]

Hi CPEdison

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

Greetings all,

This is a repost. I had to put this down for a while, but now I have to
get it running.

I am using an Access Database to store question information for a
questionnaire application. I am using VB.net as the front end, and
ADO.net to retrieve the information from the database. My data is bring
displayed in a DataGrid bound to a dataset. All of my calls to the
database are to parameter queries.

The problem that I am having is with the update command. When I call
the update method for my data adapter, and pass a dataset containing
just the changed records, only one record shows the change. I know that
parameter queries work because data is being returned and update, just
not in the quantities that I want.

--Ok, from the rest of the question it sounds like two issues 1) Only one
record is showing changed when you are expecting more than 1 2) Does
ADO.NET currently handle batch updates?

This should explain the first part in depth
http://www.knowdotnet.com/articles/exceldatasource.html - or at least how to
try to diagnose it.

From what I have read, the update method in ADO.NET should be able to
process a batch change, but the only specific information I have been
able to find concerns SQL Server and not MS Access. The only
information I have been able to find regarding the update method and MS
Access were for single records.

--The way the data adapter runs updates, it goes Row by row, verifying the
rowstate of each row it comes across. Then, depending on the rowstate, it
decides which command to fire (Insert/Delete/Modify or do nothing) and
typically will use the column mappings specified to get the parameter values
that it needs. This is completely dependent on the way you roll your logic
(the last part regarding parameters) but the row by row checks are the
behavior of the adapter in the current implementation. Now, your particular
DB may or may not be able to handle batch updating, but that's done outside
of the context of ADO.NET (at least until v 2.0) so you would have to write
specialized logic to get this to work assuming your db actually supported it
(which AFAIK Access does not). However, if you want to call the .Update
method, it's goign to do those row by row checks so if you wanted to
circumvent this, you won't be able to just specify a property or somethign,
you'd need to do some fancy footwork to build the batched statement and then
submit it all at once (again, this would only be possible if your DB
supported such featues in the first place. With Select statements though,
you can do this pretty easy (but not with Access) and simply nest a while
dataReader.Read inside a while .NextResult loop (if you're using connected
objects) or by specifying the table mappings if you're using an adapter.

Now, in ADO.NET 2.0, SQL Server will support both BulkInserts
http://www.knowdotnet.com/articles/bulkcopy_intro1.html as well as Batch
updates. However, this is pretty tricky. Why? Well b/c it's dependent on
the DB implementation as well so a given implementation may support say a
maximum of 2,000 parameters. As such, in order to know how many records you
can specify, you'd need to calculate the number of params in a query and
divide 2,000 by that number so you could cacluate the maximum that will work
(Unless of course you want to submit a number you know is less than that).
It's a great feature, but results and benefits will vary greatly depending
on the DB (and I'm guessing Access may come up a little short again - but
that's purely conjecture on my part.

Does ADO.net send each update request separately, waiting for
conformations between requests, or does it send all of the changes at
once?

--Yes, for update stuff. However for selects you can definitely pull
multiple queries over as I touch on above.
I know, if you write the queries properly, SQL server can process

the data in a lump sum, but I do not believe Access has that ability.

--Sql Server definitely has this ability, and Oracle does too. I don't use
Access but I'm 99% sure it lacks this ability. I'll see if I can find a
link to confirm it, but I'm positive I've read that currently it's not
supported.

Also, remember that when Update is called, each row walked through, it's
state determined and then a decision is made for which command to use.
After the udpate, .AcceptChanges is automatically called for you. You are
calling it manually each time here and while it doesn't look like it's
hurting anything, I'd get rid of it b/c it muddies the water a bit (making
it look like there's a specific reason for calling it) and might cause some
problems later on down the road if the logic changes. Another thing in
ADO.NET 2.0 is that there's an .AcceptChangesDuringUpdate feature which will
allow you to specify false - so that you can gain a good bit more control
over when/how .AcceptChanges is called. In the 2.0 context, calling
AcceptChanges manually may make a lot of sense if you have this set to false
on purpose. It's easy to confuse what AcceptChanges does and I speak to it
in that article above (for instance, if you have a 100 record table, call
..Delete on each 100 rows, you still have 100 records in that table - Until
you call acceptchanges). You may already be familiar with this but I
mention it as a general FYI b/c some other folks may not be aware of it - I
know I got it confused a few times when I was first learning.

HTH,

Bill

 

[CPEdison]

Bill,

Thanks for the help. I was finally able to get it to work, but not the
way I wanted it to.

I had to hard Code the SQL command inside my module instead of using
the parameter query (Stored Procedure) inside of MS Access. The SQL is
Identical to the stored procedure, but for some reason ADO.net and
Access were able to talk better this way. I would have much preferred to
use the stored procedure, but hopefully the users will not notice any
performance difference.

I have attached the updated code below in case anyone else has the
problems. Again, thanks for all your help.

Edison

Public Sub New()

strClassName = "Questions"
InitializeConnection()

Dim cmdSel As OleDbCommand
Dim cmdIns As OleDbCommand
Dim cmdUpd As OleDbCommand
Dim cmdDel As OleDbCommand
Dim strSQL As String

'Setup Select Command
strSQL = "Select Question_ID, Question_Text, Correct_Answer,
Question_Number From Tbl_Questions"
cmdSel = New OleDbCommand(strSQL, m_oCn)
cmdSel.CommandType = CommandType.Text

'Setup Insert Command
strSQL = "Insert into Tbl_Questions(Question_Text,
Correct_Answer, Question_Number) values (@QuestionText, @CorrectAnswer,
@QuestionNumber)"
cmdIns = New OleDbCommand(strSQL, m_oCn)
cmdIns.CommandType = CommandType.Text
cmdIns.Parameters.Add("@QuestionText", OleDbType.LongVarWChar,
255, "Question_Text")
cmdIns.Parameters.Add("@CorrectAnswer", OleDbType.BigInt, 1,
"Correct_Answer")
cmdIns.Parameters.Add("@QuestionNumber", OleDbType.BigInt, 1,
"Question_Number")

'Setup Update Command
strSQL = "Update Tbl_Questions Set Question_Number =
@QuestionNumber, Question_Text = @QuestionText, Correct_Answer =
@CorrectAnswer Where Question_ID = @QuestionID"
cmdUpd = New OleDbCommand(strSQL, m_oCn)
cmdUpd.CommandType = CommandType.Text
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionNumber",
OleDbType.BigInt, 50, "Question_Number"))
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionText",
OleDbType.LongVarWChar, 255, "Question_Text"))
cmdUpd.Parameters.Add(New OleDbParameter("@CorrectAnswer",
OleDbType.BigInt, 50, "Correct_Answer"))
cmdUpd.Parameters.Add(New OleDbParameter("@QuestionID",
OleDbType.BigInt, 50, "Question_ID")).SourceVersion =
DataRowVersion.Original

'Setup Delete Command
strSQL = "Delete Question_ID, Question_Text, Correct_Answer,
Question_Number From Tbl_Questions Where Question_ID = @QuestionID"
cmdDel = New OleDbCommand(strSQL, m_oCn)
cmdDel.CommandType = CommandType.Text
cmdDel.Parameters.Add("@QuestionID", OleDbType.BigInt, 50,
"Question_ID")

initalizeDataAdapter(cmdSel, cmdIns, cmdUpd, cmdDel)

End Sub