Re: resorting recordset returned from SQL stroed procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Jan 28, 11:35 am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxxxxxxxxxxxxxxxx> wrote:
<sanma...@xxxxxxxxxx> wrote in message

news:b23314f2-7e8b-441d-abda-1d0903b140ee@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





I have a stored procedure on a SQL server 2005 database. Application
accessing it is a VB6 program. The app returns the records fine. What
I would llike to do is after the recordset gets returned is to resort
it to one of the columns returned. Data is currently being sorted from
stored procedure by order date. It being a stored procedure, that is
being used by several different apps, I can't change it on server.
I have defined recordset CursorLocationto be adUseClient
This is the two lines setting recordset (in this case rsNotPicked)

rsNotPicked.CursorLocation = adUseClient
rsNotPicked.Open m_cmdQuery, , adOpenStatic, adLockReadOnly
'EXECUTES the stored procedure

At this point I can step through records with

Do While Not rsNotPicked.EOF
   MsgBox rsNotPicked.Fields("item Description")
   rsNotPicked.MoveNext
Loop
The message box is the field I would like to resort by

when I try to execute
rsNotPicked.Sort = "item description ASC"

I get error Run-Time 3001
Arguments are of the wrong type, are out of range,or are in conflict
with one another.
I can use the sort method with simple select statements, but using the
stored procedure recordset I am struggling with.

ANy help would be most appreciated.

I wonder if the space in the field name confuses things. I would try:

rsNotPicked.Sort = "[item description] ASC"

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab -http://www.rlmueller.net
--- Hide quoted text -

- Show quoted text -

Thank you. That did it and I am sooooooo damn grateful.
.

Relevant Pages

  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Not Getting results from Stored procedure
    ... So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. ... In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. ... These messages appear to be returned to the calling application as a recordset. ... when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method. ...
    (microsoft.public.vb.database)
  • Re: vbscript logon script getting return data from sql sp
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (microsoft.public.scripting.vbscript)
  • Re: Missing Resultset When Calling an Stored Procedure w/a Nested
    ... we tried advancing to the next recordset by using the NextRecordSet ... program only returned the nested stored procedure call resultset and failed ...
    (microsoft.public.data.ado)
  • Re: Ado Recordset and stored procedure(SQL server 2000)
    ... I want to fill a recordset using a stored procedure. ... inner join track on images.box = track.box where releasetime between ...
    (microsoft.public.vb.database.ado)