ADO Connection Object

[MattyBoy]

Can anyone tell me if there is anything wrong with this bit of code I am using to return a resultset asynchronously?

.Open strSQL, GetConnection, adOpenForwardOnly, adLockOptimistic

--get connection :
Private Function GetConnection() As ADODB.Connection
Dim strConnection As String

If madoConn Is Nothing Then
Set madoConn = New ADODB.Connection
strConnection = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" _
& mstrDSN & ";Data Source=" & mstrServer
madoConn.Open strConnection
End If

Set GetConnection = madoConn

End Function


I keep getting eof as true when I check after the rst has been opened, and I know there is data there for the query I am sending through??????!!
This is major hassle PLEASE HELP!!!!!

MattyBoy
I think you must code it like this


If madoConn Is Nothing Then
Set madoConn = New ADODB.Connection
madoConn.Connectionstring = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" _
& mstrDSN & ";Data Source=" & mstrServer
madoConn.Open
madoConn.CursorLocation = adUseClient
End If

Set GetConnection = madoConn


I hope this works.

[MattyBoy]

I get a connection and the connection state is 1, ie open. I retrieve one set of values using the rst.open method and then, using a new rst try and get more values but my resultset only gives me 1 row, when there should be many more! If I remake the connection using your code it will work and return all the rows! What is going on...!!!!?!?!?

Could it be in your sql string ?

[MattyBoy]

I dont think so as when I paste the sql string into query analyser I receive all the rows!

[Lafor]

What is the exact sql u're using to retrieve the rows
..
May have an idea...