I believe there is a next recordset method that will fetch you the next rs,
if you choose to keep your SQL the same.

Check it out in your object browser F2 and search for NextRecordset

"Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
news:btgib8$9gq$1@lust.ihug.co.nz...
> 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
>
>