Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
Hi, Thanks for viewing my question.

I have a button in my form which is responsible for inserting the values inside the textboxes into a datagridview, I have another button that takes all the rows inside the datagridview and inserts them to my access datatable and I basically have 3 columns in that datatable which are : (BookName, Amount and Price). This is my 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();


Now, this code works just fine with me but the problem is that I don't want to enter duplicate values in the "productname" column, but instead I want to check if the product already exists in the access datatable and if it doesn't I just want to insert it, but if it does exist I just want to add the "Amount" value in the datagridview to its alternative value in the access datatable.

What I have tried:

So someone on this website tried helping me by changing my query from what it is above to this :
C#
string cmdText2 = @"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 (BookName, Amount, Price) VALUES (@productName, @amount, @price)
END";


So I am updating but if there are no rows with that bookname i am gonna insert a new row.
So it is like updating if the insert fails.

But it throws an exception which is this :
Invalid SQL syntax - expected token: AS.


I hope you guys know how to solve it.
Thanks in advance.
Posted
Updated 16-Aug-21 1:44am
v3

1 solution

Access databases do not support stored procedures. You have to run the SELECT query in your code, analyze the return value from the query, then decide whether to execute the INSERT query.
   
Comments
Abdelrahman-Dev 17-Aug-21 15:21pm
   
Thanks, I will try this.
But why am I getting too many downvotes, why not someone like you just telling me that a stored procedure wouldn't work with Access
Dave Kreskowiak 17-Aug-21 16:52pm
   
Probably because nobody uses Access for anything because of its limitations.

Oh, and Access doesn't use named parameters like you have. They'll work, but the names don't mean anything. You have to specify the parameters in the order they appear in your SQL statement.

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