Hello, I'm building an expenses application in asp.net and I'm attempting to insert some data in a SQL database in visul studio but I get the following error (screenshot here http://s13.postimg.org/4kjmn4nev/SQL_error.jpg):
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_expenses_ToexpenseCode". The conflict occurred in database "Applications", table "dbo.expenseCode", column 'Id'.
The statement has been terminated.
I've done a bit of googling and I've found something on stackoverflow saying that the error might happen because the table that contains the primary key needs to have data in before I insert data in the table with the foreign key...and that's exactly my situation, in that the table with the primary key (expenseCode) is empty.
So, as this is the first time I deal with primary and secondary keys, I thought it might be worth asking a few more questions.
First, here are the two tables:
expenseCode (primary key is id):
http://s11.postimg.org/6wdkcdlzn/expense_Code.jpg
and expenses (foreign key):
http://s12.postimg.org/b0mamknd9/expenses.jpg
Now, the first table expenseCode
is supposed to contain the id (1,2,3,4) and a string denoting the expense type (food, bills, rent, car); the expenses
table, among the other things, contains an int reference to that string stored in the other table (1 for rent, 2 for car, 3 for bills, 4 for food). So, if the solution to my problem is the above, how do I populate the first table? Should I hardcode the values (which to me seems the best solution)?
This is what I'm doing trying to populate expenses
:
protected void submitForm(object sender, EventArgs e){
//billValue.Text = "submitted";
hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
strInsert = "INSERT INTO expenses(Date,ExpenseId,Cost,Comment) VALUES (@date,@expenseid,@cost,@comment)";
sqlCmd = new SqlCommand(strInsert, hookUp);
sqlCmd.Parameters.Add("@date", DateTime.Now.ToString("MM/dd/yyyy"));
sqlCmd.Parameters.Add("@expenseid", expenseID);
sqlCmd.Parameters.Add("@cost", Convert.ToDecimal(billValue.Text));
sqlCmd.Parameters.Add("@comment", expenseComment.Value);
hookUp.Open();
sqlCmd.ExecuteNonQuery();
hookUp.Close();
initForm();//re Initialize form after submission
}
thanks