SQL Parameter Query - Rowset Position Cannot be Restarted
I have the following code:
It works in that my query executes and if I remove the .MoveFirst statement (bold below), but I need that statement in there as my records are sorted in order and I refer to it throughout the program.
This is the first time I open the recordset and when it gets to that .movefirst statement below I get
"An Error Has Occurred - Rowset position cannot be restarted"
Any idears? I notice using a debug.print that it's not setting the cursor type/location to what I specify
Code:
strSQL = "My QUERY GOES HERE"
Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command
With objCommand
.ActiveConnection = SQLCon
.CommandType = adCmdText
.CommandText = strSQL
.Prepared = True
.Parameters.Append .CreateParameter("ClinicDate", adDate, adParamInput, , dtDate)
.Parameters.Append .CreateParameter("Institution", adChar, adParamInput, 4, strInstitution)
rsGetTodaysData.CursorType = adOpenDynamic
Set rsGetTodaysData = objCommand.Execute
Set objCommand = Nothing
End With
With rsGetTodaysData
If .BOF <> True And .EOF <> True Then
.MoveFirst
Do Until .EOF = True
If FormInUse.Name = "frmMain" Then
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
Think I got it.
Changed the line "Set rsGetTodaysData = objCommand.Execute" to say "rsGetTodaysData.Open objCommand". That runs my query and by checking the cursor location and type with a debug.print statement, it also sets those appropriately.
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
Don't you need rsGetTodaysData.CursorLocation=adUseClient?
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
Everything i found to that Error-Message points to CursorLocation=adUseClient missing,
since adUseServer is the default if omitted
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
I had a Set rsGetTodaysData = New Recordset statement at the top of that sub.
I don't know why, but even though I specified a cursor location and type it wasn't "taking". Not until I changed that statement to rsGetTodaysData.Open objCommand did it work. I had the adUseClient in there earlier and it wasn't working. That's part of it I know but the way I was opening the recordset was causing issues somehow.
Re: SQL Parameter Query - Rowset Position Cannot be Restarted
Consider the following lines from the original snippet:
rsGetTodaysData.CursorType = adOpenDynamic
Set rsGetTodaysData = objCommand.Execute
The second one actually creates a brand new recordset. Whatever properties you set before that are gone. It's unnecessary even to allocate a recordset before setting the reference to a brand new one.
How can you control the CursorLocation of this newly minted recordset?
Just set Connection.CursorLocation to whatever before Execute-ing the command. Must admit this is an obscure API design by ADO team, probably an after-thought.
cheers,
</wqw>