ADO problem

[Joel Whitehouse]

Hello all:


After the 38th iteration of a loop that calls this code, I get the
following error:

An unhandled exception of type
'System.Runtime .InteropService s.COMException' occurred in Antenna Test
Range Control.exe

Additional information: Unspecified error


Here's my code:

Private Sub SendQuery(ByVal query As String)
Dim command As New ADODB.Command
'Create a connection object
Dim adoConnection As New ADODB.Connectio n
adoConnection.C onnectionString =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Docum ents and
Settings\Antenn a\My Documents\testd ata.mdb;"

adoConnection.O pen() '<-----------------Error happens here
command.ActiveC onnection = adoConnection
command.ActiveC onnection.Begin Trans()
command.Command Text = query
command.Command Type = CommandTypeEnum .adCmdText
command.Execute ()
command.ActiveC onnection.Commi tTrans()

If adoConnection.S tate = ObjectStateEnum .adStateOpen Then
adoConnection.C lose()
end if
adoConnection = Nothing
End Sub


Any ideas? Thanks!


-Joel

[Ben]

RE: ADO problem

wait a min, you're dealing with MS access. make sure your access dB doesn't
have a DAO *.mdw password authentication. ADO don't go with the concept of
workgroup. Maybe there is a trick to get ADO.net to do that but not that I
am awared off hand. You could work around this thru adodb class, but then
again what's the point to going to ADo when there is ADO.net that is much
more powerful.

Now if you're not using an *.mdw password authentication mechanism then, try
using the explorer (GUI db connector) to see if you could make your
connection cuz based on what I saw here is VB couldn't opened your db so
there must be some authentication issues. try testing northwind.mdb, which
isn't encrypted with password, and see for yourself. your codes should open
it.

-Ben


"Joel Whitehouse" wrote:
[color=blue]
> Hello all:
>
>
> After the 38th iteration of a loop that calls this code, I get the
> following error:
>
> An unhandled exception of type
> 'System.Runtime .InteropService s.COMException' occurred in Antenna Test
> Range Control.exe
>
> Additional information: Unspecified error
>
>
> Here's my code:
>
> Private Sub SendQuery(ByVal query As String)
> Dim command As New ADODB.Command
> 'Create a connection object
> Dim adoConnection As New ADODB.Connectio n
> adoConnection.C onnectionString =
> "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Docum ents and
> Settings\Antenn a\My Documents\testd ata.mdb;"
>
> adoConnection.O pen() '<-----------------Error happens here
> command.ActiveC onnection = adoConnection
> command.ActiveC onnection.Begin Trans()
> command.Command Text = query
> command.Command Type = CommandTypeEnum .adCmdText
> command.Execute ()
> command.ActiveC onnection.Commi tTrans()
>
> If adoConnection.S tate = ObjectStateEnum .adStateOpen Then
> adoConnection.C lose()
> end if
> adoConnection = Nothing
> End Sub
>
>
> Any ideas? Thanks!
>
>
> -Joel
>[/color]

[Cor Ligthert [MVP]]

Re: ADO problem

Joel,

I have the idea that this is not your complete code, otherwise you would not
use this.
[color=blue]
>
> If adoConnection.S tate = ObjectStateEnum .adStateOpen Then
> adoConnection.C lose()[/color]

However, close it without condition.

Just my thought,

Cor

[Joel Whitehouse]

Re: ADO problem

Ben wrote:
[color=blue]
> wait a min, you're dealing with MS access. make sure your access dB doesn't
> have a DAO *.mdw password authentication. ADO don't go with the concept of
> workgroup. Maybe there is a trick to get ADO.net to do that but not that I
> am awared off hand. You could work around this thru adodb class, but then
> again what's the point to going to ADo when there is ADO.net that is much
> more powerful.[/color]

Ben, I don't have any passwords setup... This code seems to work.

I modified the code and added a count variable. When the error occurs,
the variable count = 60. Get that! 60! In addition, I can see teh
effects of every query that this code sends. Why would this run a full
60 times and then break?



Private Sub SendQuery(ByVal query As String)
Static count As Integer

Dim command As New ADODB.Command
'Create a connection object
Dim adoConnection As New ADODB.Connectio n

adoConnection.C onnectionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=C:\Docum ents and Settings\Antenn a\My Documents\testd ata.mdb;"

adoConnection.O pen() '<-----------------Error happens here
command.ActiveC onnection = adoConnection
command.ActiveC onnection.Begin Trans()
command.Command Text = query
command.Command Type = CommandTypeEnum .adCmdText
command.Execute ()
command.ActiveC onnection.Commi tTrans()

If adoConnection.S tate = ObjectStateEnum .adStateOpen Then
adoConnection.C lose()
adoConnection = Nothing

count = count + 1 '<------------Count increments to 60 -
'but error occurs before it can
'increment to 61
End Sub

[Joel Whitehouse]

Re: ADO problem

Further experimentation *always* breaks the sendquery function on the
61st call. Why?

[Paul Clement]

Re: ADO problem

On Tue, 26 Jul 2005 17:29:50 -0500, Joel Whitehouse <joelwhitehouse @gmail.com> wrote:

¤ Hello all:
¤
¤
¤ After the 38th iteration of a loop that calls this code, I get the
¤ following error:
¤
¤ An unhandled exception of type
¤ 'System.Runtime .InteropService s.COMException' occurred in Antenna Test
¤ Range Control.exe
¤
¤ Additional information: Unspecified error
¤
¤
¤ Here's my code:
¤
¤ Private Sub SendQuery(ByVal query As String)
¤ Dim command As New ADODB.Command
¤ 'Create a connection object
¤ Dim adoConnection As New ADODB.Connectio n
¤ adoConnection.C onnectionString =
¤ "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Docum ents and
¤ Settings\Antenn a\My Documents\testd ata.mdb;"
¤
¤ adoConnection.O pen() '<-----------------Error happens here
¤ command.ActiveC onnection = adoConnection
¤ command.ActiveC onnection.Begin Trans()
¤ command.Command Text = query
¤ command.Command Type = CommandTypeEnum .adCmdText
¤ command.Execute ()
¤ command.ActiveC onnection.Commi tTrans()
¤
¤ If adoConnection.S tate = ObjectStateEnum .adStateOpen Then
¤ adoConnection.C lose()
¤ end if
¤ adoConnection = Nothing
¤ End Sub
¤
¤
¤ Any ideas? Thanks!

Try running System.Runtime. InteropServices .Marshal.Releas eComObject on your adoConnection object
after you close it and set it to Nothing.


Paul
~~~~
Microsoft MVP (Visual Basic)

[Joel Whitehouse]

Re: ADO problem

Paul Clement wrote:
[color=blue]
> Try running System.Runtime. InteropServices .Marshal.Releas eComObject on your adoConnection object
> after you close it and set it to Nothing.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)[/color]

This technique appears to release the resources effectively, but I still
get the unspecified error on the 60th try...

I'm so stuck here, and I only have 2 weeks to get this thing working....
Ugh!

-Joel