Problem in generate autonumber in textbox by using database

Question

1.00/5 (1 vote)

See more:

i use this Code for Generate AutoNumber but i have a Problem when i start enter new Data in DataBase AutoNumber Not Work if i Not have any data in my DataBase, if only work when i have 00001 in my DataBase but if i start form Zero then it's Not Work

What I have tried:

C#

private void Autonumber()
        {
            String connstring = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            OleDbConnection con = new OleDbConnection(connstring);
            string Query = "select MAX(ReceiptNo) from Data";
            OleDbCommand cd = new OleDbCommand(Query, con);
            
            {
                con.Open();
                int count = Convert.ToInt16(cd.ExecuteScalar()) + 1;
                txtRecd.Text = count.ToString("00000");
                txtRecd.Enabled = false;

            }

            
        }

Posted 27-May-20 2:33am

Amar chand123

Updated 27-May-20 21:14pm

Patrice T

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2020-05-27T02:39:00

Solution 1

You should use an auto-incremented column on the database itself. Generating id's on client side becomes quicly subject to errors whenever there are more than one user.

Posted 27-May-20 2:39am

phil.o

Comments

Amar chand123 28-May-20 2:48am

Only one user Project and auto incremented column problem is if i have 10 data in database and i delete last 7 data and add new data in database then SrNo is show 11 not 7

phil.o 28-May-20 3:42am

This is a common non-issue. You should not care that your ids in the database are not contiguous. They are not meant to make your data pretty, they are meant to index a row unambiguously. Strict continuity of numeric ids is nothing else than cosmetic.

RickZeeland · Accepted Answer · 2020-05-27T03:03:00

Solution 2

Phil.o's answer is the recommended way, but you could also try something like this:

var obj = cd.ExecuteScalar();
var count = (obj == DBNull.Value ? 0 : Convert.ToInt32(obj));

If your table is empty the ExecuteScalar will return a DBNull.Value.

Posted 27-May-20 3:03am

RickZeeland

Updated 27-May-20 21:10pm

v4

Comments

Amar chand123 28-May-20 2:44am

Not working

RickZeeland 28-May-20 3:10am

Corrected the answer, you probably need to use DBNull.Value instead of Null.

Amar chand123 28-May-20 4:46am

Thank you