Query field values from SQL Server Compact

Question

(playerListTableAdapter.GetHeight(sortedPlayers[counter])

"Sorted Players" is an array of decimals, which match primary keys.

When I call the query GetHeight, I get the following error: "ConstraintException was unhandled: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

The SQL statement for the query is:

SELECT        Height
FROM            PlayerList
WHERE        (Number = @PlayerID)

Note: In the database, Number is a primary key.

When we step through the execution, we can see that sortedPlayers[counter] does match a player in the database.

Any ideas on what causes this error?

Thanks!

-Dominique

What's the value of `sortedPlayers[counter]`? – Leniel Maccaferri Aug 31 '12 at 16:02 — Leniel Maccaferri, Aug 31 '12 at 16:02

score 2 · Answer 1 · answered Aug 31 '12 at 16:02

2

You add your primary key in your query

SELECT       YourPrimaryKey, Height
FROM            PlayerList
WHERE        (Number = @PlayerID)

answered Aug 31 '12 at 16:02

Aghilas Yakoub

28,516
5
46
51

If I do that, won't it try to return the primary key as well as the height? (Number from the WHERE statement is the primary key) – Dominique Aug 31 '12 at 17:24
yes in order to localize your height target with your primary key, in order to modify good value – Aghilas Yakoub Aug 31 '12 at 17:26
To be clear, I'm just trying to read the Height, i don't want to modify it. – Dominique Aug 31 '12 at 18:25
but with your query if your don't set EnforceConstraints = false; you can't read – Aghilas Yakoub Aug 31 '12 at 18:27

score 2 · Answer 2 · answered Aug 31 '12 at 16:04

To solve this you can set EnforceConstraints to false on the DataTable.

EplayerListTableAdapter.EnforceConstraints = false;

A more detailed answer is here: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/c2c9158d-cddf-40cf-bf6c-794dc3ef9c7f/

score 2 · Accepted Answer · answered Sep 01 '12 at 03:06

After fighting to get the query working, I discovered that I could access the value more directly by using the following syntax:

decimal? Player1Hgt = dsPlayerTeam.PlayerList.Rows.Find(sortedPlayers[counter]).Field<decimal?>("Height");

This allowed me to go pull the value from the database without using the tableAdapter query and is significantly simpler.

Incidentally, I did attempt to set the EnforceConstraints property of my tableAdapter to false, but it still threw the error.

Thank You for your help!

Really Nice! Congrats for solving the problem in a simpler manner. — Leniel Maccaferri, Sep 01 '12 at 05:15

Query field values from SQL Server Compact

3 Answers3