calling a sql variable in asp

how do you do it?

[mattglet]

"DECLARE @idOrder int; " & _
"SELECT @idOrder = @@identity; " & _
"UPDATE [OrderItem] SET idOrder = @idOrder"
set rs = conn.execute(SQL)

how do i call the @idOrder in an asp recordset?

rs("@idOrder") and rs(@idOrder) do not work.

[txbakers]

I think you might have to rename your fields without the "@" symbol.

You also don't have a FROM clause in your SQL statement.

I've seen the AS/400 database use the "@" symbol in the field name, but when I access that DB through ASP I need to drop the "@" and use the remaining chars as the ASP field name.

So, something like @ID in the AS/400 would be referenced as ID in ASP.

Give it a try.

Also, I'm not familiar with the DECLARE statement in this context. If you want to alias the field you can do is:

SELECT IDENTITY as IDORDER
FROM?

[mattglet]

i'm not using an as/400 database... using sql.

also, you don't need a from clause when calling @@identity.

my statements work fine; they all execute perfectly. the only problem i'm having is calling the @idOrder variable in asp. i was told something about using rs(0) with NOCOUNT, but i'm not entirely sure what that does.

[mattglet]

and i just realized that i need to paste more code to make sure you understand the whole situation:

SQL = "SET NOCOUNT ON; INSERT INTO [Order] (<columns here>)" & _
"VALUES (<values here>);" & _
"DECLARE @idOrder int; " & _
"SELECT @idOrder = @@identity; " & _
"UPDATE [OrderItem] SET idOrder = @idOrder WHERE idShoppingCart = " & idShoppingCart
set rs = conn.execute(SQL)
set rs = rs.NextRecordset

ordernumber = rs(0)

there, this is the whole statement. but, when i try this, i get a "type mismatch" error the rs(0) line. any thoughts? thank you in advance.

[aspdaddy]

Maybe the result in rs(0) an eof marker, as the last command is an update which returns no recordset.

[mattglet]

so the question is, why is it not returning a record? i assure you that @@identity works.

[txbakers]

if it's EOF then it won't return a record.

I didn't realize that msSql syntax was so different from ANSI SQL syntax.

Maybe you can put an IF statement in there to check for EOF before making the assignment.

[mattglet]

well, that's the thing...

it is NOT eof :)

there's no possible way it can be eof. @idOrder is being assigned @@identity, and @@identity can never be eof. see what i mean?

[aspdaddy]

What i meant was the recordset is empty because the last query executed is an update. :)

Maybe with ;select max(@idOrder); on the end it will work, I'm not sure, I generally execute one query at a time in asp to avoid stuff like this.

[mattglet]

i agree about 1 execution at a time, but this is a special case. i need these statements to execute all in one shot, to make sure the numbers go where they are supposed to, and that the tables get updated correctly. it's a shopping cart system, so i can't be having incorrect order numbers being assigned to incorrect orders (obviously).

but, i do like your idea about the max(@idOrder), that might just work. i will try it out, and let you know. thank you very much.

[mattglet]

well, i went ahead and decided to take a gamble by making 2 separate sql statements.

i could not it to work, so after the main glob of statement execution, i added a "SELECT MAX(idOrder) as idNew FROM [Order]" and it works now.

i now dread the day where 2 people almost simultaneously place an order and the numbers get messed up. the odds are definitely with me, but it only takes once.