I've tried a number of things with @@Identity and it still doesn't work, I 

tried a simplified sql statement to pass along:



sqlString = "INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost, total_cost) VALUES (" &  UserID & ", GETDATE(), 0," & 

shippingCost & ", " & orderTotalFinal & " ) Select @@Identity as OrderID"



Set objRec1=server.CreateObject("ADODB.Recordset")

Set objRec1=objConn.Execute(sqlString)



While Not objRec1.EOF

OrderID=objRec1("OrderID")

Response.write "<p> the new order ID is: <b>" & CurrentOrderID & " </b>"

objRec1.MoveNext

Wend

'Closet the recordset object

objRec1.Close

set objRec1=nothing



This is the sql string that is passed to sql server which I've tested in 

SQL query analyzer and it works perfectly there...



INSERT INTO orders ( user_id, order_entrydate, order_status, shipping_cost, 

total_cost) VALUES (" &  UserID & ", GETDATE(), 0," & shippingCost & ", " & 

orderTotalFinal & " ) Select @@Identity as OrderID



But I get the message:



ADODB.Fields error '800a0cc1' 



ADO could not find the object in the collection corresponding to the name 

or ordinal reference requested by the application.



I'm wondering if it's possible for a recordset to do an insertion and 

select within the same statement...if not, how can I get the most recent 

"order ID" in a simple manner (I had another method that retrieved all the 

recent order IDs for a customer and filters out the most recent one but it 

bombs out now that it's on a live server).



thanks



Ben

If you run your query in the Query analycer you will have to recordset.

One with rows affected and one with the new id.

You have to set NOCOUNT ON.



So if you try this query it will work..



sqlString = "SET NOCOUNT ON;INSERT INTO orders ( user_id, order_entrydate, 

order_status,

shipping_cost, total_cost) VALUES (" & UserID & ", GETDATE(), 0," & 

shippingCost & ", " & orderTotalFinal & " );Select @@Identity as OrderID"



/Fredrik Normén

Again!!! If you use for calling SQL query with this:



Set objRec1=server.CreateObject("ADODB.Recordset")

Set objRec1=objConn.Execute(sqlString)



you can't use TWO or more SQL commands.

You must prepare Stored Procedure for it. And then call it.

If you need help with calling SP in ASP, write to me.



It works in SQL query analyzer 'cause it splits to right commands.



Regards,



Richard Bukovansky



----- Original Message -----

From: Ben Lew <>

To: ASP Databases <asp_databases@p...>

Sent: Friday, July 14, 2000 3:00 PM

Subject: [asp_databases] @@ Identity still can't get it to work





> I've tried a number of things with @@Identity and it still doesn't work, I

> tried a simplified sql statement to pass along:

>

> sqlString = "INSERT INTO orders ( user_id, order_entrydate, order_status,

> shipping_cost, total_cost) VALUES (" &  UserID & ", GETDATE(), 0," &

> shippingCost & ", " & orderTotalFinal & " ) Select @@Identity as OrderID"

>

> Set objRec1=server.CreateObject("ADODB.Recordset")

> Set objRec1=objConn.Execute(sqlString)

>

> While Not objRec1.EOF

> OrderID=objRec1("OrderID")

> Response.write "<p> the new order ID is: <b>" & CurrentOrderID & " </b>"

> objRec1.MoveNext

> Wend

> 'Closet the recordset object

> objRec1.Close

> set objRec1=nothing

>

> This is the sql string that is passed to sql server which I've tested in

> SQL query analyzer and it works perfectly there...

>

> INSERT INTO orders ( user_id, order_entrydate, order_status,

shipping_cost,

> total_cost) VALUES (" &  UserID & ", GETDATE(), 0," & shippingCost & ", "

&

> orderTotalFinal & " ) Select @@Identity as OrderID

>

> But I get the message:

>

> ADODB.Fields error '800a0cc1'

>

> ADO could not find the object in the collection corresponding to the name

> or ordinal reference requested by the application.

>

> I'm wondering if it's possible for a recordset to do an insertion and

> select within the same statement...if not, how can I get the most recent

> "order ID" in a simple manner (I had another method that retrieved all the

> recent order IDs for a customer and filters out the most recent one but it

> bombs out now that it's on a live server).

>

> thanks

>

> Ben

>

>

Please try this, you only need to change ConnectionString and the SQL 

Query.



<%

	Dim rs,con,strSQL

	set rs = Server.CreateObject("ADODB.Recordset")

	set con = Server.CreateObject("ADODB.Connection")



	con.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=test;Data 

Source=test"

	

	strSQL = "SET NOCOUNT ON;" & _

		 "INSERT INTO Customer (FirstName,LastName) 

VALUES('Fredrik2','Normen2');" & _

			 "SELECT @@IDENTITY As newId;SET NOCOUNT OFF"

	

	set rs = con.Execute(strSQL)

	

	Response.Write rs(0)

	

	rs.Close

	con.Close

	set rs = nothing

	set con = nothing



%>



It will work..



/Fredrik Normen