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?
>
|
View Cart
