Click here to Skip to main content
15,355,296 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a button that is responsible for inserting the values inside the textboxes into a datagridview, and then another button responsible for inserting every row in that datagridview into the access datatable by looping through them and inserting them one by one, with this code :
C#
string cmdText2 = @"INSERT INTO SoldProducts (BookName, Amount, Price) 
            Values(@productname, @amount, @price)";

            connection.Open();
            OleDbCommand command2 = new OleDbCommand(cmdText2, connection);
            command2.Parameters.Add("@productname", OleDbType.VarWChar);
            command2.Parameters.Add("@amount", OleDbType.Integer);
            command2.Parameters.Add("@price", OleDbType.Double);            

            foreach (DataGridViewRow r in dataGridView1.Rows)
            {
                command2.Parameters["@amount"].Value = 
                Convert.ToInt32(r.Cells[1].Value);
                command2.Parameters["@productname"].Value = 
                Convert.ToString(r.Cells[0].Value);
                command2.Parameters["@price"].Value = 
                Convert.ToDouble(r.Cells[4].Value);
                command2.ExecuteNonQuery();

            }
            connection.Close();

And it works just fine but my problem is that I don't want to enter duplicate values so what I want to do is to check if the "productname" already exists in the access datatable, so if it doesn't exist just insert the values in a new record, but if it already exists then just add the "amount" of that product in the datagridview to the amount of the product in the datatable.
Thanks in advance.

What I have tried:

Didn't really find any tutorials that could help and my ideas didn't work either.
Posted
Updated 15-Aug-21 5:46am
Comments
PIEBALDconsult 15-Aug-21 13:36pm
   
Well, you should know which rows of the grid were filled from the "database" and which were not.

1 solution

The simplest solution is to do an UPDATE operation and then INSERT if it fails: I'd create a stored procedure called InsertOrUpdate and do it there:
SQL
CREATE PROCEDURE InsertOrUpdate
	@ProductName NVARCHAR(MAX), 
    @Amount INT, 
    @Price DECIMAL
AS
BEGIN
    UPDATE MyTable SET Amount = Amount + @Amount WHERE ProductName = @ProductName
    IF @@ROWCOUNT=0
        INSERT INTO MyTable (ProductName, Amount, Price) VALUES (@ProductName, @Amount, @Price)
END
   
Comments
OriginalGriff 15-Aug-21 12:31pm
   
How do you expect me to know that without seeing your whole query?
Abdelrahman-Dev 15-Aug-21 12:35pm
   
Sorry I just did something wrong in the query. Here is my whole query :
@"CREATE PROCEDURE InsertOrUpdate
@ProductName NVARCHAR(MAX),
@Amount INT,
@Price DECIMAL
AS
BEGIN
UPDATE SoldProducts SET Amount = Amount + @amount WHERE BookName = @ProductName
IF @@ROWCOUNT=0
INSERT INTO SoldProducts (ProductName, Amount, Price) VALUES (@productName, @amount, @price)
END"
And here is what the exception actually is :
Invalid SQL syntax - expected token: AS
Abdelrahman-Dev 15-Aug-21 14:01pm
   
So why does it throw that exception?

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