2

I'm trying to insert two data columns into my SQL Server database but I get an error at the code line -> cmd.ExecuteNonQuery();

Cannot insert the value NULL into column OrderID, table RestaurantApp.dbo.Junc_Order; column does not allow nulls. INSERT fails.

The OrderID column is actually the primary key in my data table. I set it identity(1, 1) and want to insert other data and meanwhile it can insert 1, 2, 3, 4....automatically.

Here is the part of my code:

string insertString = "INSERT INTO Junc_Order(ID, Quantity)values (@ID, @Quantity)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.Parameters.AddWithValue("@ID", r_ID);
cmd.Parameters.AddWithValue("@Quantity", r_Quantity);
cmd.ExecuteNonQuery();

I already get connection with database ahead of these codes, so the problem should not be that.

Updated Junc_Order table design:

OrderID (PK,FK,int,not null)

ID(FK,int,not null)

Quantity(int,not null)
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
  • 1
    Would be good if you can show the table design code as well. – Dhrumil Jun 15 '15 at 06:54
  • 2
    Are you _really_ sure your `OrderID` is idendity column? Did you check this column properties? – Soner Gönül Jun 15 '15 at 06:54
  • If you want identity to be automatically insertted then just dont insert it. Allow SQL Server to allot it. Just use `INSERT INTO Junc_Order(Quantity)values (@Quantity)";` – Amit Jun 15 '15 at 06:58
  • well, ID and OrderID are different column. This is actually a junction table. –  Jun 15 '15 at 07:12
  • I just updated my question and added table design. –  Jun 15 '15 at 07:21
  • If it is foreign key then you are inserting identity value before this statement. Catch it and pass it. How you are fetching r_id and how it is returned by SQL Server. – Amit Jun 15 '15 at 07:35
  • Thanks everyone, it turned out the problem is I cannot insert a foreign key, have to insert that column in another table as a primary key first. –  Jun 15 '15 at 08:16

1 Answers1

2

By viewing your question, it seems that your insert query is not correct:

  1. First of all, you don't need to insert "OrderID" as it is primary key identity so sql server automatically insert it.

  2. second, somewhere you are getting "r_ID" as null that's why you are facing error.Verify it and modify your code with the following:

    string insertString = "INSERT INTO Junc_Order(Quantity) values(@Quantity)"; SqlCommand cmd = new SqlCommand(insertString, conn); cmd.Parameters.AddWithValue("@Quantity", r_Quantity); cmd.ExecuteNonQuery();

Dev D
  • 225
  • 1
  • 13
  • Sorry again for the misunderstanding. This is a junction table, OrderID is the primary key and ID is another, which is a foreign key. r_id is actually the input data variable I name. –  Jun 15 '15 at 07:15
  • 1
    after seeing your last edit, it seems you did not created your "OrderID" as Identity. Please verify and get help from the below link to add an identity to existing column : http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – Dev D Jun 15 '15 at 07:28