with command object in VBScript return the identity of inserted row

the first column of tblBlah is the identity, BlahID.  I'm getting "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." I need to know how to do this using a command object in a VBScript if it's possible. Also, it needs to be bulletproof so none of this selecting the max(identity) after the fact. And .executeScalar() doesn't seem to work either.

      sql =       "insert into tblBlah ([This],[That],[TheOther]) "
      sql = sql & "OUTPUT INSERTED.BlahID into @BlahID "
      sql = sql & "select This, 'somevalue', '1234' from tblBlah where BlahID = '1223'"
            
      set cmd = CreateObject("adodb.command")
      with cmd
            .ActiveConnection = conn
            .CommandType = 1
            .CommandTimeout = 60 * 30
            .CommandText = sql
            .Parameters.Append(.CreateParameter("@BlahID", db_int, output_param))
      end with
      set rs = cmd.execute()
      new_order_id = rs("OrderID")
      set rs = nothing

P.S. Hitting MS SQL server 2008.
RossaminoAsked:
Who is Participating?
 
RossaminoAuthor Commented:
After having tried all of the suggestions and all of the linked suggestions the only thing I found to work was a stored procedure.

Stored Procedure InsertIntoBlah:
@RecID int

insert into tblBlah ([This],[That],[TheOther])
select This, 'somevalue', '1234' from tblBlah where RecID = @RecID

select cast(scope_identity() as int) as NewRecID

then in the script:

set hdrrs = conn.execute("exec InsertIntoBlah @RecID = " & rows(0,0))
new_rec_id = hdrrs("NewRecID")
hdrrs.close
set hdrrs = nothing
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
  sql =       "set nocount on insert into tblBlah ([This],[That],[TheOther]) "
      sql = sql & "OUTPUT INSERTED.BlahID into @BlahID "
      sql = sql & "select This, 'somevalue', '1234' from tblBlah where BlahID = '1223'" 

Open in new window

 
next point, to retrieve the parameter...
and the cmd.Execute, you shall be able to retriate it's value:

cmd.Parameteres(@BlahID").value
0
 
RossaminoAuthor Commented:
Updated:
"set nocount on . . . "
and
"new_order_id = cmd.Parameters("@OrderID").value"

Getting same error:
Script: insertRecordGetIdentity.vbs
Line: 347
Char: 4
Error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Code: 800A0BB9
Source: ADODB.Command
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
Somehow the creation of the variable @BlahID of type table seems to have got lost.
0
 
RobSampsonCommented:
Hi, have a look at the solution here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_23479531.html#a21775795

I can't remember that far back, but it looks like SET NOCOUNT ON in the stored procedure itself, then create your parameter first, and set the parameter value second.

Rob.
0
 
RobSampsonCommented:
Ok great. You should mark your own comment as the answer for others to see.

Rob.
0
 
RossaminoAuthor Commented:
This worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.