Hello,

Is it possible to connection pool with Microsoft Access? I am coding in
VB.NET and my application is giving me errors because the connection is
still open and fetching records.

Thanks!

On Sun, 17 Aug 2003 11:58:15 -0700, "Jim" <(E-Mail Removed)> wrote:

¤ Sure, here is the bit of code throwing the exception.
¤
¤ Try
¤ objCommand.CommandText = "INSERT INTO
¤ advancementHistory (agreementID, paymentReceivedDate, advanceAmount) VALUES
¤ (" & agreementID & ", #" & dtEditAdvanceReceived.Text & "#, '" &
¤ CorrectStrings(FormatNumber(CDbl(txtEditAdvance.Text), 2)) & "')"
¤ objCommand.ExecuteNonQuery()
¤ ''Initialize balance data
¤ Dim dblCurrentBalance As Double = 0
¤ objCommand.CommandText = "SELECT TOP 1 balanceID,
¤ currentBalance FROM currentBalance WHERE agreementID = " & agreementID
¤ objDR = objCommand.ExecuteReader
¤ If objDR.Read = True Then
¤ dblCurrentBalance = objDR.GetDecimal(1)
¤ ''Update current balance
¤ objCommand.CommandText = "UPDATE currentBalance
¤ SET currentBalance = '" & CorrectStrings(FormatNumber((dblCurrentBalance -
¤ CDbl(txtEditAdvance.Text)), 2)) & "' WHERE balanceID = " _
¤ & objDR.GetInt32(0)
¤ Else
¤ ''Insert record into current balance
¤ objCommand.CommandText = "INSERT INTO
¤ currentBalance (agreementID, currentBalance) VALUES (" & agreementID & ", '"
¤ & _
¤ CorrectStrings(FormatNumber((-1 *
¤ CDbl(txtEditAdvance.Text)), 2)) & "')"
¤ End If
¤ objDR.Close()
¤ objCommand.ExecuteNonQuery()
¤ Catch exp5 As Exception
¤ MessageBox.Show(exp5.Message, "Error Saving Advance
¤ Payment", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤ myUpdater.DoError()
¤ bolSaveAborted = True
¤ End Try
¤
¤ The code works fine when I run if locally, but if we use the database over
¤ the network it gives the open/fetching error.


On which line does it fail and what is the error message?


Paul ~~~ (E-Mail Removed)
Microsoft MVP (Visual Basic)

Since I couldn't replicate the error on my development machine and I didn't
have time to try more extreme error trapping, I assume the error was caused
by resetting the CommandText while a DataReader was still open. I found
that if I set the CommandText to a string variable, close the DataReader,
the reset the Command Object's CommandText to the string, and finally
execute - everything works fine.



"Paul Clement" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> On Sun, 17 Aug 2003 11:58:15 -0700, "Jim" <(E-Mail Removed)> wrote:
>
> ¤ Sure, here is the bit of code throwing the exception.
<SNIP CODE>
> ¤
> ¤ The code works fine when I run if locally, but if we use the database
over
> ¤ the network it gives the open/fetching error.
>
>
> On which line does it fail and what is the error message?
>
>
> Paul ~~~ (E-Mail Removed)
> Microsoft MVP (Visual Basic)