Auto number in text box problem

Question

0.00/5 (No votes)

See more:

I Use This Code For Auto number in Text Box
But After When I Saved 10 Data in my Database this code stop working and this code Show every time number "10" in Text box Not 11,12,13....

So Please Help me Resolve this Problem

Thank you

What I have tried:

C#

public void autonumber()
        {
             String connstring = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
            
            using (OleDbConnection con = new OleDbConnection(connstring))
            {

                using (OleDbCommand cmd = new OleDbCommand("select max (LoanNumber)+1 from customer", con))
                {
                    con.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();

                    if(dr.HasRows)
                    {
                        while(dr.Read())
                        {
                            textBoxLoanNumber.Text = dr[0].ToString();
                            if(textBoxLoanNumber.Text == "")
                            {
                                textBoxLoanNumber.Text = "1";
                            }
                        }
                    }
                    else
                    {
                        textBoxLoanNumber.Text = "1";
                    }
                    con.Close();
                }
            }
        }

Posted 29-Feb-20 22:42pm

Amar chand123

Updated 1-Mar-20 16:08pm

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 1-Mar-20 22:10pm

Is this only for display? Or do you want to store the numbers? I don't recommend storing the numbers as it tends to lead only to pain down the road.
Of course, the use of MS Access leads only to pain anyway, so maybe you're a glutton for pain?

Richard Deeming 3-Mar-20 9:34am

My guess is that your LoanNumber column is storing strings rather than numbers. The string "9" is greater than the string "10", because strings are compared character by character, and "9" is greater than "1". So your Max(LoanNumber) will always return "9".

But pay attention to the discussion in solution 1 - this is definitely not the right thing to do.

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 · Answer 1 · 2020-02-29T22:57:00

Solution 1

Auto-numbering is typically something which has to be done on the server side, not on the client side. Because with client-side method, if there are several clients, nothing insures that two clients do not get the same id at the same time.
Performing auto-numbering on client side is an anti-pattern which you should learn avoiding.

sql auto generated id[^]

Posted 29-Feb-20 22:57pm

phil.o

Updated 29-Feb-20 22:58pm

v2

Comments

Amar chand123 1-Mar-20 7:19am

then what is Solution for this Problem
because if i use ms access autonumber there is problem if i delete any data then autonumber not reset and access autonumber option is read-only

phil.o 1-Mar-20 7:31am

If you delete any data, you should not worry about having a missing value in the sequence. It has no impact at all on the performance, nor on data validity. This is a non-problem.

Amar chand123 1-Mar-20 8:47am

Is There such code, that can count rows in my database's customer Table
than we add +1 and show in Textbox than i saved database
because now i try save manually data in access autonumber column by c# Textbox and Data Successfully Saved

phil.o 1-Mar-20 9:02am

As I explained, this is not a correct way to do it like this. Please use auto-incremented primary key on the database, and stop relying on table count to get next id.

Amar chand123 1-Mar-20 9:30am

i Find a Method And its Working
my Access Database Column is Autonumber But Its Edit by this code
Please Check this Code

private void Autonumber1()
{
String connstring = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
OleDbConnection con = new OleDbConnection(connstring);
string Query = "select count(*) from customer";
OleDbCommand cd = new OleDbCommand(Query, con);
try
{
con.Open();
int count = Convert.ToInt16(cd.ExecuteScalar()) + 1;
textBoxLoanNumber.Text = count.ToString("00000");
textBoxLoanNumber.Enabled = false;
}

finally
{
con.Close();
}
}

Dave Kreskowiak 1-Mar-20 10:36am

Again, you can NEVER generate record ID's, or "ID numbers", on the client side. You WILL get collisions in a multi-user environment.

Even you you say "Well, this will work in a single-user environment", yes, it will. Right up until your customer add a second person to the system and you find out your code is NOT upgradable to handle more than one user at the same time.

Dave Kreskowiak 1-Mar-20 10:32am

NO!!! DO NOT DO THAT!!!

Again, in a multi-user system, if two clients execute that code at the same time, they will both get the same ID number. You NEVER "check out" an ID number before commiting data for that record to the database.

ALWAYS use the database auto-numbering feature to get record ID numbers. The database guarantees no two records will get the same ID number, even if two clients submit new data for new records at the exact same time.

You are, of course, going to have to commit a minimum amount of data to a new record to get that ID number generated. What data that is depends on your business rules.

PIEBALDconsult 1-Mar-20 22:06pm

Well, _any_ numbering of rows is an anti-pattern.

Maciej Los 2-Mar-20 4:20am

5ed!

Randy Edge · Answer 2 · 2020-03-01T15:29:00

If you want to simulate row Numbers in SQL Server you can generate the row number in a query.

that way you can still have an identity column and still return numbered records.
What ever you do DON'T try to use Row Number to link queries.

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15

sample:

SQL

USE AdventureWorks2012;   
GO  
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

It will return :

Row FirstName    LastName               SalesYTD  
--- -----------  ---------------------- -----------------  
1   Linda        Mitchell               4251368.54  
2   Jae          Pak                    4116871.22  
3   Michael      Blythe                 3763178.17  
4   Jillian      Carson                 3189418.36  
5   Ranjit       Varkey Chudukatil      3121616.32  
6   José         Saraiva                2604540.71  
7   Shu          Ito                    2458535.61  
8   Tsvi         Reiter                 2315185.61  
9   Rachel       Valdez                 1827066.71  
10  Tete         Mensa-Annan            1576562.19  
11  David        Campbell               1573012.93  
12  Garrett      Vargas                 1453719.46  
13  Lynn         Tsoflias               1421810.92  
14  Pamela       Ansman-Wolfe           1352577.13