Calling MoveFirst on a recordset requeries the database

[pokasick]

I have a recordset that I use to load a grid on a form. I added a filter option to the grid. The filter works, but when I set the filter command, it is running the same query as the first time then applying the filter. I'm using the SQL profiler to optimize the program. I've been trying the different CursorTypes and cursor locations with no change in the behavior.

The recordset does not need to be updateable. I'm using SQL Server 2000. Can anyone see what I'm doing wrong?

I created a small program that simulates what I'm doing so that I can try the different options easier than doing it in the actual application. Here's the button event code from my test app. This is all of the code.

Thanks,
Paul

Private Sub Command2_Click()
Dim objCommand As ADODB.Command
Dim objRecordSet As ADODB.Recordset
Dim sSQL As String
Dim objSQLConnection As ADODB.Connection

Set objSQLConnection = New ADODB.Connection
Call objSQLConnection.Open("LocalServer&quot
Set objCommand = New ADODB.Command
objCommand.ActiveConnection = objSQLConnection

sSQL = sSQL & " Use Pubs SELECT Stor_ID FROM Sales WHERE Stor_ID like '7%'"
objCommand.CommandText = sSQL

Set objRecordSet = New ADODB.Recordset
objRecordSet.CursorLocation = adUseClient
objRecordSet.CursorType = adOpenKeyset
Set objRecordSet = objCommand.Execute

'Simulate loading a grid...
While Not objRecordSet.EOF
Debug.Print objRecordSet!Stor_ID
objRecordSet.MoveNext
Wend

'Set a filter - This is where it's executing the query again
objRecordSet.Filter = "Stor_ID like '70%'"

'Reload the grid
If Not objRecordSet.EOF Then
Debug.Print vbCrLf & vbCrLf & "Second Time..."
While Not objRecordSet.EOF
Debug.Print objRecordSet!Stor_ID
objRecordSet.MoveNext
Wend
End If

objSQLConnection.Close

End Sub

 

[kmfna]

Hey, is your question, how to stop the query from running again??

Kevin

 

[pokasick]

Yes, sorry I didn't make that clear.

Paul

 

[Sideman]

Hi pokasick.
I'm no expert in SQL, but it looks like you may be filtering the recordset twice...once with the WHERE clause and again with the Filter property using the same criteria - "Stor_ID Like '7%'". That will return the same data both times.
Maybe you are intending to Sort the recordset the second time for use in the Grid??
I may be way off base, but just wanted to try my hand at helping as so many have done for me.

Good luck.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Sideman]

Oops, forgot to mention...the Filter property returns a filtered recordset, that could be why your query seems to be running twice. If you search MSDN for "Filter Property (ADO) you'll see the whole skinny on Filtering.

Good luck, again.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[pokasick]

Sideman,

Thanks for your reply. The query in the example has a where clause of Stor_ID like '7%. The filter is "Stor_ID like '70%'" to show only stores that have an ID starting with 70. I thought that by using the filter property on the current recordset, I could save some server processing by not executing a query again. Everything I've read about the filter property makes it sound like it works on the current recordset, and not hitting the server again. It seems strange to me that applying a filter would cause the recordset to requery the database. I used the SQL Profiler to discover that the query is running twice, once at the initial load of the recordset and again when assigning.

I'm currently reading about cursors and filters on the MSDN site. If if find anything definitive about filters executing queries, I'll post it here.

You mentioned that "the Filter property returns a filtered recordset". The MSDN filter property page states "Use the Filter property to selectively screen out records in a Recordset object." The difference between the two is significant, and I'm wondering if I'm not looking in the right place. Did you find that in help in VB (F1), or Online?

Paul

 

[AkutaSame]

One option is to modify the query if you are having so many issues with the filter property. Just add onto the end of your string the "filter"... It will minimize the amount of processing you'll have to do on the server end anyways (because it will result in a smaller recordset returned).

Just an idea.