SCOPE_IDENTITY() not working 1

[tyutghf]

I am using SCOPE_IDENTITY() to grab the recently inserted id value of one row to populate another, but it is not working, here is my code

SQLn = "insert into t_table1 (item1, item2, item3) values ('" & rs("item1") & "', '" & rs("item2") & "', '" & rs("item3") & "')"
objConn.execute SQLn
Set rsIdentity = objConn.Execute("SELECT SCOPE_IDENTITY() FROM t_table1")

response.write rsIdentity(0)

rsIdentity(0) is coming out empty. The t_table1 has an auto identity yes and auto increment. Am I doing something wrong?

Thanks

 

[gmmastros]

You do not select scope_identity() from a table. From the From t_table1 part and you should be fine.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

From the From t_table1 part and you should be fine.

Uh... yeah. I meant to say,

Remove the From t_table1 part and you should be fine.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tyutghf]

It still isn`t picking the value, I now have

SQLn = "insert into t_table1 (item1, item2, item3) values ('" & rs("item1") & "', '" & rs("item2") & "', '" & rs("item3") & "')"
objConn.execute SQLn
Set rsIdentity = objConn.Execute("SELECT SCOPE_IDENTITY()")

response.write rsIdentity(0)

 

[gmmastros]

try...

SQLn = "Set NoCount On insert into t_table1 (item1, item2, item3) values ('" & rs("item1") & "', '" & rs("item2") & "', '" & rs("item3") & "') SELECT SCOPE_IDENTITY()"

Set rsIdentity = objConn.Execute(SQLn)

response.write rsIdentity(0)

Also, while you're at it, do a little research on [google]sql injection[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tyutghf]

That worked great thanks, what does the Set NoCount on bit do?

The page is fully sql injectioned up, I quickly rewrote the SQL as the actual SQL on the page is much more convoluted than that

 

[gmmastros]

When you have a query that includes multiple statements, SET NOCOUNT ON prevents a 'phantom' recordset from being returned.

When you run a query in a query window, you can look at the messages tab to see how many rows are affected. If you have a single query/statement, there will be only one value. When you have multiple statements, there are multiple values for 'records affected', so this returns as a recordset AND the data returns as a recordset. The SET NOCOUNT ON statement disables the recordset with the rows affected from being returned.

Just for fun, remove that part. You'll see that your query fails when trying to retrieve the identity value. You'll get a message about the recordset being closed (or something similar). SET NOCOUNT ON prevents that error.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom