Question ConstraintException which I can't solve

[kfirba]

Hello!

I tried to do a simple Select query, but for some reason, when im trying to populate a table with the query result, I get this error:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The error line:

Using BorrowID As DataTable = BooksTableAdapter1.GetData_BorrowID_By_Given_BookID(bookID)

The full code:

Dim bookID As String       
 bookID = lstBooks.SelectedValue


Using BorrowID As DataTable = BooksTableAdapter1.GetData_BorrowID_By_Given_BookID(bookID)


            BorrowsTableAdapter1.Update_Borrow_End_To_False(BorrowID.Rows(0).Item(0).ToString)


        End Using
        BooksTableAdapter1.UpdateBookStatus(bookID, False)

I can't understand how it's even related to the constraint! It's a SELECT query, im not attempting to Update/Delete/Insert any information, just pull out filtered information!

Here's the query:

SELECT        Borrows.BorrowID
FROM            (Books INNER JOIN
                         Borrows ON Books.BookID = Borrows.BookID)
WHERE        (Books.BookID = @ BookID) AND (Books.BookBorrowed = True)

Thanks in advance!

 

[jmcilhinney]

The error message is telling you what the issue is. You are trying to insert data into the DataTable that violates one of those types of constraints. Obviously it's not a foreign key issue because it's a standalone DataTable. Presumably you're only getting a single record so it won't be anything to do with non-unique data.

Your code actually doesn't make sense You are calling a method on the BooksTableAdapter yet you're getting data from the Borrows table. The BooksTableAdapter is for Books data, not Borrows data. You're only retrieving a single value too, which means that every other field in the row will be empty, so I'll wager that your issue is related to null values for non-nullable columns. You shouldn't be populating a DataTable for a single value anyway. You should be using a scalar query and simply returning the value.

I strongly suggest that you do some reading on typed DataSets and TableAdapters because you are using them all wrong I'm afraid.

 

[kfirba]

I see, I should create the query accordin to its output columns.
about the typed dataset, yesterday I tried changing my syntax as you showed me in another thread, but it gave me an error.

What the scalar query mod does? It's going to return 1 row only? And if it is, can I store the result immediately in a DataRow?

I didn't really understand what you tried to explain about the null values and non-unique data, could you explain what you meant there

Thanks in advance!