difficulty with SELECT SCOPE_IDENTITY();

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

Re: difficulty with SELECT SCOPE_IDENTITY();

Joe Befumo wrote:
> 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 )

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"