By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

Help filling dataset with one to many realtionship. HELP PLEASE REALLY URGENT!

P: n/a
Hi!

I have a query that INNER JOIN's three other tables. All the relation are 1
to 1, except for one, where an Item can have more than one price. I have
this query that work fine in MSDE Query Analyzer, but when I fill a dataset
with it, I get an error. (I attached a screenshot of it)
visual basic
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------
Nov 21 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
1) You didn't attach anything. But that's actually good, as many people
don't open attachments that come with newsgroup postings for security
reasons. You should tell us what the text of the message is instead.
2) With questions like this, you always need to post relevant snippets of
code.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hi!

I have a query that INNER JOIN's three other tables. All the relation are
1 to 1, except for one, where an Item can have more than one price. I have
this query that work fine in MSDE Query Analyzer, but when I fill a
dataset with it, I get an error. (I attached a screenshot of it)
visual basic
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------

Nov 21 '05 #2

P: n/a
ERROR MESSAGE== "Failed to enable constraints. One or more rows contain
values violating non-null, unique or foreign key constraints."
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------
the dataadapter is not generated at runtime. I have a sqldataadapter,
sqlconnection and dataset on my form, please, anyone help really
appreciated!!!!
Try

SqlDataAdapter1.Fill(DataSet11) <<<<<<<<ERROR ERROR ERROR
HERE!!!!!!!!!!!!!!!!!!!!

rptPlant.SetParameterValue("lang", preferenceSettingLanguage)

rptPlant.SetParameterValue("Company", preferenceCompanyName)

rptPlant.SetParameterValue("address", preferenceAddressBillingStreet)

rptPlant.SetDataSource(DataSet11)
Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

list.reportList.ReportSource = rptPlant

list.Show()

list.MdiParent = Me

End Try

"Marina" <so*****@nospam.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...
1) You didn't attach anything. But that's actually good, as many people
don't open attachments that come with newsgroup postings for security
reasons. You should tell us what the text of the message is instead.
2) With questions like this, you always need to post relevant snippets of
code.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hi!

I have a query that INNER JOIN's three other tables. All the relation are
1 to 1, except for one, where an Item can have more than one price. I
have
this query that work fine in MSDE Query Analyzer, but when I fill a
dataset with it, I get an error. (I attached a screenshot of it)
visual basic
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------




Nov 21 '05 #3

P: n/a
The data adapter is being generated at run time. Using the wizard just means
it generates the code for you - but it's the same code you could write as
well.

Since there is a bunch of generated code that we are not seeing, it's hard
to say. Odds are, this query is picking up a detail table row, that does not
have a master record in your dataset.

My advice would be to not use the wizard, and write all the data retrieval
code yourself. Then you are in a position where you know exactly what is
going on, exactly what code is running, etc.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:uM*************@tk2msftngp13.phx.gbl...
ERROR MESSAGE== "Failed to enable constraints. One or more rows contain
values violating non-null, unique or foreign key constraints."
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------
the dataadapter is not generated at runtime. I have a sqldataadapter,
sqlconnection and dataset on my form, please, anyone help really
appreciated!!!!
Try

SqlDataAdapter1.Fill(DataSet11) <<<<<<<<ERROR ERROR ERROR
HERE!!!!!!!!!!!!!!!!!!!!

rptPlant.SetParameterValue("lang", preferenceSettingLanguage)

rptPlant.SetParameterValue("Company", preferenceCompanyName)

rptPlant.SetParameterValue("address", preferenceAddressBillingStreet)

rptPlant.SetDataSource(DataSet11)
Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

list.reportList.ReportSource = rptPlant

list.Show()

list.MdiParent = Me

End Try

"Marina" <so*****@nospam.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...
1) You didn't attach anything. But that's actually good, as many people
don't open attachments that come with newsgroup postings for security
reasons. You should tell us what the text of the message is instead.
2) With questions like this, you always need to post relevant snippets of
code.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hi!

I have a query that INNER JOIN's three other tables. All the relation
are
1 to 1, except for one, where an Item can have more than one price. I
have
this query that work fine in MSDE Query Analyzer, but when I fill a
dataset with it, I get an error. (I attached a screenshot of it)
visual basic
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------



Nov 21 '05 #4

P: n/a
but if I right it myself, can I bind the dataset to a Crystal report
after????????

"Marina" <so*****@nospam.com> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...
The data adapter is being generated at run time. Using the wizard just
means it generates the code for you - but it's the same code you could
write as well.

Since there is a bunch of generated code that we are not seeing, it's hard
to say. Odds are, this query is picking up a detail table row, that does
not have a master record in your dataset.

My advice would be to not use the wizard, and write all the data retrieval
code yourself. Then you are in a position where you know exactly what is
going on, exactly what code is running, etc.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:uM*************@tk2msftngp13.phx.gbl...
ERROR MESSAGE== "Failed to enable constraints. One or more rows contain
values violating non-null, unique or foreign key constraints."
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr, tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------
the dataadapter is not generated at runtime. I have a sqldataadapter,
sqlconnection and dataset on my form, please, anyone help really
appreciated!!!!
Try

SqlDataAdapter1.Fill(DataSet11) <<<<<<<<ERROR ERROR ERROR
HERE!!!!!!!!!!!!!!!!!!!!

rptPlant.SetParameterValue("lang", preferenceSettingLanguage)

rptPlant.SetParameterValue("Company", preferenceCompanyName)

rptPlant.SetParameterValue("address", preferenceAddressBillingStreet)

rptPlant.SetDataSource(DataSet11)
Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

list.reportList.ReportSource = rptPlant

list.Show()

list.MdiParent = Me

End Try

"Marina" <so*****@nospam.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...
1) You didn't attach anything. But that's actually good, as many people
don't open attachments that come with newsgroup postings for security
reasons. You should tell us what the text of the message is instead.
2) With questions like this, you always need to post relevant snippets
of
code.

"Jean Christophe Avard" <NO***@M.BITCH> wrote in message
news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hi!

I have a query that INNER JOIN's three other tables. All the relation
are
1 to 1, except for one, where an Item can have more than one price. I
have
this query that work fine in MSDE Query Analyzer, but when I fill a
dataset with it, I get an error. (I attached a screenshot of it)
visual basic
code:------------------------------------------------------------------------------
SELECT tbl_items.latin_name, tbl_family.family_fr,
tbl_family.family_en,
tbl_price.format_fr, tbl_price.format_en, tbl_price.price,
tbl_price.profit_margin, tbl_items.common_name_fr,
tbl_items.common_name_en, tbl_guarantee.guarantee_fr,
tbl_guarantee.guarantee_en, tbl_family.id_family,
tbl_guarantee.id_guarantee, tbl_items.id_item FROM tbl_items INNER JOIN
tbl_family ON tbl_family.id_family = tbl_items.id_family INNER JOIN
tbl_guarantee ON tbl_items.id_guarantee = tbl_guarantee.id_guarantee
INNER JOIN tbl_price ON tbl_items.id_item = tbl_price.id_item WHERE
(tbl_items.hard_good = 0)
------------------------------------------------------------------------------



Nov 21 '05 #5

P: n/a
Set Property - Enforce Contraints = false on your dataset
Ken

Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.