VB6/ADO: Using Recordset.Sort on a recordset from a stored procedure

[Barry Seymour]

I've tried to find the answer to this on the Internet but am coming up cold.

I create a connection to MS SQL Server 2000 from my VB6 app, ADO 2.6, then I
create a command object and run a stored procedure with a clientside cursor
to get a static recordset. (The stored procedure itself joins on multiple
tables.)

I am then unable to use the Recordset.Sort method. I get Error 3251,
"Current Provider does not support the necessary interfaces for sorting or
filtering."

Any ideas? Thanks in advance.

- Barry Seymour

[William (Bill) Vaughn]

Let's see some code... It sounds like you're not getting a client-side
cursor.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Post by Barry Seymour
I've tried to find the answer to this on the Internet but am coming up cold.
I create a connection to MS SQL Server 2000 from my VB6 app, ADO 2.6, then I
create a command object and run a stored procedure with a clientside cursor
to get a static recordset. (The stored procedure itself joins on multiple
tables.)
I am then unable to use the Recordset.Sort method. I get Error 3251,
"Current Provider does not support the necessary interfaces for sorting or
filtering."
Any ideas? Thanks in advance.
- Barry Seymour

[Barry Seymour]

"William (Bill) Vaughn" <***@nwlink.com> wrote in message news:***@TK2MSFTNGP11.phx.gbl...
| Let's see some code... It sounds like you're not getting a client-side
| cursor.

Bless yer heart, and thanks in advance...

I thought I was using client side cursors...


Private Function DataGet() As Boolean

Dim strRowsFor As String, lngTick As Long

Me.MousePointer = vbHourglass
Me.Caption = "Connecting..."

Set madoConn = New ADODB.Connection
With madoConn
'password obscured...
.ConnectionString = "Provider=SQLOLEDB.1;Password=XXXXX;Persist
Security Info=True;User ID=xBuy;Initial Catalog=xBuy;Data Source=SQLX"
.CommandTimeout = 30
.ConnectionTimeout = 15
.Open
End With

Me.Caption = "Setting Up Command Object..."
Set madoCmd = New ADODB.Command
With madoCmd
Set .ActiveConnection = madoConn

.CommandType = adCmdStoredProc
.CommandText = "dbo.doc_main"
.Parameters("@notxml") = "1"
strRowsFor = "2938, 1862, 6214, 2940, 3035, 5268, 2941, 2939, 6102,
6232, 6324, 6467, 7677, 7793, 8848, 3190, 3611, 5195, 6233, 5849, 4392,
2219, 5695, 5988, 6207, 6247, 6271, 6757, 5728, 5972, 8530, 6064, 6238,
7512, 7913, 8561, 6007, 7433, 6184, 7870, 7942, 8865, 8887, 7533, 5413"
.Parameters("@RowsFor") = strRowsFor

Set madoRS = New ADODB.Recordset
With madoRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
End With

Me.Caption = "Fetching Data..."
lngTick = GetTickCount()
Set madoRS = .Execute()
Debug.Print "Fetched data in " & (GetTickCount() - lngTick) / 1000 &
" seconds."
End With ' madocmd

DataGet = True

Me.MousePointer = vbDefault

End Function

[Val Mazur]

Hi Barry,

Based on your code recordset will be opened on a server side. It happens
because you are using Execute method of the Command to open recordset. In
this case ADO uses settings of the Command object to open recordset and
ignores settings, which you set for the Recordset variable. To open
recordset on a client side, using Command, you need to set CursorLocation
property of your Connection (not recordset) to adUseClient
--
Val Mazur
Microsoft MVP

Post by Barry Seymour
| Let's see some code... It sounds like you're not getting a client-side
| cursor.
Bless yer heart, and thanks in advance...
I thought I was using client side cursors...
Private Function DataGet() As Boolean
Dim strRowsFor As String, lngTick As Long
Me.MousePointer = vbHourglass
Me.Caption = "Connecting..."
Set madoConn = New ADODB.Connection
With madoConn
'password obscured...
.ConnectionString = "Provider=SQLOLEDB.1;Password=XXXXX;Persist
Security Info=True;User ID=xBuy;Initial Catalog=xBuy;Data Source=SQLX"
.CommandTimeout = 30
.ConnectionTimeout = 15
.Open
End With
Me.Caption = "Setting Up Command Object..."
Set madoCmd = New ADODB.Command
With madoCmd
Set .ActiveConnection = madoConn
.CommandType = adCmdStoredProc
.CommandText = "dbo.doc_main"
strRowsFor = "2938, 1862, 6214, 2940, 3035, 5268, 2941, 2939, 6102,
6232, 6324, 6467, 7677, 7793, 8848, 3190, 3611, 5195, 6233, 5849, 4392,
2219, 5695, 5988, 6207, 6247, 6271, 6757, 5728, 5972, 8530, 6064, 6238,
7512, 7913, 8561, 6007, 7433, 6184, 7870, 7942, 8865, 8887, 7533, 5413"
Set madoRS = New ADODB.Recordset
With madoRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
End With
Me.Caption = "Fetching Data..."
lngTick = GetTickCount()
Set madoRS = .Execute()
Debug.Print "Fetched data in " & (GetTickCount() - lngTick) / 1000 &
" seconds."
End With ' madocmd
DataGet = True
Me.MousePointer = vbDefault
End Function

[Barry Seymour]

Thanks, I'll give it a shot.

"Val Mazur" <***@hotmail.com> wrote in message news:***@TK2MSFTNGP10.phx.gbl...
| Hi Barry,
|
| Based on your code recordset will be opened on a server side. It happens
| because you are using Execute method of the Command to open recordset. In
| this case ADO uses settings of the Command object to open recordset and
| ignores settings, which you set for the Recordset variable. To open
| recordset on a client side, using Command, you need to set CursorLocation
| property of your Connection (not recordset) to adUseClient
|
| --
| Val Mazur
| Microsoft MVP
|
|
| "Barry Seymour" <***@spamcop.net> wrote in message
| news:***@TK2MSFTNGP14.phx.gbl...
| >
| > "William (Bill) Vaughn" <***@nwlink.com> wrote in message
| > news:***@TK2MSFTNGP11.phx.gbl...
| > | Let's see some code... It sounds like you're not getting a client-side
| > | cursor.
| >
| > Bless yer heart, and thanks in advance...
| >
| > I thought I was using client side cursors...
| >
| >
| > Private Function DataGet() As Boolean
| >
| > Dim strRowsFor As String, lngTick As Long
| >
| > Me.MousePointer = vbHourglass
| > Me.Caption = "Connecting..."
| >
| > Set madoConn = New ADODB.Connection
| > With madoConn
| > 'password obscured...
| > .ConnectionString = "Provider=SQLOLEDB.1;Password=XXXXX;Persist
| > Security Info=True;User ID=xBuy;Initial Catalog=xBuy;Data Source=SQLX"
| > .CommandTimeout = 30
| > .ConnectionTimeout = 15
| > .Open
| > End With
| >
| > Me.Caption = "Setting Up Command Object..."
| > Set madoCmd = New ADODB.Command
| > With madoCmd
| > Set .ActiveConnection = madoConn
| >
| > .CommandType = adCmdStoredProc
| > .CommandText = "dbo.doc_main"
| > .Parameters("@notxml") = "1"
| > strRowsFor = "2938, 1862, 6214, 2940, 3035, 5268, 2941, 2939,
6102,
| > 6232, 6324, 6467, 7677, 7793, 8848, 3190, 3611, 5195, 6233, 5849, 4392,
| > 2219, 5695, 5988, 6207, 6247, 6271, 6757, 5728, 5972, 8530, 6064, 6238,
| > 7512, 7913, 8561, 6007, 7433, 6184, 7870, 7942, 8865, 8887, 7533, 5413"
| > .Parameters("@RowsFor") = strRowsFor
| >
| > Set madoRS = New ADODB.Recordset
| > With madoRS
| > .CursorLocation = adUseClient
| > .CursorType = adOpenStatic
| > End With
| >
| > Me.Caption = "Fetching Data..."
| > lngTick = GetTickCount()
| > Set madoRS = .Execute()
| > Debug.Print "Fetched data in " & (GetTickCount() - lngTick) /
1000
| > &
| > " seconds."
| > End With ' madocmd
| >
| > DataGet = True
| >
| > Me.MousePointer = vbDefault
| >
| > End Function
| >
| >
| >
| >
|
|