How To AutoNumber With in Database?

Question

I use C# Windows Form. i'm need auto number not duplicate.

I'm Import file excel to datagridview and Generate field 'id' for not duplicate.

But don't work.

Ex.

First Import. ID | Name P001 => Auto Gen | A P002 => Auto Gen | B

Second Import. ID | Name P001 => Auto Gen But I need P003 | C P002 => Auto Gen But I need P004 | D

Code:

    SqlConnection Conn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        StringBuilder sb = new StringBuilder();
        SqlTransaction tr;

        private void testExcel_Load(object sender, EventArgs e)
        {
            string appConn = ConfigurationManager.ConnectionStrings["connDB"].ConnectionString;
            Conn = new SqlConnection();
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();

            }
            Conn.ConnectionString = appConn;
            Conn.Open();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    TextBox1.Text = openFileDialog1.FileName;
                    File.ReadAllText(TextBox1.Text);
                }
                OleDbConnection conn = new OleDbConnection();

                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + TextBox1.Text + @";Extended Properties=""Excel 8.0; HDR=Yes; IMEX=1; ImportMixedTypes=Text; TypeGuessRows=0"" ";
                OleDbCommand cmd = new OleDbCommand("SELECT * " + "FROM [SHEET1$]", conn);
                DataSet ds = new DataSet();
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                ad.Fill(ds);

                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception)
            {

            }
        }

private void button2_Click(object sender, EventArgs e)
        {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    tr = Conn.BeginTransaction();

                    sb.Remove(0, sb.Length);
                    sb.Append("INSERT INTO tbl_Asset (AsId,AsName)");
                    sb.Append("VALUES (@Asid,@AsName)");
                    string sqlSave = sb.ToString();

                    cmd.CommandText = sqlSave;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = Conn;
                    cmd.Transaction = tr;
                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("@Asid", SqlDbType.VarChar).Value = dataGridView1.Rows[i].Cells[0].Value;
                    cmd.Parameters.Add("@AsName", SqlDbType.VarChar).Value = dataGridView1.Rows[i].Cells[1].Value;

                    cmd.ExecuteNonQuery();
                    tr.Commit();

                }
                MessageBox.Show("Insert Done", "Result", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            int cellnum = 0;

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                cellnum = cellnum + 1;
                dataGridView1.Rows[i].Cells[0].Value = txtID.Text + "000" + cellnum;                
            }
        }

Thanks you so much for you time. :)

Here is a site that can walk you thru how to setup AutoNumber Field It took less than a second to do this google search http://www.techonthenet.com/access/tables/autoinc.php — MethodMan, Sep 17 '12 at 20:48
Why don't you use an [identity](http://msdn.microsoft.com/en-us/library/ms186775%28v=sql.100%29.aspx) column? — Tim Schmelter, Sep 17 '12 at 20:50
SQL Server don't allow to add an identity property to an already populated field. You may have to recreate the table and pupulate it using `set identity_insert table_name on;` to populate with the current data. — Ricardo Souza, Sep 17 '12 at 21:08

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

If you're doing this purely in SQL Server, you could add an identity column then add a computed column to give you your custom format:

alter table MyTable add MyNewColumn as 'P' + right('000' + cast(MyIdentColumn as varchar(3)), 3)

Note that this example will give unexpected results after your identity reaches 1000.

edited May 23 '17 at 10:32

Community

1
1

answered Sep 17 '12 at 21:39

Tim Lehner

14,813
4
59
76

How To AutoNumber With in Database?

1 Answers1