CODEWITHSUNDEEP
c#asp.netdataset
sanjuro
sanjuro

Reputation: 1661

Dataset problem with constraints when selecting from more tables

So i am developing app according to official asp.net tutorials DAL Tutorial

So i created dataset connected to filebased sql database where are tables VIDEO, GENRE, LOAN! VIDEO has FK to GENRE and LOAN has FK to VIDEO! I tried to create method GetVideoByLoan with this sql command:

SELECT        VIDEO.ID_VIDEO, VIDEO.TITLE, VIDEO.DIRECTOR, VIDEO.YEAR, GENRE.TITLE AS GENRE_TITLE
FROM            VIDEO INNER JOIN
                     GENRE ON VIDEO.ID_GENRE = GENRE.ID_GENRE INNER JOIN
                     LOAN ON VIDEO.ID_VIDEO = LOAN.ID_VIDEO
WHERE (LOAN.USERID = @USERID OR @USERID = cast(cast(0 as binary) as uniqueidentifier) AND(LOAN.ISLOAN = @ISLOAN)

Everything is ok, no problem to return the results, but when i try this sql:

SELECT        LOAN.ID_LOAN, LOAN.USERID, LOAN.ID_VIDEO, VIDEO.TITLE, VIDEO.DIRECTOR, VIDEO.YEAR, GENRE.TITLE AS GENRE_TITLE
FROM            VIDEO INNER JOIN
                     GENRE ON VIDEO.ID_GENRE = GENRE.ID_GENRE INNER JOIN
                     LOAN ON VIDEO.ID_VIDEO = LOAN.ID_VIDEO
WHERE (LOAN.USERID = @USERID OR @USERID = cast(cast(0 as binary) as uniqueidentifier) AND(LOAN.ISLOAN = @ISLOAN)

i get error

Failed to enable constraints. One or more rows contain values violating non-null, unique, 
or foreign-key constraints. 
Description: An unhandled exception occurred during the execution of the current web request. 
Please   review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.ConstraintException: Failed to enable constraints. 
One or more rows contain values violating non-null, unique, or foreign-key constraints.

If i run above sql command in query editor everything is fine, but when i try to access it through dataset the above error apperas, despite that first sql was working. I also tried to remove VIDEO DataTable from DataSet and drop it there from server explorer again but it not helped. Do you know where can be the problem? Many thanks for answer.

Upvotes: 0

Views: 1792

Answers (2)

sanjuro
sanjuro

Reputation: 1661

i was trying to add to a datatable method with sql statement returning more columns than datatable had, solution was to create new datatableadapter with datable consisting of right columns. so it was my beginner mistake.

Upvotes: 1

indiPy
indiPy

Reputation: 8062

Check list for such problem,

  1. I suppose that your join returning more then one row for join where it should return only one.(though it is working correctly in sql.) Sometime result contain more then one row which contains same unique/primary key

  2. Check the datatype of SQL Table and Datatypes of Datatable, both should match.

Upvotes: 1

Related Questions