VB Ado.net timing issue

[Guest]

Good Afternoon,

I get the following error when trying to add a record to and access table:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll. The code runs fine as long as I wait a second or two
between each occurance of it running, but if I try to add the records to
fast the error is generated. I have put a debug.write line at the begining
and
end of the code and it is finishing it before the next write starts so
does
any one know what the issue is?
Here is my code:


Dim strHolderTN = "'" & strTrailerNumber & "'"
Dim strHolderCN = "'" & strContainerNumber & "'"

Dim strSQL As String = "Insert into tblLotTrace(TrailerKey,
CSerialNumber, DockDoor) values(" & strHolderTN & "," & strHolderCN & ","
& intDoor & ")"
Debug.Write(strSQL)
'Stop
Dim objcommand As New OleDbCommand(strSQL, New
OleDbConnection(gsSQLData))
Debug.Write(objcommand.ToString)
objcommand.Connection.Open()

'Dim cmdLotTrace As OleDbCommand = New
OleDbCommand(strSQL,conLotTrace)
'objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()
objcommand.Dispose()
objcommand = Nothing


Thanks in advance!

Dale

 

[Mike McIntyre]

If you are updaing that fast you should consider opening the connection,
looping to do all your inserts, then closing the connection.


--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

 

[W.G. Ryan eMVP]

I'm not sure if the timing is the problem but a few misc observations:

1) First I'd try catch the ExecuteNonQuery and trap OleDbException - see
specifically what exception.ToString() yeilds
2) If you open the connection in a block - you'll definitely want to have a
Finally there that closes the connection - otherwise you can't be sure that
it will close in case of any exception
3) Parameterize the Query statement - there are many reasons for this, the
least of which is that it may be an errant value with an apostrophe for
instance that's causing the exception masking itself as a timing issue.
4) Is there anything else happening in between successive calls to this
routine?

 

[Sahil Malik]

In what kind of situation is the below code being called over and over
again? I seriously suspect you have a re-entrancy issue going on. C# has
"lock", I bet VB.NET has an equivalent keyword - place that around the code
block below. If it works, come back and ask how you could re-architect your
app to make it better.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik