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 :
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 :
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.