SEO

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

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

Hi Simon

Thanks heaps for the reply, it works perfectly, i had to change the syntax
slightly because the procedure was using @MachineName as the data to search
for instead of the contents of the variable, so it looked like this

EXEC (@SqlStr + ' UNION ALL SELECT LocationID FROM CustLocations WHERE
MachineName = ' + '''' + @MachineName + '''')

Thanks again


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

"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

Hi Craig

Thanks for the reply, its handy to know how to use the .nextrecordset
function anyhow. I tried to use it before but i had the syntax wrong.

Thanks again

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