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

Question

0.00/5 (No votes)

See more:

Hello,

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;

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,

What I have tried:

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_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";
                OleDbCommand cmd = new OleDbCommand(my_querry, 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();
    }
}

Posted 12-Feb-19 6:37am

Member 12801143

Updated 13-Feb-19 3:05am

Add a Solution

Comments

Richard Deeming 12-Feb-19 13:47pm

Is the database only ever used by one person at a time?

If not, then the value you display will probably be wrong.

Richard Deeming 12-Feb-19 13:48pm

Also:

String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

4 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)

OriginalGriff · Answer 1 · 2019-02-12T08:14:00

Use an IDENTITY or "auto number" field in the DB and let it handle the sequential values - it's a dangerous idea to "pre-assign" incremental values in your presentation code as it can lead to some nasty bugs in production which are a PITA to find, impossible to duplicate reliably, and incredibly hard to fix.

And Richard is absolutely right: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Patrice T · Answer 2 · 2019-02-12T11:57:00

How can you have in same piece of code an SQL command safe to injection and an other one subject to injection?

C#

String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

CHill60 · Answer 3 · 2019-02-13T03:06:00

As @OriginalGriff has already stated, your ID field in your database table should be an AutoNumber field. That way Access will automatically increment your ID for you, and if there is more than one user you won't end up with duplicate IDs.

Once you have set that up you can use the @@Identity construct that allows you to query for the last Id that you inserted as long as you use the same connection.

In the soon to be deleted solution 3 that you posted you have ignored all of the advice about SQL Injection and are still trying to increment the max id on the table - that technique simply does not work.

To overcome your SQL injection problem (and coincidentally fix any issues you might have with column types and incorrectly formatted string concatenation) do something like this

C#

String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES(@ref,@sn,@cod_client,@client,@qtd,@number)";
OleDbCommand cmd = new OleDbCommand(my_querry, con);
cmd.Parameters.AddWithValue("@ref", reference);
cmd.Parameters.AddWithValue("@sn", sn);
cmd.Parameters.AddWithValue("@cod_client", cod_client);
cmd.Parameters.AddWithValue("@client", client);
cmd.Parameters.AddWithValue("@qtd", qtd);
cmd.Parameters.AddWithValue("@number", number);
cmd.ExecuteNonQuery();

To get the last Id entered do this

C#

cmd.CommandText = "Select @@Identity";
int newID = (int)cmd.ExecuteScalar();

Note: This is the same cmd object that was used to issue the Insert command.
Do not close the connection before using Select @@Identity
Do not issue any other SQL commands on that connection before using Select @@Identity

Member 12801143 · Answer 4 · 2019-02-12T22:55:00

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();

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

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0