A mystery it seems nobody can answer...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A mystery it seems nobody can answer...

Author

Topic

Script Error
Starting Member

2 Posts

Posted - 2004-08-28 : 17:36:29

Hello,

This is a mystery that no other developer on any other board seems to be able to answer. Why is it that I cannot retrieve a just-inserted ID -- using SCOPE_IDENTITY() -- within another open recordset's "If rs.EOF then..." statement? I really need to be able to retrieve the new ID like this but nothing I try is working. ID outputs nothing. Here's some sample code...


call openDB()

     mySQL = "SELECT ID " _
           & "FROM   table " _
           & "WHERE  ID   = " & validSQL(ID,"A") & " " _
           & "AND    Key  = " & validSQL(Key,"A") & ""
     set rstemp = openRSexecute(mySQL)

     if rstemp.EOF then
           mySQL = "INSERT INTO table " _
                 & "([DateTime], Accessed) " _
                 & "VALUES " _
                 & "(getDate(), 1); "
                 & "SELECT SCOPE_IDENTITY() AS ID FROM table"
           set rstemp2 = openRSexecute(mySQL)
           ID = rstemp2("ID")<-- this outputs nothing on the next line
           response.write "New ID: '" & ID & "'"
           call closeRS(rstemp2)
     else
           ID = rstemp("ID")<-- this output is correct because it's from rstemp
           response.write "Pre-existing ID: '" & ID & "'"
     end if

     call closeRS(rstemp)

call closeDB()

Again, using this code, if EOF is true then SCOPE_IDENTITY() outputs nothing. By the way, retrieving an ID using SCOPE_IDENTITY() works just fine when I pull it out of the EOF statement...but I need it where it is.

Anyone know why it doesn't work? How can I get the new ID from rstemp2 within rstemp.EOF=true?

Thank you much your your help!

I greatly appreciate it!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 17:54:16

quote:

mySQL = "INSERT INTO table " _
                 & "([DateTime], Accessed) " _
                 & "VALUES " _
                 & "(getDate(), 1); "
                 & "SELECT SCOPE_IDENTITY() AS ID FROM table"

Try this:

mySQL = "SET NOCOUNT ON;INSERT INTO table " _
                 & "([DateTime], Accessed) " _
                 & "VALUES " _
                 & "(getDate(), 1); "
                 & "SELECT SCOPE_IDENTITY() AS ID"

The SET NOCOUNT ON clause will suppress a message "(1 row(s) affected)" that might screw up the client after the INSERT.
Also drop the "From table" when you do: "SELECT SCOPE_IDENTITY() AS ID"
Otherwise you will get as many rows in return as there are rows in the table.
SCOPE_IDENTITY() is a "global" function and you shouldn't do:
"select SCOPE_IDENTITY() from xxx",
just "select SCOPE_IDENTITY()"

Not an ASP programmer...
/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 17:56:25

By the way, double check that there is an IDENTITY column defined for your table. ( Edit: sorry, guess you already did THAT )
And the [Datetime] is a reserved word, not theeee best choice for a column name !!!

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

Script Error
Starting Member

2 Posts

Posted - 2004-08-28 : 18:10:58

Worked like a charm!!! Thank you sooooooo much!!!


mySQL = "SET NOCOUNT ON; " _
      & "INSERT INTO table " _
      & "([DateTime], Accessed) " _
      & "VALUES " _
      & "(getDate(), 1) " _
      & "SELECT SCOPE_IDENTITY() AS ID"

Right on! I still have some testing to do but this gets me past quite a hurdle! This quandry has had me brain-dead for a few days and now I'm FREE!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 18:36:04

You are welcome, glad to be of help.
The SET NOCOUNT ON thingy is a bit tricky if one doesn't know about it

,
basically the client recieves multiple resultsets:

(1 row(s) affected)

ID                                       
---------------------------------------- 
12

instead of just


ID                                       
---------------------------------------- 
12

this tricks the data drivers... and the programmers

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

IvanaCode
Starting Member

1 Post

Posted - 2004-09-12 : 19:00:05

I am new to stored proceudres and I have a similiar need as the above conversation.
I understand the code for the stored procedure and the scope_identity(). I am able to get the correct result in SQL Query Analyzer, but what would the vbscript/asp code look like to access the returned value and pull the value for the scope_identity() into a variable to use in my application?

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources