Click here to Skip to main content
15,066,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 25-Aug-21 3:10am

1 solution

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();
    }
}
   
Comments
Abdelrahman-Dev 25-Aug-21 9:45am
   
Thanks, It worked but I changed the AddWithValue to Add, Is there a difference ?
Again thank you so much for the answer
Richard Deeming 25-Aug-21 9:48am
   
Yes, there is a difference: using Add[^] with an integer value which is 0 will call the wrong overload. If you use AddWithValue[^], there won't be any ambiguity.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900