Hi All

Im unsure of how to use vb to read the results of my stored procedure. Ive
included the stored procedure at the end of this message for reference.
Basically the stored procedure will first of all construct a select string
based on the your computer name and perform this select on a table named
Locations, next it will perform a similar search on a table called
CustLocations. Normally it will return two recordsets in query analyzer that
for example would look like:

LocationId
------------
001

LocationId
-----------
002
005

Now under vb i can access the first result set simpy enough and use a loop
that will go until it reaches EOF to read the recordest, but i cant seem to
figure out how to get vb to see the second result set. Now i may be going
about this the wrong way, and there may be a way to make the stored
procedure show only 1 result set for both searches but im not sure of how to
do that. So any help here is greatly appreciated. Ive also included the vb
code im using for testing, its a little rough.

Thanks In Advance




-----------
VB CODE
------------

Public Sub TestSP()

'On Error Resume Next

Dim PtrlCmd As New ADODB.Command

Dim Rstest As ADODB.Recordset

PtrlCmd.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_Test"
PtrlCmd.CommandType = adCmdStoredProc

PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("MachineName",
adVarChar, adParamInput, 50, "HMSSRV")
PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("UserName", adVarChar,
adParamInput, 50, "ADMINISTRATOR")

Set Rstest = PtrlCmd.Execute

' Break Before This Point

Do Until Rstest.EOF = True
MsgBox Rstest.Fields(0)
Rstest.MoveNext
Loop

Rstest.NextRecordset

Do Until Rstest.EOF = True
MsgBox Rstest.Fields(0)
Rstest.MoveNext
Loop

Set PtrlCmd.ActiveConnection = Nothing

End Sub

----------
SQL Stored Procedure
------------

CREATE PROCEDURE [dbo].[sp_Test]

@MachineName VarChar(50),
@UserName VarChar(50)

AS

DECLARE @MachineLength Char(2) /* Local Machine Name Length */
DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */
DECLARE @SqlStr VarChar(300) /* SQL Select String */
DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */

SET @SrchInt = 1

SET @MachineLength = Len(@MachineName)
SET @SqlStr = 'SELECT LocationID FROM Locations WHERE GroupID = '''

WHILE @SrchInt <= @MachineLength

BEGIN

SET @CurrMach =LEFT(@MachineName,@SrchInt)
IF @SrchInt = 1

BEGIN
SET @SqlStr = @SqlStr + LEFT(@MachineName,1) + ''''
END

IF @SrchInt > 1

BEGIN
SET @SqlStr = @SqlStr + ' OR GroupID = ' + '''' + @CurrMach + ''''
END

SET @SrchInt = @SrchInt + 1

END

EXEC (@SqlStr)

SELECT LocationID FROM CustLocations WHERE MachineName LIKE @MachineName

GO