Re: vbscript logon script getting return data from sql sp

Thanks for the try very fast and very close to what i have been trying
I did not give enough information here is some more background (do i have
the sp all wrong?)
i use cscript and wscript to run filename.vbs via the logon process

in the beinging there was this script with the procedure that is toward the
bottom of this post with the words original sp

'-------------------------------------------------------------------------------------------------------------------
' create object for connecting
'---------------------------------------------------------------------------------------------------------------------
set CmdSP = CreateObject("ADODB.Command")
set Rs1 = CreateObject("ADODB.Recordset")
'-------------------------------------------------------------------------------------------------------------------
' connection string
'---------------------------------------------------------------------------------------------------------------------
CmdSP.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial
Catalog=databasename;Integrated Security=SSPI;Persist Security Info=False"
'-------------------------------------------------------------------------------------------------------------------
' sql command to be executed please note this is an sp called flashin on
the other end
'---------------------------------------------------------------------------------------------------------------------
CmdSP.CommandText = "insertserverparent test"
set Rs1 = CmdSP.Execute()
'-------------------------------------------------------------------------------------------------------------------
' execute command
'---------------------------------------------------------------------------------------------------------------------
wscript.echo rs1.Fields(0).value
Set Rs1 = Nothing
Set CmdSP = Nothing
set r1 = nothing
RS1.Close


' this gets the error ADODB.Recordset: Item cannot be found in the
collection corresponding to the requested name or ordinal.
' then changed the wscript.echo rs1.Fields(0).value to wscript.echo
rs1.Fields("tim").value then to wscript.echo rs1.Fields("@return_value").value
'then i did the old is there a record set check with
' Do Until rs1.eof
' wscript.echo rs1.Fields(0).value
' adoRecordset.MoveNext
' loop
'which in return gives the error ADODB.Recordset: Operation is not
allowed when the object is closed. no data in record set ????
'This made me mad because the data is being put into the database i have
tons of records

i then created hole new vbscript
'-------------------------------------------------------------------------------------------------------------------
' create object for connecting
'---------------------------------------------------------------------------------------------------------------------
set Conn1 = CreateObject("ADODB.Connection")
set Cmd1 = CreateObject("ADODB.Command")
set Rs1 = CreateObject("ADODB.Recordset")
'-------------------------------------------------------------------------------------------------------------------
' connection string
'---------------------------------------------------------------------------------------------------------------------
sConnect="Provider=SQLOLEDB;Data Source=servername;Initial
Catalog=databasename;Integrated Security=SSPI;Persist Security Info=False"
Conn1.ConnectionString = sConnect
Conn1.Open

'-------------------------------------------------------------------------------------------------------------------
' sql command to be executed
'---------------------------------------------------------------------------------------------------------------------
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "insertserverparent"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = 22
Cmd1.Parameters(2).Value = 1
set Rs1 = Cmd1.Execute()
'-------------------------------------------------------------------------------------------------------------------
' execute command
'---------------------------------------------------------------------------------------------------------------------

Do Until Rs1.EOF
' MsgBox (rs1)
wscript.echo rs1.Fields.Item("tim")
Loop
Set Rs1 = Nothing
Set CmdSP = Nothing
set r1 = nothing
RS1.Close

' This created error ADODB.Command: Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.
' on the following code Cmd1.CommandType = adCmdStoredProc
' i then changed the stored procedure to several different randisions
without return code with return code with and without the OUTPUT switches
' i changed so many things about the sp i almost lost my mind. i even
brought it done to something as simple as

CREATE PROCEDURE dbo.insertserverparent
@OutTest int OUTPUT
AS
SET @OutTest = 9
RETURN 1
then changed the vbscript code in accordance and got the same error
' ADODB.Command: Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.
' on the following code Cmd1.CommandType = adCmdStoredProc
' i started to think adCmdStoredProc can not be called outside of ASP.
'At this point my hair started to come out and i figured it was just the
..parameters were not right and complete in the command corectly so i created
this, and several variations.
'-------------------------------------------------------------------------------------------------------------------
' sql command to be executed
'---------------------------------------------------------------------------------------------------------------------
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "insertserverparent"
Cmd1.CommandType = adCmdStoredProc
set p=cmd1.parameters
p.Append Cmd1.CreateParameter("@param1namehostp", advarchar, adParamInput,
250)
p.Append Cmd1.CreateParameter("@param2misc", advarchar, adParamInput, 250)
Cmd1("@param1namehostp") = "test"
Cmd1("@param2misc") = "test"
' but the error would not go away
' ADODB.Command: Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.
' on the following code Cmd1.CommandType = adCmdStoredProc


i have even tried the code you gave me with the following sp but i get the
error
ADODB.Command: Item cannot be found in the collection corresponding to the
requested name or ordinal.


CREATE PROCEDURE insertserverparent
(@param1namehostp varchar(250),
@param2misc varchar(250) = null,
@myreturn int =null OUTPUT)
AS
INSERT INTO [logon].[dbo].[serverparent]( [namehostp], [misc])
VALUES( @param1namehostp, @param2misc)
set @myreturn = SCOPE_IDENTITY()

'-----------------------vbscript
'-------------------------------------------------------------------------------------------------------------------
' create object for connecting
'---------------------------------------------------------------------------------------------------------------------
set Conn1 = CreateObject("ADODB.Connection")
set Cmd1 = CreateObject("ADODB.Command")
set Rs1 = CreateObject("ADODB.Recordset")
'-------------------------------------------------------------------------------------------------------------------
' connection string
'---------------------------------------------------------------------------------------------------------------------
sConnect="Provider=SQLOLEDB;Data Source=servername;Initial
Catalog=databasename;Integrated Security=SSPI;Persist Security Info=False"
Conn1.ConnectionString = sConnect
Conn1.Open

'-------------------------------------------------------------------------------------------------------------------
' sql command to be executed
'---------------------------------------------------------------------------------------------------------------------
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "insertserverparent test"
Cmd1.Execute()
strValue = Cmd1.Parameters("@myreturn").Value
wscript.echo strValue
'-------------------------------------------------------------------------------------------------------------------
' execute command
'---------------------------------------------------------------------------------------------------------------------

Set Rs1 = Nothing
Set CmdSP = Nothing
set r1 = nothing
RS1.Close





--original sp code
--this is the stored procedure i have tried several things and this is
overkill but it does both things.
--both things being @return_value and a standard return for record set with
the select
ALTER PROCEDURE insertserverparent
(@param1namehostp varchar(250),
@param2misc varchar(250) = null,
@myreturn int =null OUTPUT)
-- @myreturn int = OUTPUT)
AS
INSERT INTO [logon].[dbo].[serverparent]( [namehostp], [misc])
VALUES( @param1namehostp, @param2misc)
set @myreturn = SCOPE_IDENTITY()
select @myreturn as tim
--select @myreturn = SCOPE_IDENTITY()
return @myreturn
--print
--return @myreturn
"Richard Mueller [MVP]" wrote:

>
> > '---------------------------
> > set CmdSP = CreateObject("ADODB.Command")
> > CmdSP.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial
> > Catalog=database;Integrated Security=SSPI;Persist Security Info=False"'
> > cmdstr= "allinone "&chr(34)& v1 &
> >
> chr(34)&","&chr(34)&v2&chr(34)&","&chr(34)&v3&chr(34)&","&chr(34)&v4&chr(34)
> &","&chr(34)&v5&chr(34)&","&chr(34)&v6&chr(34)&","&chr(34)&v7&chr(34)&","&ch
> r(34)&v8&chr(34)&","&chr(34)&v9&chr(34)&","&chr(34)&v10&chr(34)&","&chr(34)&
> v11&chr(34)&","&chr(34)&v12&chr(34)&","&chr(34)&v13&chr(34)&","&chr(34)&v14&
> chr(34)&","&chr(34)&v15&chr(34)&","&chr(34)&v16&chr(34)&","&chr(34)&v17&chr(
> 34)&","&chr(34)&v18&chr(34)&","&chr(34)&v19&chr(34)&","&chr(34)&v8&chr(34)&"
> ,"&chr(34)&v9&chr(34)
> > 'Wscript.Echo cmdstr
> > CmdSP.CommandText = "spname "&chr(34)& v1 &
> >
> chr(34)&","&chr(34)&v2&chr(34)&","&chr(34)&v3&chr(34)&","&chr(34)&v4&chr(34)
> &","&chr(34)&v5&chr(34)&","&chr(34)&v6&chr(34)&","&chr(34)&v7&chr(34)&","&ch
> r(34)&v10&chr(34)&","&chr(34)&v11&chr(34)&","&chr(34)&v12&chr(34)&","&chr(34
> )&v13&chr(34)&","&chr(34)&v14&chr(34)&","&chr(34)&v15&chr(34)&","&chr(34)&v1
> 6&chr(34)&","&chr(34)&v17&chr(34)&","&chr(34)&v18&chr(34)&","&chr(34)&v19&ch
> r(34)&","&chr(34)&v8&chr(34)&","&chr(34)&v9&chr(34)
> > ' CmdSP.CommandType = adCmdSPStoredProc
> > CmdSP.Execute()
> > Set CmdSP = Nothing
> > cmdsp.close
>
> Hi,
>
> One way to retrieve values from a stored procedure is with a Recordset
> object. I have used code similar to:
>
> Set adoCommand = CreateObject("ADODB.Command")
> adoCommand.ActiveConnection = "xxx"
> adoCommand.CommandType = adCmdStoredProc
> adoCommand.CommandText = "MyProcName"
> Set adoRecordset = adoCommand.Execute
>
> Do Until adoRecordset.EOF
> strValue = adoRecordset.Fields(0).Value
> adoRecordset.MoveNext
> Loop
> adoRecordset.Close
>
> Specifics depend on the stored procedure. adCmdStoredProc must be defined in
> VBScript.
>
> I have also seen the Parameters method of the Command object used. Again,
> the stored procedure must define the return value, but the value would be
> retrieved with code similar to:
>
> adoCommand.Execute
> strValue = adoCommand.Parameters("@intID").Value
>
> I have to check what SCOPE_IDENTITY is, but if it is the autoincrement
> feature, you can use the @@IDENTITY function to retrieve the ID value
> assigned (assuming the stored procedure added a row to a table). For
> example, I have used:
>
> adoCommand.Execute
>
> Set adoRecordset = CreateObject("ADODB.Recordset")
> adoRecordset.ActiveConnection = "xxx"
> adoRecordset.Source = "SELECT @@IDENTITY"
> adoRecordset.Open
> lngID = CLng(adoRecordset.Fields(0).Value)
>
> @@IDENTITY returns the last autoincrement ID value assigned.
> adoRecordset.Fields(0).Value returns the first (or only) field in the
> recordset. You can retrieve fields by name with
> adoRecordset.Fields("FieldName").Value. If the stored procedure returns a
> recordset with one row, then the Do Until adoRecordset.EOF loop above loops
> once. Otherwise, you will retrieve one value for every row in the recordset.
> The @@IDENTITY function always returns one row, so I did not loop, but just
> retrieve the first (and only) value.
>
> I hope this helps. You can find documentation on @@IDENTITY, the Parameters
> method of the Command object, and the Recordset object at the Microsoft
> site. Although most of my experience with the above is with VB, I've also
> done similar in VBScript.
>
> --
> Richard
> Microsoft MVP Scripting and ADSI
> Hilltop Lab web site - http://www.rlmueller.net
> --
>
>
>
.

Relevant Pages

  • Re: vbscript logon script getting return data from sql sp
    ... > One way to retrieve values from a stored procedure is with a Recordset ... then the Do Until adoRecordset.EOF loop above loops ...
    (microsoft.public.scripting.vbscript)
  • Retrieving Output Parameters and Recordset
    ... I have a T-SQL stored procedure that returns both output parameters ... How do I retrieve them in my ASP script? ... but will I be able to retrieve the recordset using the ...
    (microsoft.public.inetserver.asp.general)
  • Re: Retrieving Output Parameters and Recordset
    ... How do I retrieve them in my ASP script? ... the ADO Command object can be used to retrieve the output ... but will I be able to retrieve the recordset using the ... CommandText properties to point at the stored procedure, ...
    (microsoft.public.inetserver.asp.general)
  • Re: vbscript logon script getting return data from sql sp
    ... One way to retrieve values from a stored procedure is with a Recordset ... adoCommand.CommandType = adCmdStoredProc ...
    (microsoft.public.scripting.vbscript)
  • Re: Operation not allowed when object is closed
    ... The error lies on the line " If RS.EOF = False Then ". ... is inside the loop. ... Maybe the error is occurring because you are opening the recordset ... You are actually executing a stored procedure, ...
    (comp.lang.basic.visual.misc)