Solution required for a Custom Web Control

Guest User

Hi

I have almost completed my E-Commerce website and needed help with one of the final implementations.

I need to have an Order Id\Ref upon checking out and thought the best way to go with this would be to...

1. Create an Order Id table in the database.
2. Create a Web Custom Control on the asp page to display the value.
3. Write the code to wire up the plumbing between UI to the DL.
4. Write a list of Order Ids in the database starting from 1 onwards.

I wondered how the auto incrementing of the Order Id can be done. So say that once Order Id 1 has gone how will the page know to display Order Id 2 automatically ready for the next customer?

Thanks

Steven

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi Steven,

You no need to do it manually by checking next id or the total number of records in the table.

Sql server provides you to keep the identity column in the table. You can configure the seed and increment value and no need to insert. Because it will take care of the order of numbers.

CREATE TABLE Order
(
iOrderID INT IDENTITY(1,1) PRIMARY KEY
,ProductID INT REFERENCES Product(ProductID)
,Qty INT
,OrderDate DATETIME DEFAULT GETDATE()
)

Here you have set the identity column on the orderID and it will start from the 1 and increment by one. When you truncate the table will reset the identity value in the table and when you delete the records it will continue the identity value from where it ends.

There are three ways to get the identity value from the sql server.

@@SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Instead of writing separate logic to read the identity value after inserting it can returns the stored procedure with output parameter.

CREATE PROC sp_getIdentity @id int output,@name varchar(20)
as

INSERT INTO t1 (name) VALUES (@name);
select @id = SCOPE_IDENTITY()
go

Then call the stored procedure in a sql command:

SqlCommand cmd = new SqlCommand("sp_getIdentity", connection);
connection.Open();
SqlParameter sp= new SqlParameter("@id",SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(sp);

int id=sp.Value.ToString();

For more info:

http://www.dotnetfunda.com/articles/article1547-how-to-retrieve-identity-auto-incremented-value-using-output-paramter-thr.aspx

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

http://ramanisandeep.net/tag/how-to-retrieve-the-identity-value/
Guest User14y ago
Hi

I now have a solution for the above post.

I am going with the Identity Increment and Increment Seed for the Order Id column in the Database.

This way once an order has been processed all the through to the checkout stage a redirect will then occur to go to an order confirmation page which then fire the Insert to the Database via the code behind page_load.