SQL Stored Procedures used in Vbscript (CM11874)

[EDN Admin]

[background=#efefef]Good day all, [/background]
[background=#efefef][/background]
[background=#efefef]I have[/background][background=#efefef]managed to create a script that returns values from a [/background][background=#efefef]SQL Stored Procedure[/background][background=#efefef]. [/background]
[background=#efefef] [/background]
[background=#efefef]One problem I am getting is that I am getting multiple results of the same values, eg: [/background]
[background=#efefef] [/background]
[background=#efefef]"Value1;Value1;Value1;Value2;Value2;Value2;Value3;Value3;Value3;" [/background]
[background=#efefef] [/background]
[background=#efefef]Can anyone please guide me as to get single results for the multiple values? [/background]
[background=#efefef] [/background]
[background=#efefef]Here is my code: [/background]
[background=#efefef] [/background]This script is used to popluate the Tax Info Picklist

Option Explicit
dim objConn, conStr, par1, par2, rs, result, command, fields, value, nameset objConn = CreateObject("ADODB.command")
Const adParamInput = 1Const
adVarChar = 200
conStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TEST..."

set par1 = CreateObject("ADODB.Parameter")
set par2 = CreateObject("ADODB.Parameter")
par1.Direction=adParamInput
par1.name="@Option"
par1.Size=50
par1.Type=adVarChar
par1.Value="3"

par2.Direction=adParamInput
par2.name="@CompanyID"
par2.Size=50
par2.Type=adVarChar
par2.Value="1"

With objConn
.activeconnection = conStr
.commandtype = 4
.commandtext = "sp_AO_Select_GRV_Lups"
.Parameters.Append par1
.Parameters.Append par2
.Execute

End with

This section results with one record from the stored procedureSet rs = objConn.Execute
result = rs.Fields(1).Value & ";" wscript.echo resultRS.Close


This section results with 2 records from the stored procedure and works in Doc NavRESULT="" Set rs = objConn.Execute For each name In rs.fields result = result & rs.Fields(1).Value & ";" nextRS.Close wscript.echo result


This section results with multiple records from the stored procedure
result=""
set rs = CreateObject("ADODB.Recordset")
rs.open objConn
do until rs.eof
For each name In rs.fields
result = result & rs.Fields(1).Value & ";"
next rs.move
next loop
RS.Close
set rs=nothing
wscript.echo result

[background=#efefef]Thank you, [/background]

[background=#efefef]Regards, [/background]
[background=#efefef]Greg [/background]

View the full article