Auto Generate ID

[abelingaw]

I have a column named Emp_Id and set as primary key (not Auto Incr).I am trying to generate an ID (employee) and save it into a SQL database.

The logic goes this way:

Generate the id number with a prefix plus the number (i.e. EMP0001).
Display it on a textbox on a form so that I can save it on the Emp_Id column.

I can't provide any codes since I really do not know how to begin with this.

Appreciate your help. Thanks

[niranga]

You can generate the IDs using couple of variables

string empPrefix = "EMP";
int counter = 0;

public string getNewID()
{
    counter = counter + 1;
    return empPrefix + counter.ToString("D4");
}

[Fenrir()]

I would suggest you get the max emp ID from the database first and then increment it by whatever you need SELECT MAX(Emp_Id)+1 FROM Table

[ChrisHunter]

As Fenrir() said you should generate the ID in within your database. If you generate the ID within your application you risk generating duplicate IDs which will cause errors.

You should make the ID the primary key for in a table because it is be the unique identifier for each record. When you insert the a new record you can return the ID that is automatically generated.

[abelingaw]

Tried Fenrir()'s suggestion. Seems ok but how about the prefix?

[Fenrir()]

Once you have the value from the database you should have no problem concatenating the prefix. Or are we missing something?

Click Here

Here's a good example that might help you. Instead you will type

Textbox1.Text = "EMP"+SqlCommand.ExecuteScalar();

you will obviously have to do some conversions and exception handling but i think you'll get the idea.

[jireh]

Here's a good way to do that:
1. Create a table for generated id maintenance. The table should contain fields like...

Prefix nvarchar(10)
StartingNumber int
NumberWidth int

Example:
Prefix: EMP
StatingNumber: 1
NumberWidth: 6

so to generate a new employee id you just have to query the last generated number

1. SQL:

   select (StartingNumber + 1) as NewEmployeeID,
   (NumberWidth - LEN(StartingNumber + 1) as LengthToAdd
   from MaintenanceTable

2. Code:

   System.Text.StringBuilder id = new System.Text.StringBuilder();
   int lengthToAdd = 5; //replace the data from the database record

   for (int i = 1; i <= lengthToAdd; i++)
   {
   id.Append("0");
   }

   string prefixID = "EMP"; //replace the data from the database record
   int newID = 2; //replace the data from the database record
   string employeeId = string.Format("{0}{1}{2}", prefixID, id.ToString(), newID);

HTH ;)

[abelingaw]

Ok, I got finally got this to work.

{
            SqlDataAdapter Adapter;
            DataSet ds;


            cn.Open();
            Adapter = new SqlDataAdapter("SELECT MAX(CID) FROM candidates",cn);
            ds = new DataSet();

            Adapter.Fill(ds, "candidates");

            try
            {
                System.Text.StringBuilder id = new System.Text.StringBuilder();
                int lengthToAdd = 3;

                for (int i = 1; i <= lengthToAdd; i++)
                {
                    id.Append("0");
                }

                string prefixID = "EMP"; int newID = 2;
                string employeeId = string.Format("{0}{1}{2}", prefixID, id.ToString(), newID);

                txtcp.Text =employeeId;
            }

            catch (Exception ex)
            {
               MessageBox.Show(""+ex);
            }

            cn.Close();
        }

Now the problem is that, when I add new record, it still generates the same ID number which is EMP0002.

Its suppose to increment by 1, say EMP0003 and so on.

[pritaeas]

Its suppose to increment by 1, say EMP0003 and so on.

So where is the + 1 ?

[abelingaw]

Something like this?

Adapter = new SqlDataAdapter("SELECT MAX(CID) + 1 FROM candidates",cn);

Or Am i going the other way.

[pritaeas]

Something like this?

Right.