Checking if the record already exist in access datatable with C#

Question

0.00/5 (No votes)

See more:

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 15-Aug-21 5:27am

Abdelrahman-Dev

Updated 15-Aug-21 5:46am

Add a Solution

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

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 · 2021-08-15T05:46:00

Solution 1

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

Posted 15-Aug-21 5:46am

OriginalGriff

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?