?
Solved

How to get the "L:ast" record

Posted on 2011-10-06
17
Medium Priority
?
251 Views
Last Modified: 2012-05-12
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.

0
Comment
Question by:Wildone63
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 12

Expert Comment

by:Preece
ID: 36925299
Try something like this:

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

Preece
0
 
LVL 12

Expert Comment

by:Preece
ID: 36925303
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
 
LVL 12

Expert Comment

by:Preece
ID: 36925319
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Expert Comment

by:Preece
ID: 36925353
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
 
LVL 1

Author Comment

by:Wildone63
ID: 36925382
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36925386
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
 
LVL 12

Expert Comment

by:Preece
ID: 36925513
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
 
LVL 1

Author Comment

by:Wildone63
ID: 36926624
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36926670
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
 
LVL 1

Author Comment

by:Wildone63
ID: 36927221
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36927269
Modify your Select statement to include the primary key field.
0
 
LVL 1

Author Comment

by:Wildone63
ID: 36927456
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
 
LVL 1

Author Comment

by:Wildone63
ID: 36927550
Ok,

I am trying this

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

But the identity var is empty after I execute.
0
 
LVL 1

Author Comment

by:Wildone63
ID: 36927561
I changed


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

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

now all I get is a 1

0
 
LVL 1

Author Comment

by:Wildone63
ID: 36927679
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36928150
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
 
LVL 1

Author Closing Comment

by:Wildone63
ID: 36931690
Thank You. That worked fine.

I will investigate the stored procedure approach. Thank You again.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question