September 17, 2002 at 2:49 am
I've got a bit of a problem;
Having transfered a lot of the logic for my web app from asp to the SQL Server (In a stored procedure), i've come across a problem;
Essentially all it is is a basic search engine, designed to search for whatever the user has input (e.g. a postcode) and the search will become wider and wider until it finds a record.
It does this using dynamic sql (not the best way i know, but it works). Hopefully an example will help......
execute SPifasearch @town='', @postcode='cf316', @searchcolumns='adviser.initials, adviser.surname', @numberrows='4'
Will give me:
initials surname sort
------------------------------ ------------------------------ ----
initials surname sort
------------------------------ ------------------------------ ----
D Keefe 7872
P Branch 7749
Trevor Gyles 7641
Paul Walls 7641
The first recordset is blank (@@rowcount=0) so the postcode is made less accurate (all it does is take one character of the postcode, making it cf31), this is searched on and results are found.
My problem is that when i try to execute this sp from my asp, i just get the blank recordset, and not the second one with results in.
Can anyone help solve my problem; is there anyway to 'bin' the first recordset after finding out there's nothing in it? If not is there anyway to loop through the recordets in asp?
Any help's appreciated.
Regards
CP
September 17, 2002 at 4:06 am
First, are you returnning multiple sets? If so then perform a nextrecordset in your asp page on the recordset object and should be there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 17, 2002 at 4:09 am
The problem is i'm not going to know how many recordsets there are; any ideas how to 'movelast'?
September 17, 2002 at 8:03 am
This solud work:
'if rs is a recordset declared as:
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
'and rs is:
Set rs = cmd.Execute
'where cmd.commandtext="store_procedure ..."
'then:
Do While rs.State = adStateOpen
'main loop for all result sets
Do While Not rs.EOF
'...
rs.movenext
loop 'a result set
Set rs = rs.NextRecordset
If rs.State = adStateOpen Then
' there is another recordset
Else
' no more result sets in sp
Exit Do
End If
loop 'main
quote:
The problem is i'm not going to know how many recordsets there are; any ideas how to 'movelast'?
September 18, 2002 at 5:01 am
Just change the last bit of the last comment I woudl suggest do this way.
Do Until rs Is Nothing
Do Until rs.EOF
'process this recordset data here.
rs.MoveNext 'Move to next record
Loop
Set rs = rs.NextRecordset 'Move to next recordset
Loop
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply