how to insert then select in one visit to the database without using a stored procedure

Question

I want to be able to insert a row into a table and then select the newly created identity and return that to classic asp (JScript) without having to create a stored procedure. Can it be done?

Basically something like this:

...
rs = Server.CreateObject("ADODB.RecordSet");
rs.Open("INSERT INTO ...;SELECT SCOPE_IDENTITY() x;", objConn, ...
Response.Write(rs("x").Value);

The following works, but amounts to two trips to the database:

...
objConn.Execute("INSERT INTO ...
rs = Server.CreateObject("ADODB.RecordSet");
rs.Open("SELECT SCOPE_IDENTITY() x;", objConn, ...
Response.Write(rs("x").Value);

That should work. If the record set is void, there may be other configuration issues at work here. — Chris, Jun 07 '13 at 15:45
If I don't include the insert in the recordset but use objConn.execute instead, it works fine, but that's two trips to the database, so no other configuration issues in play — Graham, Jun 07 '13 at 15:52
My old school ADO memory is fuzzy. Don't you need to `MoveFirst` or something to make sure you're on a record? — JC Ford, Jun 07 '13 at 15:55
Movefirst is not normally needed. In my case, the recordset is void, so no movefirst available — Graham, Jun 07 '13 at 15:57
Maybe this will help. It's safer from a sql injection standpoint anyway: http://stackoverflow.com/questions/5506456/how-to-get-the-insert-id-from-this-adodb-recordset — JC Ford, Jun 07 '13 at 15:57

score 1 · Answer 1 · edited May 23 '17 at 12:28

You could try a direct call to the stored procedure and store the results in an array like so:

dim rs, arr, sql
sql = "EXEC myProc"
set rs = Server.CreateObject("ADODB.Recordset") 
rs_temp.open sql, application("CONN_DISCHARGE"),3,3,1
if not (rs.bof and rs.eof) then runsql=rs.getrows
rs.close
set rs = nothing

Within your stored procedure you can do whatever you want...

CREATE PROCEDURE myProc
AS
BEGIN
    UPDATE 
        myTable
    SET 
        myField = 42

    SELECT 
        myField 
    FROM 
        myTable 
    WHERE 
        someCondition = 1
END

-- EDIT --

For a more complete version of the ASP function see here. Note that you'll have to include this in a separate file with a VB Script specifier at the top (not JScript) for it to work properly.

score 1 · Answer 2 · answered Jun 10 '13 at 10:29

I think you are receiving the number of rows affected by the insert first, then the identity value. You can either use SET NOCOUNT ON or use rs.MoveNext before accessing the recordset. e.g.

   rs = Server.CreateObject("ADODB.RecordSet");
    rs.Open("SET NOCOUNT ON;INSERT INTO ...;SELECT SCOPE_IDENTITY() x;", objConn, ...
    Response.Write(rs("x").Value);

or

rs = Server.CreateObject("ADODB.RecordSet");
rs.Open("INSERT INTO ...;SELECT SCOPE_IDENTITY() x;", objConn, ...
rs.MoveNext()
Response.Write(rs("x").Value);

how to insert then select in one visit to the database without using a stored procedure

2 Answers2