How to get the "L:ast" record

Try something like this:

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

Preece

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

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

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

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?

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

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

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.

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.

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

Ok,

I am trying this

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

But the identity var is empty after I execute.

I changed


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

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

now all I get is a 1

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()

Thank You. That worked fine.

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