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

0
 
PreeceCommented:
Try something like this:

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

Preece
0
 
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

I am trying this

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

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


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

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

now all I get is a 1

0
 
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()
0
 
Wildone63Author Commented:
Thank You. That worked fine.

I will investigate the stored procedure approach. Thank You again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.