p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: using @@Identity - problems with it


Message #1 by "Ben Lew" <formula1@b...> on Fri, 14 Jul 2000 6:59:54
I can't seem to get @@Identity to

work...my asp page generates the following SQL statement:



INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) SELECT @@IDENTITY as

'orderID', INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,1, 'Abel Super Series 

12',

$735.00, 1), INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,9, 'Ande Tournament

Monofilament', $10.49, 1), INSERT INTO OrderCart (Order_ID, Product_ID,

Product_Name, Product_Price, Product_Quantity) VALUES (OrderID ,10, 

'Extreme

Fluorcarbon Monofilament', $19.99, 1)



But when I run this in the query analyzer I get the message:



Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'INSERT'.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.



I'm not worried about the incorrect syntax near INSERT...it's the 

'OrderID'

not permitted that's causing the problems.  I've tried running the

@@identity in a separate sql statement after I've executed the following:



INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 )



But then all I get are a bunch of null values (I get a number of null 

values

equal to the number of rows I have in the table.



The statement I'm running is "SELECT @@identity FROM orders"



I've also tried the following:



Declare @iden int

 INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) 

SET @IDEN=@@IDENTITY  

INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (@IDEN ,1, 'Abel Super Series 12',

$735.00, 1)

 INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (@IDEN,9, 'Ande Tournament

Monofilament', $10.49, 1)

 INSERT INTO OrderCart (Order_id, Product_ID,

Product_Name, Product_Price, Product_Quantity) VALUES (@IDEN,10, 'Extreme

Fluorcarbon Monofilament', $19.99, 1)



and I get the following error message:



Server: Msg 545, Level 16, State 1, Line 0

Explicit value must be specified for identity column in table 'Orders' when 

IDENTITY_INSERT is set to ON.

Server: Msg 515, Level 16, State 2, Line 5

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Server: Msg 515, Level 16, State 2, Line 8

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Server: Msg 515, Level 16, State 2, Line 11

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.



Can anyone give me an idea?



Thanks



Can you tell me what I'm doing wrong?



Message #2 by David Johnson <david@l...> on Fri, 14 Jul 2000 19:57:38 +1000
Ben



Not too sure if this is relevant but I normally use the following expression

when assigning a holding variable (in this case @Ident as the variable name)

the @@IDENTITY value....



SET @Ident = @@IDENTITY



Would it not be wiser (more secure & a lot faster) to use a stored proc on

your SQL Server rather than SQL syntax on your ASP page.



Regards

David



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

From: Ben Lew 

Sent: Friday, 14 July 2000 5:00 PM

To: ASP Databases

Subject: [asp_databases] using @@Identity - problems with it





I can't seem to get @@Identity to

work...my asp page generates the following SQL statement:



INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) SELECT @@IDENTITY as

'orderID', INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,1, 'Abel Super Series 

12',

$735.00, 1), INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,9, 'Ande Tournament

Monofilament', $10.49, 1), INSERT INTO OrderCart (Order_ID, Product_ID,

Product_Name, Product_Price, Product_Quantity) VALUES (OrderID ,10, 

'Extreme

Fluorcarbon Monofilament', $19.99, 1)



But when I run this in the query analyzer I get the message:



Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'INSERT'.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.



I'm not worried about the incorrect syntax near INSERT...it's the 

'OrderID'

not permitted that's causing the problems.  I've tried running the

@@identity in a separate sql statement after I've executed the following:



INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 )



But then all I get are a bunch of null values (I get a number of null 

values

equal to the number of rows I have in the table.



The statement I'm running is "SELECT @@identity FROM orders"



I've also tried the following:



Declare @iden int

 INSERT INTO orders ( user_id, order_entrydate, order_status, 

shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) 

SET @IDEN=@@IDENTITY  

INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (@IDEN ,1, 'Abel Super Series 12',

$735.00, 1)

 INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (@IDEN,9, 'Ande Tournament

Monofilament', $10.49, 1)

 INSERT INTO OrderCart (Order_id, Product_ID,

Product_Name, Product_Price, Product_Quantity) VALUES (@IDEN,10, 'Extreme

Fluorcarbon Monofilament', $19.99, 1)



and I get the following error message:



Server: Msg 545, Level 16, State 1, Line 0

Explicit value must be specified for identity column in table 'Orders' when 

IDENTITY_INSERT is set to ON.

Server: Msg 515, Level 16, State 2, Line 5

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Server: Msg 515, Level 16, State 2, Line 8

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Server: Msg 515, Level 16, State 2, Line 11

Cannot insert the value NULL into column 'order_id', table 

'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

The statement has been terminated.



Can anyone give me an idea?



Thanks



Can you tell me what I'm doing wrong?



Message #3 by "Richard Bukovansky" <richard.bukovansky@c...> on Fri, 14 Jul 2000 11:26:42 +0200
You can't use INSERT..., INSERT!!! Every command for SQL must have

separated row, not olnly by comma!



Richard Bukovansky

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

From: Ben Lew 

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

Sent: Friday, July 14, 2000 6:59 AM

Subject: [asp_databases] using @@Identity - problems with it





> I can't seem to get @@Identity to

> work...my asp page generates the following SQL statement:

>

> INSERT INTO orders ( user_id, order_entrydate, order_status,

> shipping_cost,

> total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) SELECT @@IDENTITY as

> 'orderID', INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

> Product_Price, Product_Quantity) VALUES (OrderID ,1, 'Abel Super Series

> 12',

> $735.00, 1), INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

> Product_Price, Product_Quantity) VALUES (OrderID ,9, 'Ande Tournament

> Monofilament', $10.49, 1), INSERT INTO OrderCart (Order_ID, Product_ID,

> Product_Name, Product_Price, Product_Quantity) VALUES (OrderID ,10,

> 'Extreme

> Fluorcarbon Monofilament', $19.99, 1)

>

> But when I run this in the query analyzer I get the message:

>

> Server: Msg 156, Level 15, State 1, Line 1

> Incorrect syntax near the keyword 'INSERT'.

> Server: Msg 128, Level 15, State 1, Line 1

> The name 'OrderID' is not permitted in this context. Only constants,

> expressions, or variables allowed here. Column names are not permitted.

> Server: Msg 128, Level 15, State 1, Line 1

> The name 'OrderID' is not permitted in this context. Only constants,

> expressions, or variables allowed here. Column names are not permitted.

> Server: Msg 128, Level 15, State 1, Line 1

> The name 'OrderID' is not permitted in this context. Only constants,

> expressions, or variables allowed here. Column names are not permitted.

>

> I'm not worried about the incorrect syntax near INSERT...it's the

> 'OrderID'

> not permitted that's causing the problems.  I've tried running the

> @@identity in a separate sql statement after I've executed the following:

>

> INSERT INTO orders ( user_id, order_entrydate, order_status,

> shipping_cost,

> total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 )

>

> But then all I get are a bunch of null values (I get a number of null

> values

> equal to the number of rows I have in the table.

>

> The statement I'm running is "SELECT @@identity FROM orders"

>

> I've also tried the following:

>

> Declare @iden int

>  INSERT INTO orders ( user_id, order_entrydate, order_status,

> shipping_cost,

> total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 )

> SET @IDEN=@@IDENTITY

> INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

> Product_Price, Product_Quantity) VALUES (@IDEN ,1, 'Abel Super Series 12',

> $735.00, 1)

>  INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

> Product_Price, Product_Quantity) VALUES (@IDEN,9, 'Ande Tournament

> Monofilament', $10.49, 1)

>  INSERT INTO OrderCart (Order_id, Product_ID,

> Product_Name, Product_Price, Product_Quantity) VALUES (@IDEN,10, 'Extreme

> Fluorcarbon Monofilament', $19.99, 1)

>

> and I get the following error message:

>

> Server: Msg 545, Level 16, State 1, Line 0

> Explicit value must be specified for identity column in table 'Orders'

when

> IDENTITY_INSERT is set to ON.

> Server: Msg 515, Level 16, State 2, Line 5

> Cannot insert the value NULL into column 'order_id', table

> 'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

> The statement has been terminated.

> Server: Msg 515, Level 16, State 2, Line 8

> Cannot insert the value NULL into column 'order_id', table

> 'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

> The statement has been terminated.

> Server: Msg 515, Level 16, State 2, Line 11

> Cannot insert the value NULL into column 'order_id', table

> 'SWStoreSQL.dbo.OrderCart'; column does not allow nulls. INSERT fails.

> The statement has been terminated.

>

> Can anyone give me an idea?

>

> Thanks

>

> Can you tell me what I'm doing wrong?

>


  Return to Index