Help with VB.net incrementing a field in a database by 1

Question

5.00/5 (1 vote)

See more:

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 24-Feb-12 5:37am

Oblit3ration

Updated 7-Apr-23 19:23pm

Add a Solution

3 solutions

Add a Solution

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

Not Active · Accepted Answer · 2012-02-24T06:59:00

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.

Dave Kreskowiak · Accepted Answer · 2012-02-24T09:08:00

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.

Simon_Whale · Accepted Answer · 2012-02-24T12:19:00

Solution 3

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[^]

Posted 24-Feb-12 12:19pm

Simon_Whale

Help with VB.net incrementing a field in a database by 1

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Help with VB.net incrementing a field in a database by 1

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us