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.

LVL 1
Wildone63Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PreeceCommented:
Try something like this:

SET NOCOUNT ON  
INSERT INTO table (field1, field2) VALUES (1, 2)
SELECT @@IDENTITY
SET NOCOUNT OFF

Preece
PreeceCommented:
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
PreeceCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PreeceCommented:
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
Wildone63Author Commented:
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?
CodeCruiserCommented:
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.
PreeceCommented:
The key is to use:

SELECT @@IDENTITY

after the insert.  That guarantees that you will get the id of the record just inserted.

Preece
Wildone63Author Commented:
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....
CodeCruiserCommented:
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.
Wildone63Author Commented:
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?
CodeCruiserCommented:
Modify your Select statement to include the primary key field.
Wildone63Author Commented:
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
Wildone63Author Commented:
Ok,

I am trying this

        MyQuery = "select @@Identity"
        identity = DBCmd.ExecuteScalar()

But the identity var is empty after I execute.
Wildone63Author Commented:
I changed


        MyQuery = "select @@Identity"
        identity = DBCmd.ExecuteScalar()

to
        MyQuery = "select @@Identity"
        identity = DBCmd.ExecuteNonQuery

now all I get is a 1

Wildone63Author Commented:
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.AddWithValue("@Company", Session("Company"))
        DBCmd.Parameters.AddWithValue("@Customer", Session("CustomerNo"))
        DBCmd.Parameters.AddWithValue("@FName", Session("FName"))
        DBCmd.Parameters.AddWithValue("@LName", Session("LName"))
        DBCmd.Parameters.AddWithValue("@Email", Session("Email"))
        DBCmd.Parameters.AddWithValue("@Requestdate", Session("RequestDate"))
        DBCmd.Parameters.AddWithValue("@RequiredDate", Session("RequiredDate"))
        DBCmd.Parameters.AddWithValue("@Store", Session("Store"))
        DBCmd.Parameters.AddWithValue("@ShipMethod", Session("ShipMethod"))
        DBCmd.Parameters.AddWithValue("@CreatedBy", Session("Createdby"))
        DBCmd.Parameters.AddWithValue("@UserIP", Session("UserIP"))
        DBCmd.Parameters.AddWithValue("@Status", Session("Status"))
        DBCmd.Parameters.AddWithValue("@ApprovedBy", Session("ApprovedBy"))
        DBCmd.Parameters.AddWithValue("@ApprovedDate", Session("ApprovedDate"))
        DBCmd.ExecuteNonQuery()
        MyQuery = "select Scope_Identity() as OrderNo"
        Session("MyOrder") = DBCmd.ExecuteNonQuery

        MsgBox(Session("MyOrder"))

        DBConn.Close()
Anthony PerkinsCommented:
As previously indicated this is best handled with a Stored Procedure, but if you insist, try it this way:
MyQuery = "SET NOCOUNT ON; 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); SELECT SCOPE_IDENTITY() OrderNo"

'Create SQL parameters to write row to database
DBCmd = New SqlCommand(MyQuery, DBConn)
DBCmd.Parameters.AddWithValue("@Company", Session("Company"))
DBCmd.Parameters.AddWithValue("@Customer", Session("CustomerNo"))
DBCmd.Parameters.AddWithValue("@FName", Session("FName"))
DBCmd.Parameters.AddWithValue("@LName", Session("LName"))
DBCmd.Parameters.AddWithValue("@Email", Session("Email"))
DBCmd.Parameters.AddWithValue("@Requestdate", Session("RequestDate"))
DBCmd.Parameters.AddWithValue("@RequiredDate", Session("RequiredDate"))
DBCmd.Parameters.AddWithValue("@Store", Session("Store"))
DBCmd.Parameters.AddWithValue("@ShipMethod", Session("ShipMethod"))
DBCmd.Parameters.AddWithValue("@CreatedBy", Session("Createdby"))
DBCmd.Parameters.AddWithValue("@UserIP", Session("UserIP"))
DBCmd.Parameters.AddWithValue("@Status", Session("Status"))
DBCmd.Parameters.AddWithValue("@ApprovedBy", Session("ApprovedBy"))
DBCmd.Parameters.AddWithValue("@ApprovedDate", Session("ApprovedDate"))
Session("MyOrder") = DBCmd.ExecuteScalar

MsgBox(Session("MyOrder"))

DBConn.Close()

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wildone63Author Commented:
Thank You. That worked fine.

I will investigate the stored procedure approach. Thank You again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.