Question ForeignKeyConstraint error that I can't solve

[kfirba]

Hello!

As a practise, I started to build a MINI library application.

Finally, I have understood how to use queries and parameters.

My problem is, When i attempt to add a row into a table, I get the following error:

ForeignKeyConstraint CostumersBorrows requires the child key values (111111111) to exist in the parent table.

the code that i try to commit:

borrowerID = cboCostumers.SelectedValue        bookID = lstBooks.SelectedValue




        Dim BorrowDR As DataRow = borrowDS.Tables("Borrows").NewRow()
  


        BorrowDR.Item("CostumerID") = borrowerID
        BorrowDR.Item("BookID") = bookID
        BorrowDR.Item("BorrowDate") = Now()
        BorrowDR.Item("BorrowEnd") = False


        borrowDS.Tables("Borrows").Rows.Add(BorrowDR)


        Me.BorrowsTableAdapter1.Update(borrowDS.Tables("Borrows"))
        Me.BooksTableAdapter1.UpdateBookStatus(bookID)

The table structure is like that:

http://www.siz.co.il/my/0e0nzdqznwu3.jpg

The tables relations are Cascade because I need it like that

Thanks in advance!

 

[jmcilhinney]

That means that you are trying add a record to the child table, i.e. Borrows, with a foreign key value that doesn't exist in the parent table, i.e. Costumers.

 

[kfirba]

It's impossible :O!
The user can pick a costumerID from a list that executes all of the costumers from Costumers table, and he can pick books that has not been borrowed already, which means they are exist! that's wierd >.<

 

[jmcilhinney]

Exactly what line does that exception get thrown on? Is it the Add line or the Update line? I'm guessing that it's the Add line and what's happening is that you are not populating the Costumers DataTable in the same DataSet as the Borrows DataTable belongs to that you're adding the row to. If you want to add a row to a child table in a DataSet then you must have a corresponding parent row in the related table in the same DataSet.

 

[kfirba]

Yea, the error occurred on the add line. But, I'm using the same dataset for my whole program
evet form requests the dataset from the main form. I think that maybe the costumes and books that I'm adding to the database are not being saved in the Dataset? Is it possible?

 

[jmcilhinney]

It's possible but not really relevant to this specific issue. If the issue occurs on the Add line then all that matters is what's in the DataSet, not what's in the database. Either something on your system is broken or else, just as the error message says, there is no matching parent record. I suggest that you write some code at that point to prove one way or the other.

By the way, if you're going to use a typed DataSet then use it properly. Don't use it as though it's an untyped DataSet. That code in post #1 should be:

borrowerID = cboCostumers.SelectedValue
bookID = lstBooks.SelectedValue

Dim BorrowDR As DataRow = borrowDS.Borrows.NewBorrowsRow()

BorrowDR.CostumerID = borrowerID
BorrowDR.BookID = bookID
BorrowDR.BorrowDate = Date.Now
BorrowDR.BorrowEnd = False

borrowDS.Borrows.AddBorrowsRow(BorrowDR)

Me.BorrowsTableAdapter1.Update(borrowDS.Borrows)
Me.BooksTableAdapter1.UpdateBookStatus(bookID)

or something close to it.

 

[kfirba]

First time I hear about typed dataset and untapped dataset.
whats the different between them? And why they have different functions? Wow, that's confusing

 

[IanRyder]

Hi,

See if this helps:-

Typed Dataset - also known as a "Strongly Typed Dataset"
This is a type of Dataset that is created using the Visual Studio IDE which in turn creates explicit properties to define the Names and Data Types of each field within each table of the dataset. These DatsSets also have an .xsd (Xml Schema definition) file associated with them which can then be edited within the Visual Studio IDE.

The properties of tables can then be accessed in the following manner:- northwindDataSet.Products.ProductNameColumn

UnTyped Dataset - also known as a "Weakly Typed Dataset"
These type of DataSets are typically defined using code at runtime and only have table and column collections for their members with no definition of their data types. To therefore access a particular column in a particular table you need to specify the index of the table and column to be used and then do any special data conversions that you may need.

The properties of tables are therefore accessed in this manner:-
northwindDataSet.Tables("Products ").Columns("ProductNameColumn")

On the basis that you did not understand the difference earlier you will have no doubt set up a Typed Dataset with the Visual Studio IDE.

On another note after reading your post, you may just want to check that the ValueMember property of the cboCostumers ComboBox control is mapped to the correct CostmerID field in your data source. You will obviously get this error if it is not mapped to the field which is maintaining the relationship.

Hope that helps.

Cheers,

Ian

 

[kfirba]

IanRyder;

Thanks a lot!
so, which dataset is recommended? Which one is better? And why?

 

[IanRyder]

Hi,

They both have there uses, but for the moment, I would just stick with what you have got.

Cheers,

Ian

 

[kfirba]

IanRyder;

can you be more specific? Wha are their uses?

 

[IanRyder]

Hi,

Both types can be used to create any DataSet that you want to interact with in your project and there is no actual difference in what they do. The only difference is the way you interact with them as shown previously. When choosing to use one type over another type is more choice than anything else but if you want to classify this then you would typically use a Typed Dataset when you have a KNOWN and FIXED number of tables in a data source that you access all the time whereas you would use an UnTyped DataSet when you need to define and create access to different data tables in code depending on criteria gained at runtime.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

They're both DataSets so they have the same uses. A typed DataSet is just an extension of an untyped DataSet, which is why you were able to use yours as an untyped DataSet even though it was a typed DataSet.

The point of a typed DataSet is that it is specific to your data. That means that you have type safety and Intellisense at design time, making it easier to code and less likely that you'll make an error. For instance, look at this section of code you posted and what you should have used that I posted:

        BorrowDR.Item("CostumerID") = borrowerID
        BorrowDR.Item("BookID") = bookID
        BorrowDR.Item("BorrowDate") = Now()
        BorrowDR.Item("BorrowEnd") = False

BorrowDR.CostumerID = borrowerID
BorrowDR.BookID = bookID
BorrowDR.BorrowDate = Date.Now
BorrowDR.BorrowEnd = False

In your code, you're using Strings to identify the columns. You get no prompting from the IDE to help you determine which column name to use and you could use any String at all, whether a column with that name exists or not. Also, because each field is type Object, you can assign any object at all, even if it's not the correct type for that column. If you use an invalid column name or assign an invalid value, the compiler has no way of knowing so you will not find out until an exception is thrown at run time.

In my code, I'm using properties of the typed DataRow so I would be prompted by Intellisense with the available valid column names and I would not be allowed by the compiler to use an invalid column names. Each property is declared as the specific type of the corresponding column too, so there's no way that I could assign an invalid value.