updating a database
-
Hello, I am developing a library system. I have 2 tables, book and student. The relationship is 1 student can have many books. I am having updating the book table when the student returns the book, the studentID (foreign key) will be cleared from the book table. I think this is the problem l am having. My code is listed below with the error description.
try { cnnReturnBook.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdb;Persist Security Info=False"; //Find the book that have to be returned OleDbCommand cmdReturnBook = cnnReturnBook.CreateCommand(); cmdReturnBook.CommandText = @"SELECT * FROM BOOK WHERE RefNumber = '" + txtBookRef.Text + "' "; OleDbDataAdapter daReturnBook = new OleDbDataAdapter(cmdReturnBook); OleDbCommandBuilder cbReturnBook = new OleDbCommandBuilder(daReturnBook); if ( daReturnBook.Fill(dtReturnBook) == 1 )//Book has been found, there should be only one 1 record in the datatable { //Clear the text boxes txtDateOut.Text = ""; txtDateDue.Text = ""; txtStatus.Text = "Available"; //Show that the book is now available txtIDNumber.Text = ""; //ID number of the student who has this book. txtName.Text = ""; txtSurname.Text = ""; //Insert into the datatable row the new values dtReturnBook.Rows[0]["Status"] = txtStatus.Text; dtReturnBook.Rows[0]["DateOut"] = txtDateOut.Text; dtReturnBook.Rows[0]["Remarks"] = txtRemarks.Text; dtReturnBook.Rows[0]["StudentID"] = txtIDNumber.Text; dtReturnBook.Rows[0]["DateDue"] = txtDateDue.Text; daReturnBook.Update(dtReturnBook);//Error here } } catch ( OleDbException ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK,MessageBoxIcon.Warning); } catch ( Exception ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { cnnReturnBook.Dispose(); } }Error description System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Student'. At System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) At System.Data.Common.DbDataAdapter.Update(DataTable dataTable) Thanks in advance, Steve -
Hello, I am developing a library system. I have 2 tables, book and student. The relationship is 1 student can have many books. I am having updating the book table when the student returns the book, the studentID (foreign key) will be cleared from the book table. I think this is the problem l am having. My code is listed below with the error description.
try { cnnReturnBook.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdb;Persist Security Info=False"; //Find the book that have to be returned OleDbCommand cmdReturnBook = cnnReturnBook.CreateCommand(); cmdReturnBook.CommandText = @"SELECT * FROM BOOK WHERE RefNumber = '" + txtBookRef.Text + "' "; OleDbDataAdapter daReturnBook = new OleDbDataAdapter(cmdReturnBook); OleDbCommandBuilder cbReturnBook = new OleDbCommandBuilder(daReturnBook); if ( daReturnBook.Fill(dtReturnBook) == 1 )//Book has been found, there should be only one 1 record in the datatable { //Clear the text boxes txtDateOut.Text = ""; txtDateDue.Text = ""; txtStatus.Text = "Available"; //Show that the book is now available txtIDNumber.Text = ""; //ID number of the student who has this book. txtName.Text = ""; txtSurname.Text = ""; //Insert into the datatable row the new values dtReturnBook.Rows[0]["Status"] = txtStatus.Text; dtReturnBook.Rows[0]["DateOut"] = txtDateOut.Text; dtReturnBook.Rows[0]["Remarks"] = txtRemarks.Text; dtReturnBook.Rows[0]["StudentID"] = txtIDNumber.Text; dtReturnBook.Rows[0]["DateDue"] = txtDateDue.Text; daReturnBook.Update(dtReturnBook);//Error here } } catch ( OleDbException ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK,MessageBoxIcon.Warning); } catch ( Exception ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { cnnReturnBook.Dispose(); } }Error description System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Student'. At System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) At System.Data.Common.DbDataAdapter.Update(DataTable dataTable) Thanks in advance, SteveI think that the problem is that you've entered the id of a student that doesn't exist in the Database. Either enter a corresponding student into the database or remove the constraint that there must be a student actually responsible for the loan. ;)
-
Hello, I am developing a library system. I have 2 tables, book and student. The relationship is 1 student can have many books. I am having updating the book table when the student returns the book, the studentID (foreign key) will be cleared from the book table. I think this is the problem l am having. My code is listed below with the error description.
try { cnnReturnBook.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdb;Persist Security Info=False"; //Find the book that have to be returned OleDbCommand cmdReturnBook = cnnReturnBook.CreateCommand(); cmdReturnBook.CommandText = @"SELECT * FROM BOOK WHERE RefNumber = '" + txtBookRef.Text + "' "; OleDbDataAdapter daReturnBook = new OleDbDataAdapter(cmdReturnBook); OleDbCommandBuilder cbReturnBook = new OleDbCommandBuilder(daReturnBook); if ( daReturnBook.Fill(dtReturnBook) == 1 )//Book has been found, there should be only one 1 record in the datatable { //Clear the text boxes txtDateOut.Text = ""; txtDateDue.Text = ""; txtStatus.Text = "Available"; //Show that the book is now available txtIDNumber.Text = ""; //ID number of the student who has this book. txtName.Text = ""; txtSurname.Text = ""; //Insert into the datatable row the new values dtReturnBook.Rows[0]["Status"] = txtStatus.Text; dtReturnBook.Rows[0]["DateOut"] = txtDateOut.Text; dtReturnBook.Rows[0]["Remarks"] = txtRemarks.Text; dtReturnBook.Rows[0]["StudentID"] = txtIDNumber.Text; dtReturnBook.Rows[0]["DateDue"] = txtDateDue.Text; daReturnBook.Update(dtReturnBook);//Error here } } catch ( OleDbException ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK,MessageBoxIcon.Warning); } catch ( Exception ex ) { MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { cnnReturnBook.Dispose(); } }Error description System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Student'. At System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) At System.Data.Common.DbDataAdapter.Update(DataTable dataTable) Thanks in advance, SteveThis should really be posted in the ADO.NET forum[^], but I'll try an answer it anyway. Without knowing more about your data model, I'm guessing that you have no student whose Id is an empty (zero length) string (This is not the same as NULL). And when you update the table the row is trying to relate itself to a student with an empty string ID. If my guess is correct: You should set
StudentIDin theBOOKtable to be nullable. If the student is to be cleared you should set the value of the column to beDBNull.Value.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
This should really be posted in the ADO.NET forum[^], but I'll try an answer it anyway. Without knowing more about your data model, I'm guessing that you have no student whose Id is an empty (zero length) string (This is not the same as NULL). And when you update the table the row is trying to relate itself to a student with an empty string ID. If my guess is correct: You should set
StudentIDin theBOOKtable to be nullable. If the student is to be cleared you should set the value of the column to beDBNull.Value.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
Hello Thanks for your help. I did what you asked, but l am still having the same problem. As i am using MS Access i can't set the StudentID in the book table to be Nullable. dtReturnBook.Rows[0]["StudentID"] = DBNull.Value; //Gave a compile error dtReturnBook.Rows[0]["StudentID"] = DBNull.Value.ToString(); //Gave the same error as the first problem. The problem remains, l need to clear the StudentID in the book table, as the student no longer has that book. Any other ways to solve this problem. Thanks in advance, Steve
-
Hello Thanks for your help. I did what you asked, but l am still having the same problem. As i am using MS Access i can't set the StudentID in the book table to be Nullable. dtReturnBook.Rows[0]["StudentID"] = DBNull.Value; //Gave a compile error dtReturnBook.Rows[0]["StudentID"] = DBNull.Value.ToString(); //Gave the same error as the first problem. The problem remains, l need to clear the StudentID in the book table, as the student no longer has that book. Any other ways to solve this problem. Thanks in advance, Steve
steve_rm wrote: As i am using MS Access i can't set the StudentID in the book table to be Nullable. I've not used Access in a few years, so I may be wrong, but you can set Required to No - which is the same as saying Nullable. steve_rm wrote: DBNull.Value; //Gave a compile error Not sure why that would be...
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.