Click here to Skip to main content
15,867,765 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi all,

Would someone mind helping me with the following please?

I'm using Visual Web Developer Express, with Microsoft's StarterSite in order to start to learn a small amount of web development. I've been practising getting a webpage to enter data into fields in a couple of tables so far (using SQL Server Compact), but have run into a small roadblock.

I have a small page, with a button on it. When I click the button I'd like the code to look at a field in a table (call the field "InvoiceNumber" and call the table "InvoiceTable"), take the last number stored, increment it by "1" then store it back in the "InvoiceTable" and display the new invoice number on the page.

Currently the button is called "New Invoice", so the idea is that clicking this button is going to create a new invoice, with a number one newer than the latest one in the table.

If someone could help I'd be eternally grateful.

Many thanks
Posted
Updated 7-Apr-23 19:23pm

This could easily be done with a stored proc

DECLARE @invnum INT;
SELECT @invnum = MAX(InvoiceNumber) FROM InvoiceTable

@invnum = @invnum + 1

INSERT INTO InvoiceTable (InvoiceNumber) VALUES (@invnum)

RETURN @invnum


using(SqlCommand cmd = new SqlCommand("proc name", conection))
{
  int invnum = (int)cmd.ExecuteScalar();
}


If the field is a number you could get the same affect by creating a autonumber field and just allowing the database to increment the value.
 
Share this answer
 
Yoy don't EVER do this in a real database. AutoId columns are better for this because of the problems of multiple users doing the exact same operation. If they both run the code to get the last number, they will each come up with the same result and try to create the same invoice number. Obviously this would be a very bad thing.
 
Share this answer
 
I would change your approach and use an IDENTITY column. I would have a read of these articles

Understanding Identity Columns[^]
SQL SERVER – Add or Remove Identity Property on Column[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900