Re: vbscript logon script getting return data from sql sp

SCOPE_IDENTITY
is the same thing as @@IDENTITY
but for the currect session / table

example table with trigger
insert data
trigger fires and inserts data in other table
@@IDENTITY says number for second insert
SCOPE_IDENTITY says data for the first insert


"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
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (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)