C# access database updating record if it already exists

Question

0.00/5 (No votes)

See more:

, +

Hello,
I have a C# winform application for selling books, And in my selling form I have some textboxes where the user can enter the name of the book, the amount, the price, etc...
I have a button for inserting these values into an Access datatable called SoldProducts, So my problem is that I want to check if the BookName already existed in the SoldProducts table, So if it did exist Just add the amount in the datagridview to the amount in the datatable but if it doesn't exist then insert it as a new record.

What I have tried:

I have tried returning the data in the Access datatable and filling a C# datatable with an OleDB dataReader, then looping through it to check for every BookName in every row in the table with this code :

C#

//recieving data from the database and inserting it to the datatable
            OleDbCommand reader = new OleDbCommand("select * from SoldProducts", connection);
            DataTable table = new DataTable();
            connection.Open();
            table.Load(reader.ExecuteReader());
            connection.Close();
            ////////////////////////////////////
            string cmdUpdate = @"Update SoldProducts 
                   set Amount = Amount + @amt
                   where BookName = @book";

            OleDbCommand command2 = new OleDbCommand(cmdUpdate, connection);
            command2.Parameters.Add("@book", OleDbType.VarWChar);
            command2.Parameters.Add("@amt", OleDbType.Integer);
            command2.Parameters.Add("@price", OleDbType.Double);
            //----------------------------------------------------------------

            //looping and checking if the BookName already exists
            foreach (DataRow datarow in table.Rows)
            {
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    command2.Parameters["@amt"].Value = Convert.ToInt32(r.Cells[1].Value);
                    command2.Parameters["@book"].Value = Convert.ToString(r.Cells[0].Value);
                    command2.Parameters["@price"].Value = Convert.ToDouble(r.Cells[4].Value);
                    //-------------------------------------------------------------------------------
                    if (r.Cells[0] == datarow.ItemArray[0])
                    {
                        connection.Open();
                        command2.ExecuteNonQuery();
                        connection.Close();
                    }
                    else
                    {
                        string cmdInsert = @"INSERT INTO SoldProducts (BookName, Amount, Price) Values('" + Convert.ToString(r.Cells[0]) + "', '" + Convert.ToString(r.Cells[1]) + "', '" + Convert.ToString(r.Cells[4]) + "')";
                        OleDbCommand command3 = new OleDbCommand(cmdInsert, connection);
                        connection.Open();
                        command3.ExecuteNonQuery();
                        connection.Close();
                    }
                }
            }

So as u can see I used a nested loop to check for the bookname in both the datatable and the datagridview then decided whether I should insert or update, But it simply didn't work for some reason, It didn't even throw an exception or anything.

Do you have any other ideas on how to perform that task ?

Sorry if my question is too long, I am still a begginner.
Thanks in advance.

Posted 25-Aug-21 2:49am

Abdelrahman-Dev

Updated 25-Aug-21 3:10am

Add a Solution

1 solution

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)

Richard Deeming · Accepted Answer · 2021-08-25T03:10:00

Quote:

C#

string cmdInsert = @"INSERT INTO SoldProducts (BookName, Amount, Price) Values('" + Convert.ToString(r.Cells[0]) + "', '" + Convert.ToString(r.Cells[1]) + "', '" + Convert.ToString(r.Cells[4]) + "')";

Not like that!

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[^]

You already know how to use parameters - you've done it in the UPDATE query. So why haven't you done the right thing here?

As to your question, all you need to do is execute the UPDATE command and check the number of rows affected. If it doesn't affect any rows, then execute an INSERT command.

C#

using (var command = new OleDbCommand("UPDATE SoldProducts SET Amount = Amount + @Amount WHERE BookName = @BookName", connection))
{
    command.Parameters.AddWithValue("@Amount", newAmount);
    command.Parameters.AddWithValue("@BookName", bookName);
    
    int rowsAffected = command.ExecuteNonQuery();
    if (rowsAffected == 0)
    {
        command.Parameters.Clear();
        command.CommandText = "INSERT INTO SoldProducts (BookName, Amount, Price) VALUES (@BookName, @Amount, @Price)";
        command.Parameters.AddWithValue("@BookName", bookName);
        command.Parameters.AddWithValue("@Amount", newAmount);
        command.Parameters.AddWithValue("@Price", price);
        command.ExecuteNonQuery();
    }
}