Very Computer

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

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

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

--

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