Fill textbox with an incremented value from access database in C#

Question

I created a C# application to query and insert a product database. However I am here with a small doubt and if anyone can help me i thank you right away.

The following is:

I have a form to insert data into the database created in MS Access 2007, with the values of reference, sale number, client code, client name, quantity and position number in archive;

Here is my code until the moment:

 private void btn_save_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=product.accdb");
        OleDbCommand check_sn = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([sn] = @sn)", con);
        OleDbCommand check_reference = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([reference] = @ref)", con);
        OleDbCommand check_number = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([number] = @num)", con);

        con.Open();
        check_reference.Parameters.AddWithValue("@ref", textBox_ref.Text);
        check_sn.Parameters.AddWithValue("@sn", textBox_sn.Text);
        check_number.Parameters.AddWithValue("@num", textBox_num.Text);

        int refExist = (int)check_reference.ExecuteScalar();
        int SNExist = (int)check_sn.ExecuteScalar();
        int numExist = (int)check_number.ExecuteScalar();


        if (refExist > 0)
        {
            MessageBox.Show("A product with this reference already exists....!");
        }
        else if (SNExist> 0)
        {
            MessageBox.Show("A product with this sale number already exists....!");
        }
        else if (numExist > 0)
        {
            MessageBox.Show("A product with this archive number already exists....!");
        }
        else
        {
            try
            {
                String reference = textBox_ref.Text.ToString();
                String sn = textBox_ov.Text.ToString();
                String cod_client = textBox_cod.Text.ToString();
                String client = textBox_cliente.Text.ToString();
                String qtd = textBox_qtd.Text.ToString();
                String number = textBox_num.Text.ToString(); //This will be the incremented number

                String my_query = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";
                OleDbCommand cmd = new OleDbCommand(my_query, con);
                cmd.ExecuteNonQuery();

                MessageBox.Show("Data saved successfully...!");

            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            finally
            {

                con.Close();
            }
            cleanTextBoxes(this.Controls);
        }
    }



    private void search_btn_Click(object sender, EventArgs e)
    {
        Form search = new Form_search();

        search.Show();

        this.Hide();
    }
}

}

How can i make it so that instead of manually entering the position number in archive in the textbox it can be automatically filled with the new position in archive. For example, my last product inserted has the position 50 in archive, the new one will automatically be number 51 and so on ... and this number should appear automatically in the textbox so that the user knows what is the number of the new registered product.

Thank you,

Use a scalar, to read a returned id back into the database. You can use this in your query to get the value. https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row — Greg, Feb 12 '19 at 16:47
Please need help to finish this aplication I already tried a select top1 desc into number collumn but how I return this valeu into the textbox? — satbr, Feb 12 '19 at 23:28
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Jun 08 '19 at 10:52

score 0 · Answer 1 · answered Feb 13 '19 at 09:53

Ok i have tried this and works but how i do now to increment this value +1?

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Aneis_Calibre.accdb");
        con.Open();
        OleDbDataReader myReader = null;
        OleDbCommand number = new OleDbCommand("SELECT TOP 1 [number] FROM product Order by [number] desc", con);
        myReader = number.ExecuteReader();
        while (myReader.Read())
        {

            textBox_num.Text = (myReader["number"].ToString());

        }
        con.Close();

score 0 · Answer 2 · answered Feb 13 '19 at 16:16

Inside your query, you would want to do something along these lines.

INSERT ... 
     OUTPUT inserted.identity_column
     VALUES (...)

That will return a row with a value for the id. The identity column in SQL will always increment automatically for you. Which would alleviate your approach where you grab the last record and do:

int.TryParse(reader["..."]?.ToString(), out int id); 
textbox.Text = id++;

By using the scalar, or reader though I would recommend scalar if you return a single column with a modified SQL query would result in the exact newly inserted id.

Fill textbox with an incremented value from access database in C#

2 Answers2