adodb, driving me nuts, multiple queries

B

Berlin Brown

I am trying run a higher level query and then inside the loop another
query based on the previous query, the higher query runs once and the
subqueries run 3 times, but after that I cant the higher query to run
again. I get the EOF and BOF error, I have tried almost all
combinations of adOpenStat...options etc, to run that higher query. I
know it is an issue of running multiple queries on a CONNECTION some
readonly this or static something, anyway, here is the code:


HUH!!!
0
0
2239280.79565957
1665496.12365957
0
0
Err: Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
---------------------------------------------------------------


Public Function RunCircleCalculate()

On Error GoTo SQLCircleErr

Dim rstX As ADODB.Recordset
Dim res() As Double


Dim sqlSelectErr As ADODB.Command
Set sqlSelectErr = New ADODB.Command
sqlSelectErr.ActiveConnection = CurrentProject.Connection
sqlSelectErr.CommandType = adCmdText
sqlSelectErr.CommandText = sqlRunSelectErr

Dim sqlSelectErrInternal As ADODB.Command
Set sqlSelectErrInternal = New ADODB.Command
sqlSelectErrInternal.ActiveConnection = CurrentProject.Connection
sqlSelectErrInternal.CommandType = adCmdText
sqlSelectErrInternal.CommandText = sqlRunSelectErrInternal

' /// ADODB.adOpenStatic & ADODB.adLockOptimistic

Dim rstInternal As ADODB.Recordset
Set rstX = sqlSelectErr.Execute(, , False)
'rstX.CursorType = 0
'rstX.LockType = 3


Dim xus As String
Dim yus As String
Dim xds As String
Dim yds As String
Dim plen As String
Dim XUS_CONN As String
Dim YUS_CONN As String
Dim XDS_CONN As String
Dim YDS_CONN As String
Dim Main_DataConnectionsPLEN As String
Dim calcLen As String
Dim connTYPE As String

Dim fid As String

Dim pt0x As Double
Dim pt0y As Double
Dim pt1x As Double
Dim pt1y As Double
Dim rad0 As Double
Dim rad1 As Double

rstX.MoveFirst
While Not (rstX.EOF And Not rstX.BOF)


xus = rstX("XUS").value
yus = rstX("YUS").value
xds = rstX("XDS").value
yds = rstX("XDS").value
plen = rstX("PLEN").value
fid = rstX("FID").value

PrintlnMod ("HUH!!!")

Dim errType As Integer
errType = 0

'///
'/// First Find out what type of error do we have
'///
Dim tmpDbl01 As Double
Dim tmpDbl02 As Double

tmpDbl01 = CDbl(xus)
tmpDbl02 = CDbl(xds)

If (tmpDbl01 < 2) And (tmpDbl02 > 100) Then

'///
'/// Perform INVALID US and VALID DS query ( USE US/DS )
'///

errType = 1

pt0x = CDbl(xds)
pt0y = CDbl(yds)
rad0 = CDbl(plen)

Else

'///
'/// Perform INVALID DS and VALID US query ( USE XS/YS )
'///

errType = 0

pt0x = CDbl(xus)
pt0y = CDbl(yus)
rad0 = CDbl(plen)

End If


If errType = 1 Then

'/// get RAD01 and PT01(US,DS)

Set rstInternal = sqlSelectErrInternal.Execute(,
Array("US", "DS", CInt(fid)), ADODB.adOpenStatic &
ADODB.adLockOptimistic)
'rstInternal.MoveFirst
While Not (rstInternal.EOF And rstInternal.BOF)

XUS_CONN = rstInternal("XUS_CONN").value
YUS_CONN = rstInternal("YUS_CONN").value
XDS_CONN = rstInternal("XUS_CONN").value
YDS_CONN = rstInternal("XUS_CONN").value

Main_DataConnectionsPLEN = rstInternal("PLEN").value
connTYPE = rstInternal("CONNTYPE").value

Dim tmpDblRad As Double
tmpDblRad = CDbl(rstInternal("CALCLEN").value)
If tmpDblRad > 100000# Then
rad1 = CDbl(Main_DataConnectionsPLEN)
Else
rad1 = tmpDblRad
End If

If connTYPE = "DS" Then
pt1x = CDbl(XUS_CONN)
pt1y = CDbl(YUS_CONN)
Else
pt1x = CDbl(XDS_CONN)
pt1y = CDbl(YDS_CONN)
End If

res = PerformCircleCalculateExample(pt0x, pt0y, pt1x,
pt1y, rad0, rad1)
PrintlnMod (res(0))
PrintlnMod (res(1))

rstInternal.MoveNext
Wend
rstInternal.Requery
rstInternal.Requery
rstInternal.Close
Set rstInternal = Nothing

ElseIf errType = 0 Then

'///
'/// Get RAD AND PT (XS,YS)
'///
res = PerformCircleCalculateExample(pt0x, pt0y, pt1x, pt1y,
rad0, rad1)

Else

'///
'/// dont calculate, we shouldnt get here

PrintlnMod ("Invalid Calculate Code")

End If


rstX.MoveNext
Wend

rstX.Close
Set rstX = Nothing
 
Top