Query should return with static cursor type

[Nicodemas]

Hello all,

I have been battling with one page for a while, trying to use the COUNT function in SQL, but SQL2000 seems to hate it and keeps giving me an error.

I have tried using the RecordCount property, but it appears as though the function which I use to open a connection, execute some SQL, and return a recordset returns the recordset with an adOpenForward CursorType.

I have tried my best to modify the file to make it return with an adOpenStatic CursorType, and still no luck. I need some advice from some ADO experts.

When I want to perform a query I use these:

strDB = "DatabaseName"
strSQL = "SELECT SomeField FROM SomeTable"
ysnSuccess = adlExecuteSQL(strDB,strSQL,NameofRecordset)

ysnSuccess being a boolean variable for testing purposes.

What do you all suggest?

I have attached the file.


Attached is the file's contents.

[rdoekes]

What I have done in the past is to create the objects outside of the function. This way you maintain the connection for all the operations you would like to do, and also you have full control over your recordset object.

Set cnn = Server.CreateObject("ADODB.COnnection")
cnn.Open "dsn=me"

Set rs = Server.CreateObject("ADODB.Recordset")
'here you set the properties

Set rs = ExecuteDb(cnn, "SELECT field1 FROM table1")

'do stuff with recordset

rs.Close
cnn.Close

Function ExecuteDB(oCnn, strSQL)

Set ExecuteDB = oCnn.Execute(strSQL)
End Function


And there even was a time when I did everything with arrays using the GetRows method

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "dsn=me"
aReturnSet = ExecuteDb(cnn, "select * from table1")
cnn.Close

'do stuff with array
If isArray(aReturnSet) Then
For i = 0 To Ubound(aReturnSet, 2)
Response.Write aReturnSet(0, i) & aReturnSet(1,i)
Next
End If

Function ExecuteDB(oCnn, strsQL)
Dim oRs
Set oRs = oCnn.Execute(strSQL)
If oRs.EOF Then
ExecuteDB = 0
Else
ExecuteDB = oRs.GetRows
End If
oRs.Close
Set oRs = Nothing
End Function
Hope this helps,

-Rogier Doekes

[Nicodemas]

Thanks for all the advice, however, I would like to stay within the bounds of the function, and simply modify it to return a static cursor.

I really do appreciate your answers, and have put them into my library, just in case I need a similar solution in the future.

Can anyone else possibly augment, or summarize the changes to the file that need to take place to return the static cursor?

[Nicodemas]

I found a solution to my current problem that was (exceptionally) easy to implement, although not exactly what I was looking for. I still can't believe this didn't dawn on me..

In the code I simply closed the recordset explicitly, set the cursortype to adOpenStatic, then opened it again.