NextRecordset() returns empty recordset 2

[Kingkumar]

Hi ,
what i need to do is have two select statemetns in a stored procedure and display the results in two different listboxes.
the problem i am facing is that its getting all hte records as required but wehn i try the second list box is always empty ( i have the sql in query analyzer )
Is there a way to check whether how many recordsets are returend or how many records are there in each recordset

Need help ...... going nuts


My Stored procedure is as follows

CREATE PROCEDURE [dbo].[spUSER_GetAgents]
@groupid int
AS
SELECT distinct con.AgentNo, c.LastName + ', ' + c.FirstName AS AgentName FROM MPUserGroup con inner join contact c on con.AgentNo = c.ClientNo
WHERE MPGroupID <> @groupid AND con.Agentno NOT IN
(SELECT AgentNo FROM MPUserGroup WHERE MPGroupID= @groupid)


SELECT distinct con.AgentNo, c.LastName + ', ' + c.FirstName AS AgentName FROM MPUserGroup con inner join contact c on con.AgentNo = c.ClientNo WHERE MPGroupID = @groupid
GO

and my asp code is as follows
<td width=285 align=center>
<%
strProc = "spUSER_GetAgents '" & strMarket & "'"
set rs = objWebData.getRS(cstr(conn),strProc,1)
Response.Write "<select name=selAgtAddList id=selAgtList MULTIPLE SIZE=7>"
While not rs.EOF
Response.Write "<option "

Response.Write "value=" & rs.Fields("AgentNo")

Response.Write ">" & rs.Fields("AgentName") & "</option>"
rs.MoveNext
Wend
Response.Write "</select>"
%>

</td>
<td width = 285 align=center>

<%
set rs = rs.NextRecordset
Response.Write "<select name=selAgtRmvList id=selAgtRmvList MULTIPLE SIZE=7>"
While not rs.EOF
Response.Write "<option "

Response.Write "value=" & rs.Fields("AgentNo")

Response.Write ">" & rs.Fields("AgentName") & "</option>"
rs.MoveNext
Wend
Response.Write "</select>"
%>
</td>

 

[Sheco]

First thing that I would try is to run the stored procedure with the Query Analyzer to make sure you actually get 2 return sets.

Also you might try setting nocount on in the stored procedure and making sure your web server is running a fairly recent version of ADO although this probably isn't it unless you have it running on NT4.

 

[Kingkumar]

in my com object i have the
objConn.CursorLocation = adUseClient

do you guys think that might be the problem as it will create a disconnected recordset???
Thanks

 

[Kingkumar]

hi Sheco,

The stored procedure is running properly and is returning two recordsets . I checked it in QA

How do i check the version of ado my webserver is runing and which version (atleast) it should be runing

Thanks

 

[Sheco]

well add the SET NOCOUNT ON to you stored procedure between the AS and the SELECT. This probably isn't it but, if I recall, some of the older versions of the sql server driver were confused by the extra data returned showing record count numbers.

What I'm trying to say is that you could have a stored procedure that returns 2 resultsets but end up with 4 recordsets in your ADO like this:
1st recordset = 1st resultset
2nd recordset = # of records in 1st resultset or error msg
3rd recordset = 2nd resultset
4th recordset = # of records in 2nd resultset or error msg.

I think the newer drivers don't do this but it can't hurt to try.

 

[Kingkumar]

I did that but still the same result.
Is there a way in asp to check how many recordsets are being returned
or to check whether there are any records when the recordset is moved to next recordset whether the next recordset has nay records in it or not?
Thanks.

 

[Sheco]

Yeah but you need to go about getting your recordset a different way.

You are calling the .GetRS() method of an object named objWebData...

Instead try createing the recordset using Server.CreateObject... then you can configure the recordset properties and set the cursor, lock, etc before you call the .Open() method to populate the recordset... That way you can check the .RecordCount property of the recordset.

But before you do all that, make sure you don't get a recordset with this config back from your objWebData. It should be easy to tell because you'll get -1 back from .RecordCount if it is just returning the default recordset config.

 

[Kingkumar]

hi Sheco,
i have to use com object and cant have the recordset create in the application (company policy)
haven been said that i was wondering is there another way around.
To check atleast if the two recordset are coming are not i tried using .recordcount but it returns the number of records in the first recordset and not the second one which its suppose to ???
i do set rs = rs.nextrecordset
response.write(rs.recordcount)

thanks.

 

[Sheco]

Well it is possible that the problem lies within this object. Set rs = rs.NextRecordset should work.

 

[Kingkumar]

what are the minimum default setting for making nextrecordset to work .
i mean i have the following to open the recordset in the object

objRS.Open vCommand, objConn, vCursorType, adLockReadOnly

objRs is the recordset

vcommand is the command typye which in this case is storedprocedure
objconn -- connection string
vcursortype -- 1 ( openkeyset i guess)
adlockreadonly -- read only lock.

is that good enough or i need to make some changes

Thanks

 

[Sheco]

Wait a minute, did you try just using rs.NextRecordset ? without the Set?

 

[Kingkumar]

i used like this
set rs = rs.nextrecordset
was'nt it supposed to be ussed like that ???

 

[Kingkumar]

ohh u mena to say wihtout set rs =rs.nextrecordset just
rs.nextrecordset
lemme try

 

[Kingkumar]

ohh u mean without the set

like just plain
rs.nextrecordset
ill try that

 

[Sheco]

Yeah nevermind on that it probably wont work.

Look I made a sample for you... Here is the ASP page:

<%@ Language=VBScript %>
<%
  Dim  cn, rs
  Set cn = Server.CreateObject("ADODB.Connection")
  cn.ConnectionString = "PROVIDER=SQLOLEDB;" _
                      & "NETWORK LIBRARY=DBMSSOCN;" _
                      & "DATA SOURCE=192.168.63.13,1433;" _
                      & "INITIAL CATALOG=pubs;" _
                      & "USER ID=TestUser;" _
                      & "PASSWORD=test" 
  cn.Open 
  
  Set rs = Server.CreateObject("ADODB.Recordset")
	Set rs.ActiveConnection = cn
	rs.LockType = ADODB.adLockOptimistic 
	rs.CursorLocation =  ADODB.adUseClient
	rs.CursorType =  ADODB.adOpenDynamic
  rs.Open "ZTest"
  
  Do While Not rs.EOF 
    Response.Write "1st recordset = " & rs(0) & "<BR>"
		rs.MoveNext 
  loop
  
  Response.Write "--------------------------------------<BR>" & vbcrlf
  
  dim rs2
  set rs2 = rs.NextRecordset
  
  do while not rs2.EOF 
		Response.Write "2nd recordset = " & rs2(0) & "<BR>"
		rs2.MoveNext 
  loop
  
  rs2.close
  rs.Close 
  cn.Close 
  set rs = Nothing
  set cn = nothing
%>

And here is the stored procedure:

CREATE PROCEDURE [dbo].[ZTest] AS
set nocount on
select * from jobs

select * from sales
GO

And here is the output:

1st recordset = 1
1st recordset = 2
1st recordset = 3
1st recordset = 4
1st recordset = 5
1st recordset = 6
1st recordset = 7
1st recordset = 8
1st recordset = 9
1st recordset = 10
1st recordset = 11
1st recordset = 12
1st recordset = 13
1st recordset = 14
--------------------------------------
2nd recordset = 6380
2nd recordset = 6380
2nd recordset = 7066
2nd recordset = 7066
2nd recordset = 7067
2nd recordset = 7067
2nd recordset = 7067
2nd recordset = 7067
2nd recordset = 7131
2nd recordset = 7131
2nd recordset = 7131
2nd recordset = 7131
2nd recordset = 7131
2nd recordset = 7131
2nd recordset = 7896
2nd recordset = 7896
2nd recordset = 7896
2nd recordset = 8042
2nd recordset = 8042
2nd recordset = 8042
2nd recordset = 8042

This just runs against the pubs database that is installed by default on SQL Server as an example. I made a new user named TestUser with exec priv on the ZTest stored procedure.

 

[Kingkumar]

Thanks Sheco,
You been great help.
You deserve a star. Right now our server is down ill try once its up
Thanks

 

[Sheco]

w00t! thanks for the star!

I did a little tweak to the ASP page so you could see more than just the first field of each record. This version puts each recordset into its own HTML table.

<%@ Language=VBScript %>
<%
  Dim  cn, rs
  Set cn = Server.CreateObject("ADODB.Connection")
  cn.ConnectionString = "PROVIDER=SQLOLEDB;" _
                      & "NETWORK LIBRARY=DBMSSOCN;" _
                      & "DATA SOURCE=192.168.63.13,1433;" _
                      & "INITIAL CATALOG=pubs;" _
                      & "USER ID=TestUser;" _
                      & "PASSWORD=test" 
  cn.Open 
  
  Set rs = Server.CreateObject("ADODB.Recordset")
	Set rs.ActiveConnection = cn
	rs.LockType = ADODB.adLockOptimistic 
	rs.CursorLocation =  ADODB.adUseClient
	rs.CursorType =  ADODB.adOpenDynamic
  rs.Open "ZTest"
  
  Response.Write "<table border='1'>" & vbCrLf
  Dim fld
  Do While Not rs.EOF 
    Response.Write "<tr>"
    for each fld in rs.Fields 
      Response.Write "<td>" & fld.Name & " = " & fld.Value & " &nbsp;&nbsp;&nbsp;&nbsp;</td>"
		next
		Response.Write "</tr>" & vbcrlf
		rs.MoveNext 
  loop
  Response.Write "</table><BR><BR><BR>" & vbCrLf
  
  dim rs2
  set rs2 = rs.NextRecordset
 
  Response.Write "<table border='1'>" & vbCrLf 
  do while not rs2.EOF 
		Response.Write "<tr>"
    for each fld in rs2.Fields 
      Response.Write "<td>" & fld.Name & " = " & fld.Value & " &nbsp;&nbsp;&nbsp;&nbsp;</td>"
		next
		Response.Write "</tr>" & vbCrLf
		rs2.MoveNext 
  loop
  Response.Write "</table>" 
  
  rs2.close
  rs.Close 
  cn.Close 
  set rs = Nothing
  set cn = nothing
%>