CODEWITHSUNDEEP
c#sql-serverauto-increment
cdrrr
cdrrr

Reputation: 147

Auto generate and AutoIncrement ID in C# when trying to add new record to database

I'm using this code to select the maxID from a database table and each time I want to add a new record, the autogenerated ID is not the last one +1.

 public formularAddCompanie()
    {

        InitializeComponent();
        try
        {
            string cs = "Data Source=CODRINMA\\CODRINMA;Initial Catalog=TrafficManager;Integrated Security=True";
            string select = "SELECT max(IDCompanie) FROM Companii";

            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand cmd2 = new SqlCommand(select, con);
                SqlDataReader sda = cmd2.ExecuteReader();
                DataTable idmax = new DataTable("idmax");
                idmax.Load(sda);
                if (idmax.Rows[0][0].ToString().Trim() == "") { txtID.Text = "1"; }
                else { txtID.Text = (int.Parse(idmax.Rows[0][0] .ToString() + 1).ToString()); }
            }
        }
        catch (Exception er) { MessageBox.Show(er.Message); }
    }

The table from where the selection is made, looks like this:

IDCompany   Name   Address   City  RegNo
1           A      Street    NY    123

Each time I want to add a new record, the autogenerated ID is like this: 11, 111, 1111. It takes the last ID and add another 1 next to it. What am I missing?

Upvotes: 0

Views: 7063

Answers (5)

user3284126
user3284126

Reputation: 55

Try This one, my id format is USR001.The code will generate auto id based on the last id inside the database. If the last id in the database is USR001, the the code will generate USR002 and put the id to the textbox

con.Open();
            string sqlQuery = "SELECT TOP 1 kode_user from USERADM order by kode_user desc";
            SqlCommand cmd = new SqlCommand(sqlQuery, con);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                string input = dr["kode_user"].ToString();
                string angka = input.Substring(input.Length - Math.Min(3, input.Length));
                int number = Convert.ToInt32(angka);
                number += 1;
                string str = number.ToString("D3");

                txtKodeUser.Text = "USR" + str;
            }
            con.Close();

Upvotes: 0

Steve
Steve

Reputation: 216273

I just add this because it seems that none cares about the weakness of the code posted by the poster. First the MAX function is not reliable if you want to find the next autoincrement value that will be assigned to an ID column. Concurrency could wreak havoc with any schema that use MAX. Just suppose that another user has already retrieved the MAX for its own INSERT operation, then depending on the relative speed of the two computers you or the other user will insert a duplicate value for the IDCompany field.

The only correct way to do this common task is to use the IDENTITY property for the column IDCompany and when you need to insert a new record you should write something like this

try
{
    string insert  = "INSERT INTO Companii (Name,Address,City,RegNo) 
                      VALUES(@name,@address,@city,@regno);
                      SELECT SCOPE_IDENTITY()";

    using (SqlConnection con = new SqlConnection(cs))
    using (SqlCommand cmd = new SqlCommand(insert, con))
    {
        con.Open();
        cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = txtBoxCity.Text;
        .... and on for the other parameters ....
        int companyID = Convert.ToInt32(cmd.ExecuteScalar());
        ... work with the just added company if required
    }
}
catch (Exception er) 
{ MessageBox.Show(er.Message); }

SCOPE_IDENTITY will return the last identity value inserted into an identity column in the same scope and in this context scope means the connection used by your command.

In any case, if the MAX approach is still required then the code could be simplified a lot using a modified query and SqlCommand.ExecuteScalar instead of building an SqlDataReader, filling a datatable, trying to parse the result with ifs

string getMax = @"select COALESCE(MAX(IDCompany), 0) + 1 AS maxPlusOne 
                  from Companii"
using(SqlConnection cnn = new SqlConnection(.....))
using(SqlCommand cmd = new SqlCommand(getMax, cnn))
{
    cnn.Open();
    int nextCompanyID = Convert.ToInt32(cmd.ExecuteScalar());
}

The COALESCE function checks the result of the MAX function and if it is NULL returns the second parameter (here 0), then just increment by 1 to get the next MAX directly from the database. ExecuteScalar will do the call returning just the maxPlusOne alias field

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

Interestingly, note that

string a = "The meaning of life is " + 42;

converts 42 to a string, creating the result

a == "The meaning of life is 42"

Look at this code:

(int.Parse(idmax.Rows[0][0] .ToString() + 1).ToString()); }

You are converting idmax.Rows[0][0] to a string and adding +1 to the end of the string rather than to an integer value. Try

(int.Parse(idmax.Rows[0][0].ToString()) + 1).ToString(); }

Note that idmax.Rows[0][0] should already have an integer in it (as pointed out in the comments). If that's the case, you can simplify to 

(idmax.Rows[0][0] + 1).ToString(); }

Upvotes: 2

Dgan
Dgan

Reputation: 10285

try this snippet:

Convert Your String into Int. String with + operator will con-cat and with int it will add numbers. 

if (idmax.Rows[0][0].ToString().Trim() == "") { txtID.Text = "1"; }

else { 
txtID.Text =  Convert.ToString(Convert.ToInt32(idmax.Rows[0][0] .ToString())+1); }

Upvotes: 0

romanoza
romanoza

Reputation: 4862

idmax.Rows[0][0].ToString() + 1 produces string, not int.

You can try 

txtID.Text = (Convert.ToInt32(idmax.Rows[0][0]) + 1).ToString();

Upvotes: 2

Related Questions