Get value of newid() function of SQLsrv2k from ASP
-
ASP & SQLserver2k & Windows2003 I use ASP to add data to 2 tables I have 2 tables: Invoice and InvoiceDetail Invoice contains: InvoiceID, ClientName, InvoiceDate........ InvoiveDetail contains: InvoiceID, Name,Price,Quantity.... I set default of InvoiceID(Invoice table)=newid() so I dont need to insert a new value when I add a new record but I can not get value of newid() Example: Invoice for Client "Tom" I use "INSERT Invoice (ClientName, InvoiceDate) VALUES ('Tom','30/1/2004')" and SQLserver2k auto create a value for InvoiceID such as {BB10687B-87F7-44E4-9397-8D510BBDFA9C} and then ..... I WANT TO GET {BB10687B-87F7-44E4-9397-8D510BBDFA9C} I NEED IT to add detail of this invoice for InvoiceDetail table like: InvoiceID - Name - Price - Quantity {BB10687B-87F7-44E4-9397-8D510BBDFA9C} - book - 26.5$ - 5 {BB10687B-87F7-44E4-9397-8D510BBDFA9C} - CD - 700.95$ - 1 I think 1) SELECT InvoiceID FROM Invoice WHERE ClientName='Tom' AND InvoiceDate='30/1/2004'" but this is not good, may be duplicate all value in all column 2) May I have to add a new column in table 3) I have to calculate my InvoiceID and not use newid() function of SQLsrv2k:doh::sigh: Please help me to get value of newid() function in SQLserver2k thanks alot
-
ASP & SQLserver2k & Windows2003 I use ASP to add data to 2 tables I have 2 tables: Invoice and InvoiceDetail Invoice contains: InvoiceID, ClientName, InvoiceDate........ InvoiveDetail contains: InvoiceID, Name,Price,Quantity.... I set default of InvoiceID(Invoice table)=newid() so I dont need to insert a new value when I add a new record but I can not get value of newid() Example: Invoice for Client "Tom" I use "INSERT Invoice (ClientName, InvoiceDate) VALUES ('Tom','30/1/2004')" and SQLserver2k auto create a value for InvoiceID such as {BB10687B-87F7-44E4-9397-8D510BBDFA9C} and then ..... I WANT TO GET {BB10687B-87F7-44E4-9397-8D510BBDFA9C} I NEED IT to add detail of this invoice for InvoiceDetail table like: InvoiceID - Name - Price - Quantity {BB10687B-87F7-44E4-9397-8D510BBDFA9C} - book - 26.5$ - 5 {BB10687B-87F7-44E4-9397-8D510BBDFA9C} - CD - 700.95$ - 1 I think 1) SELECT InvoiceID FROM Invoice WHERE ClientName='Tom' AND InvoiceDate='30/1/2004'" but this is not good, may be duplicate all value in all column 2) May I have to add a new column in table 3) I have to calculate my InvoiceID and not use newid() function of SQLsrv2k:doh::sigh: Please help me to get value of newid() function in SQLserver2k thanks alot
From what little testing I have done, I don't thing you will be able to obtain the GUID from the table after an insert like with an auto incrementing or random integer column. With an integer you can use SELECT @ID = @@Idendity. But it doesn't appear to work with the GUID.
DROP TABLE tempdb..TestGUID
DROP TABLE tempdb..TestAutoID
GO
CREATE TABLE tempdb..TestGUID (
ID uniqueidentifier ROWGUIDCOL Not NULL DEFAULT (newid()),
TestValue varchar(50)
)CREATE TABLE tempdb..TestAutoID (
ID int Identity (1, 1) Not Null,
TestValue varchar(50)
)
GOINSERT INTO tempdb..TestGUID
( TestValue )
SELECT 'Test GUID'
SELECT @@IdentityINSERT INTO tempdb..TestAutoID
( TestValue )
SELECT 'Test Integer ID'
SELECT @@IdentitySELECT * FROM tempdb..TestGUID
SELECT * FROM tempdb..TestAutoIDOutPut
(1 row(s) affected)
NULL
(1 row(s) affected)
(1 row(s) affected)
1
(1 row(s) affected)
ID TestValue
E51F034D-BD69-4A34-9983-A2B1007FBA74 Test GUID
(1 row(s) affected)
ID TestValue
1 Test Integer ID
(1 row(s) affected)
As you can see the @@Identity returns a null for the GUID and the actual value for the integer ID. You may have to obtain the GUID value prior to the insert by placing it in a variable. Then you can use the variable in both the header and detail tables. Sorry I couldn't be much more help. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)