1

I am using OleDB provider for Access database , developing in C# , VS2010

I have a Products table and Suppliers table

When I debug this code :

//open connection
con.Open();

//set command query for inserting a product
com2 = new OleDbCommand(@"INSERT INTO Products (ProductName,Model,Provider,Manufacturer,ReleasedDate,Quantity,SupplierID) 
VALUES (@name,@model,@provider,@manf,@date,@quantity,@supp)", con);

/* ADDING PARAMETERS TO THE COMMMAND */
com2.Parameters.AddWithValue("@name", textBox1.Text);
com2.Parameters.AddWithValue("@model", textBox2.Text);
com2.Parameters.AddWithValue("@provider", textBox4.Text);
com2.Parameters.AddWithValue("@manf", textBox5.Text);
com2.Parameters.AddWithValue("@date", DbType.DateTime).Value = dateTimePicker1.Value;
com2.Parameters.AddWithValue("@quantity", Convert.ToInt32(textBox6.Text));
//  com2.Parameters.AddWithValue("@price", Convert.ToInt32(textBox3.Text));

/* Getting the ID of the Supplier by his/her/it name and adding the value as a parameter */
com = new OleDbCommand("Select SupplierID from Suppliers WHERE SupplierName=@name",con);
com.Parameters.AddWithValue("@name",comboBox1.SelectedValue);
int str = (int)com.ExecuteScalar();
com2.Parameters.AddWithValue("@supp", str);

//INSERT EXECUTION
com2.ExecuteNonQuery();
MessageBox.Show("Product Added To Stock");
con.Close();

The program runs fine and executes the Query , but when i uncomment the line

com2.Parameters.AddWithValue("@price", Convert.ToInt32(textBox3.Text));

and Edit com2 Query string to : 

com2 = new OleDbCommand(@"INSERT INTO Products (ProductName,Model,Provider,Manufacturer,ReleasedDate,Quantity,SupplierID,Price) 
            VALUES (@name,@model,@provider,@manf,@date,@quantity,@supp,@price)", con);

The program crashes at line com2.ExecuteNonQuery(); with this error:

You cannot add or change a record because a related record is required in table 'Suppliers'.

Notes:

  1. if you're wondering why I wanted to add Price, it's because I didn't add it from the first place
  2. The Price column exists in the table Products
  3. I deleted all the records in both tables
  4. Price column type : Number - Long Integer
  5. SupplierID is a primary key in Suppliers table
  6. While debugging, str value was 4, which is the same value of one of the Suppliers
Improve this question
10
  • what is the column type? Commented May 28, 2014 at 15:23
  • @EhsanSajjad Number - Long Integer Commented May 28, 2014 at 15:23
  • 1
    I susupect the SupplierID is wrong, row with that Id does not exists in Supplier Table Commented May 28, 2014 at 15:24
  • Yes. Make sure the SupplierID exists in the Supplier table, because it is a foreign key, it appears. Commented May 28, 2014 at 15:26
  • @EhsanSajjad I debugged the program , and the str value is 4 , which is the ID of one of the Suppliers Commented May 28, 2014 at 15:26

3 Answers 3

Reset to default
1

Try moving this Line:

com2.Parameters.AddWithValue("@price", Convert.ToInt32(textBox3.Text));

After setting the @Supp parameter and see if the error persist

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

WOW ! didn't know Parameters addition order makes a difference
It seems like @S. Ahn was right. The order of the parameters does matters. Another posible solution is to move the block of code for getting supp before all the parameters addition.
The funny thing is that I was considering giving your answer, but I figured that having the OP change the query was easier than explaining where to move the code. Oh well, live and learn. :-)
0

The issue here is most likely due to the fact that the Supplier ID that you are providing as the value to your @sup parameter is not contained within the Suppliers table (foreign key constraint). Is it possible that the data you are pushing in has changed between the running of your query string without the price parameter and the running of your query string with the price parameter?

"You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to change or insert a record in the "many" table in a one-to-many relationship, and that record does not have a related record in the table on the "one" side. If you want to add or change the record, first add a record to the "one" table that contains the same value for the matching field." -- Microsoft Office Support

Improve this answer

1 Comment

S. Ahn makes a good point, the issue is that you are passing the value of the price variable to the supplier ID field in your query, which is going to result in a foreign key constraint error.
0

Change this line

com2 = new OleDbCommand(@"INSERT INTO Products (ProductName,Model,Provider,Manufacturer,ReleasedDate,Quantity,SupplierID,Price) 
        VALUES (@name,@model,@provider,@manf,@date,@quantity,@supp,@price)", con);

to

com2 = new OleDbCommand(@"INSERT INTO Products (ProductName,Model,Provider,Manufacturer,ReleasedDate,Quantity,Price,SupplierID) 
        VALUES (@name,@model,@provider,@manf,@date,@quantity,@price,@supp)", con);

You are sending the price as the @supp and the supp as the @price. Order matters. Other providers allow you the "bind by name," but not OleDb.

UPDATE: corrected query according to George T.

Improve this answer

10 Comments

This would un-mismatch the columns. In the code, the @supp parameter is last.
Same error , (And what are you trying to do ?) your are changing paramaters indexes ! that's not right
You are putting Price in SupplerID and supp to Price
@GabourX, I added more information to my answer. The names of the parameters are almost irrelevant. You need to preserve the order when you added the to Parameters.
@S.Ahn: You had the right idea but the query in your answer is wrong. You are correct that, with your change, the "supplier ID" value will be matched to "@supp". However, you only changed the order of "@supp" and "@price" and not the order of "SupplierID" and "Price" in the query. So SQL will match "@supp" to "Price".
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.