Getting Identity Value on Insert

Getting Identity Value on Insert

Post by Jeffrey Winte » Sat, 08 Jan 2000 04:00:00



Is is possible to execute a single s*of SQL (not a stored procedure)
that inserts a row and returns its identity.

For example: from SQL Query Analyzer I can execute:


And it returns:

    (1 row(s) affected)

    Identity
    ----------------------------------------
    126

    (1 row(s) affected)

If I attempt to execute the same SQL using an ADO command object, it appears
to execute correctly, but the resulting recordset is closed, so I can't get
the Identity value out of it:

    adoCommand.commandText = "insert into TableX (test) values ("Blah")

As 'Identity'"
    Set rs = adoCommmand.execute

now any action I take on the resulting recordset (rs) raises the error 3704:
The operation requested by the application is not allowed if the recordset
is closed.

Is there anyway to make this work?

Thanks

 
 
 

Getting Identity Value on Insert

Post by Anthony C. Perkin » Sat, 08 Jan 2000 04:00:00


Jeffrey,

I have done this in the past with a Stored Procedure and I realize you don't
want to use a Stored Procedure (and I am sure you have a very valid reason),
but you may want to check anyway the value of adoCommand(0) after the line:

adoCommmand.execute

There is no point (as you have already found out) to

Set rs = adoCommmand.execute

as no recordset is returned.

Hope this helps,
Anthony


> Is is possible to execute a single s*of SQL (not a stored procedure)
> that inserts a row and returns its identity.

> For example: from SQL Query Analyzer I can execute:


'Identity'"

> And it returns:

>     (1 row(s) affected)

>     Identity
>     ----------------------------------------
>     126

>     (1 row(s) affected)

> If I attempt to execute the same SQL using an ADO command object, it
appears
> to execute correctly, but the resulting recordset is closed, so I can't
get
> the Identity value out of it:

>     adoCommand.commandText = "insert into TableX (test) values ("Blah")

> As 'Identity'"
>     Set rs = adoCommmand.execute

> now any action I take on the resulting recordset (rs) raises the error
3704:
> The operation requested by the application is not allowed if the recordset
> is closed.

> Is there anyway to make this work?

> Thanks


 
 
 

Getting Identity Value on Insert

Post by fred » Mon, 10 Jan 2000 04:00:00


HI

I use this, and it works.

cmdText = "SET NOCOUNT ON;" & _
" INSERT INTO SalesCompanies (CompanyName, ISOCode, TransportCost, EMail,
PhoneNbr, FaxNbr)" & _
" VALUES(?, ?, ?, ?, ?, ?);" & _

" SET NOCOUNT OFF;"

dim cmd as new ADODB.Command
cmd.commandtext = cmdText
...
Dim objRst As ADODB.Recordset

Set objRst = cmd.Execute(, Array(m_CompanyName, m_ISOCode, m_TransPortCost,
m_EMail, m_PhoneNbr, m_FaxNbr))

m_SalesCompanyID = objRst.Fields(0).Value

bye


 
 
 

Getting Identity Value on Insert

Post by Carl Prothma » Mon, 10 Jan 2000 04:00:00


Jeffrey,
Take a look at the following ADO FAQ:
http://www.veryComputer.com/#Q9

--

Thanks,
Carl Prothman, MVP, CCP
ADO FAQ: http://www.veryComputer.com/
RDS FAQ: http://www.veryComputer.com/


> Is is possible to execute a single s*of SQL (not a stored procedure)
> that inserts a row and returns its identity.

> For example: from SQL Query Analyzer I can execute:


'Identity'"

> And it returns:

>     (1 row(s) affected)

>     Identity
>     ----------------------------------------
>     126

>     (1 row(s) affected)

> If I attempt to execute the same SQL using an ADO command object, it
appears
> to execute correctly, but the resulting recordset is closed, so I can't
get
> the Identity value out of it:

>     adoCommand.commandText = "insert into TableX (test) values ("Blah")

> As 'Identity'"
>     Set rs = adoCommmand.execute

> now any action I take on the resulting recordset (rs) raises the error
3704:
> The operation requested by the application is not allowed if the recordset
> is closed.

> Is there anyway to make this work?

> Thanks