"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:
>

"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
>
>

"Jarrod Morrison" <jarrodm@ihug.com.au> wrote:

> Rstest.NextRecordset

"Simon Hayes" <sql@hayes.ch> wrote in message
news:3ffbfea2$1_1@news.bluewin.ch...
>
> "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:
> >
>
> <snip>
>
> I don't have enough VB knowledge to respond to the issue of multiple
> recordsets, but one possible approach would be to return a single result
set
> from your procedure:
>
> EXEC (@SqlStr + ' UNION ALL SELECT LocationID FROM CustLocations WHERE
> MachineName = @MachineName')
>
> You may want to use UNION instead of UNION ALL, if you want to remove
> duplicate values (see Books Online). Note that this approach means you
have
> no way to tell the difference between LocationID values from Locations and
> those from CustLocations. If this is important, then you can add a dummy
> flag column to your query to indicate the source:
>
> /* L for Location */
>
> SET @SqlStr = 'SELECT ''L'' AS ''Source'', LocationID FROM Locations WHERE
> GroupID = '''
>
> /* C for CustLocation */
>
> EXEC (@SqlStr + ' UNION ALL SELECT ''C'', LocationID FROM CustLocations
> WHERE MachineName = @MachineName ORDER BY ''Source'' DESC')
>
>
> Simon
>
>

"Craig Kelly" <cnkelly@worldnet.att.net> wrote in message
news:bLYKb.8097$214.269574@bgtnsc05-news.ops.worldnet.att.net...
> "Jarrod Morrison" <jarrodm@ihug.com.au> wrote:
>
> <snip>
>
> > Rstest.NextRecordset
>
> <snip>
>
> Although I like the use of UNION mentioned already better, to get this to
> work, you would use....
>
> Set Rstest = Rstest.NextRecordset
>
> Craig
>
>