Wildone63
asked on
How to get the "L:ast" record
I have a aspx page with several fields. These fields are the user name email phone etc...
"The header section of an order"
There also is approximately 50 items the user can request.
I want to write the header section into a table called orders. and return the order number.
I then will write the items requested into a second table "With the order#"
The order number will be generated when the record is added.
So..... My question is how do I ensure I get the order number for the order I just submitted.
"The header section of an order"
There also is approximately 50 items the user can request.
I want to write the header section into a table called orders. and return the order number.
I then will write the items requested into a second table "With the order#"
The order number will be generated when the record is added.
So..... My question is how do I ensure I get the order number for the order I just submitted.
Also, to get the "identity" value, the table that you are inserting into will need to have the id field set as auto-increment. Usually by 1.
Preece
Preece
So, as an example, in VB.NET I'll use a reader to do the insert and get the id rather than just execute the insert.
Preece
Preece
To better explain the identity field, in SQL Server Management Studio, I usually set the id column to an int, and in the column properties below you'll see an Identity Specification section. Expand that section and set the Is Identity to Yes, and the increment and seed both to 1.
Hope this helps!
Preece
Hope this helps!
Preece
ASKER
Thank You.
Yes I do understand the identity field and how to use it. My question is how do I ensure I get the identity of the record I just inserted?
Yes I do understand the identity field and how to use it. My question is how do I ensure I get the identity of the record I just inserted?
You can also pass every thing to a stored procedure and it can do all the insertion, rolling back if any error and indicating success otherwise. You can use table valued parameters to send all the details of the order to stored procedure.
The key is to use:
SELECT @@IDENTITY
after the insert. That guarantees that you will get the id of the record just inserted.
Preece
SELECT @@IDENTITY
after the insert. That guarantees that you will get the id of the record just inserted.
Preece
ASKER
Can someone please show me how this is done.
Use table1 with columns Name, Email
so.....
Insert into table1 (Name, email) values (@name, @email)
Now how would I use Scope_Identity?
Thank You....
Use table1 with columns Name, Email
so.....
Insert into table1 (Name, email) values (@name, @email)
Now how would I use Scope_Identity?
Thank You....
If you have a primary key field in the table which is set to be Identity, using the Select @@Identity will get you the value of this field for the record you just inserted.
ASKER
Yes I understand. I do have a primary key..... but How do I do that? Is that part of my original insert statment? do I create a new statment?
Modify your Select statement to include the primary key field.
ASKER
I am sorry. I do not understand what you are saying.
My goal here is how do I ensure I get the order number for the order I just submitted
My goal here is how do I ensure I get the order number for the order I just submitted
ASKER
Ok,
I am trying this
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteScalar()
But the identity var is empty after I execute.
I am trying this
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteScalar()
But the identity var is empty after I execute.
ASKER
I changed
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteScalar()
to
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteNonQuery
now all I get is a 1
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteScalar()
to
MyQuery = "select @@Identity"
identity = DBCmd.ExecuteNonQuery
now all I get is a 1
ASKER
Here is my whole statement. When I execute a nonquery I get nothing when I execute Scalar all I get is a 1.
MyQuery = "INSERT INTO [IWOnline].[dbo].[Orders] ([Company] ,[Customer] ,[FName] ,[LName] ,[Email] ,[RequestDate] ,[RequiredDate] ,[Store] ,[ShipMethod] ,[CreatedBy] ,[UserIP] ,[Status] ,[ApprovedBy] ,[ApprovedDate]) VALUES (@Company, @Customer, @FName, @LName, @Email, @RequestDate, @RequiredDate, @Store, @ShipMethod, @CreatedBy, @UserIP, @Status, @ApprovedBy, @ApprovedDate)"
'Create SQL parameters to write row to database
DBCmd = New SqlCommand(MyQuery, DBConn)
DBCmd.Parameters.AddWithVa lue("@Comp any", Session("Company"))
DBCmd.Parameters.AddWithVa lue("@Cust omer", Session("CustomerNo"))
DBCmd.Parameters.AddWithVa lue("@FNam e", Session("FName"))
DBCmd.Parameters.AddWithVa lue("@LNam e", Session("LName"))
DBCmd.Parameters.AddWithVa lue("@Emai l", Session("Email"))
DBCmd.Parameters.AddWithVa lue("@Requ estdate", Session("RequestDate"))
DBCmd.Parameters.AddWithVa lue("@Requ iredDate", Session("RequiredDate"))
DBCmd.Parameters.AddWithVa lue("@Stor e", Session("Store"))
DBCmd.Parameters.AddWithVa lue("@Ship Method", Session("ShipMethod"))
DBCmd.Parameters.AddWithVa lue("@Crea tedBy", Session("Createdby"))
DBCmd.Parameters.AddWithVa lue("@User IP", Session("UserIP"))
DBCmd.Parameters.AddWithVa lue("@Stat us", Session("Status"))
DBCmd.Parameters.AddWithVa lue("@Appr ovedBy", Session("ApprovedBy"))
DBCmd.Parameters.AddWithVa lue("@Appr ovedDate", Session("ApprovedDate"))
DBCmd.ExecuteNonQuery()
MyQuery = "select Scope_Identity() as OrderNo"
Session("MyOrder") = DBCmd.ExecuteNonQuery
MsgBox(Session("MyOrder"))
DBConn.Close()
MyQuery = "INSERT INTO [IWOnline].[dbo].[Orders] ([Company] ,[Customer] ,[FName] ,[LName] ,[Email] ,[RequestDate] ,[RequiredDate] ,[Store] ,[ShipMethod] ,[CreatedBy] ,[UserIP] ,[Status] ,[ApprovedBy] ,[ApprovedDate]) VALUES (@Company, @Customer, @FName, @LName, @Email, @RequestDate, @RequiredDate, @Store, @ShipMethod, @CreatedBy, @UserIP, @Status, @ApprovedBy, @ApprovedDate)"
'Create SQL parameters to write row to database
DBCmd = New SqlCommand(MyQuery, DBConn)
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.Parameters.AddWithVa
DBCmd.ExecuteNonQuery()
MyQuery = "select Scope_Identity() as OrderNo"
Session("MyOrder") = DBCmd.ExecuteNonQuery
MsgBox(Session("MyOrder"))
DBConn.Close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You. That worked fine.
I will investigate the stored procedure approach. Thank You again.
I will investigate the stored procedure approach. Thank You again.
SET NOCOUNT ON
INSERT INTO table (field1, field2) VALUES (1, 2)
SELECT @@IDENTITY
SET NOCOUNT OFF
Preece