This has me stymied, but I'm betting it's something real simple. I'm working
in vb/asp/sql-server, and trying to insert a record into a table, and
retrieve the new index.



Dim objDataConn

Dim strSql

Dim intNewKeyVal



Set objDataConn = Server.CreateObject("ADODB.Connection")
objDataConn.Open("Provider=SQLOLEDB;server=(local);Database=MY_DB;uid=sa;pwd=password")



strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT
SCOPE_IDENTITY();"



My problem is with retrieving the value.



If I do the following:



set intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText
+ adExecuteNoRecords )



It creates the new row, but intNewKeyVal is 'Nothing' after the call. If,
instead, I do this:



intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText +
adExecuteNoRecords )



I get the following error:



Microsoft VBScript runtime error: Object variable not set



Any tips would be greatly appreciated.



Thanks.



Joe

[quoted text, click to view]

By specifying adExecuteNoRecords, you are telling ADO not to construct a
recordset, so the resultset returned by the select scope_identity statement
is lost. You cannot retrieve a return value or an output value without using
a Command object. You need to do this:

intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected,
adCmdText )(0)


strSql = "SET NOCOUNT ON;" & _
"INSERT INTO myTable (fieldname) VALUES (fieldvalue); " & _
"SELECT SCOPE_IDENTITY();"






--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"