Re: Recordset & Sort
- From: "Daniel Crichton" <msnews@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 8 Mar 2006 17:04:02 -0000
zamdrist@xxxxxxxxx wrote on 8 Mar 2006 07:35:51 -0800:
Was hoping to sort a recordset, returned by a stored procedure after
the fact. While the sort order the stored procedure works for me
otherwise, in this case I need to sort differently. This seemed hopeful
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoproperties.asp
However, Despite using a CursorLocation of adUseClient, depending on
where I place my sort property I get either no effect, or an error
message.
What is the exact text of the error message? I don't remember having a
problem with this before, but I do have a haib tof using ODBC providers.
If I set the sort poperty after the Execute method, I get a message
saying the current providor doesn't support the interfaces for sorting
and filtering. I tried using the OLEDB and native SQL Server providers,
and neither apparently support it, so says the error message.
The Sort property should be independent of the provider when using a client
side recordset.
If I place the Sort property before I execute the command procedure,
The sort property seems to have no effect.
Looking at your code, that is because after you set it you then set the
recordset object to the output of the command object, which resets the
recorset properties to those returned by the command object. This also
changes the client side cursor property. See below for suggested code
change.
Here is my code, keep in mind my connection is returned by yet another
function and otherwise works, thanks for any assistance:
Function ProcessApplicationDataSheet(sDocketNumber As String)
Dim iInventors As Integer 'Number of inventors user has selected
Dim adoConn As ADODB.Connection
Dim adoComm As New ADODB.Command
Dim adoRs As New ADODB.Recordset
Dim adoParam As New ADODB.Parameter
Set adoConn = ConnectSQLServer
adoComm.ActiveConnection = adoConn
adoComm.CommandText = "sp_UTL_PatFormGetInventorsByMatterID"
adoComm.CommandType = adCmdStoredProc
adoRs.CursorLocation = adUseClient '<--Here
adoRs.Sort = "QINVENTORRANK Asc" '<--Here
Set adoParam = adoComm.CreateParameter("@sMatterID", adVarChar,
adParamInput, 15)
adoComm.Parameters.Append adoParam
adoParam.Value = sDocketNumber
Set adoRs = adoComm.Execute
This causes the cursorlocation and sort to be reset! Try this instead:
adoRs.Open adoComm
As the adoRs object is no longer being reset, it will retain the sort and
cursor location properties from above.
Do While Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
Loop
Set adoParam = Nothing
Set adoComm = Nothing
Set adoRs = Nothing
End Function
The change above should hopefully fix your problem.
Dan
.
- Follow-Ups:
- Re: Recordset & Sort
- From: Zamdrist
- Re: Recordset & Sort
- References:
- Recordset & Sort
- From: zamdrist
- Recordset & Sort
- Prev by Date: Re: Specifying Port Number for Oracle Data Source?
- Next by Date: Re: Recordset & Sort
- Previous by thread: Recordset & Sort
- Next by thread: Re: Recordset & Sort
- Index(es):
Relevant Pages
|
