with command object in VBScript return the identity of inserted row
Posted on 2014-04-04
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")
.ActiveConnection = conn
.CommandType = 1
.CommandTimeout = 60 * 30
.CommandText = sql
.Parameters.Append(.CreateParameter("@BlahID", db_int, output_param))
set rs = cmd.execute()
new_order_id = rs("OrderID")
set rs = nothing
P.S. Hitting MS SQL server 2008.