[RESOLVED] [2005] - Dataset and datatables

[mickey_pt]

Hello

I have another problem now, i have a db in sqlserver, i have some tables one (let's call it table1) is related with other three, the relations are defined by the id_something, now i have to display the data from table1....

I have Something like this
Table1
Field 1 - 1 (id from Table 2)
Filed 2 - 2 (id from Table 3)
Field 3 - 1 (id from Table 4)

I want to replace the id's by some field of the related table...

How can i do this?

Btw i'm using the table adapter fill method...

[VBDT]

is the Table1 a child table or it is the parent?

[mickey_pt]

Child....

[jmcilhinney]

There are two ways to approach this. If you just want to display the data to the user then you can execute a single query that joins the parent and child tables. For instance, if your Parent table has ID and Name columns and your Child table has ID, ParentID and Name columns then your query would look like this:

SELECT Child.ID, Parent.Name AS Parent, Child.Name FROM Parent INNER JOIN Child ON Parent.ID = Child.ParentID

Note that you can build that query visually using the Query Builder.

If you want the user to be able to edit the parent record for the child records then you can't do it that way. You would need two separate DataTables. For instance, you could use a DataGridView with a combo box column for the ParentID. You'd then bind the Child table to the grid itself and the Parent table to the combo box column. The user could then select a parent by name and have the corresponding ParentID automatically set in the child record.

[mickey_pt]

Thks for the answer but were i put the select query?

I'm using a fill method with some parameters...

[VBDT]

I think you designed your relation not correctly. I mean it is possible for a table to have two or more child tables but I don’t think it is possible to have relation where two parent tables have the same child table. The reason is that if you select a record in one of the two parents then the child table should show the records for that particular parent-record but the second parent has also a record selected so which records should the child table show. It is conflicting, it is like in the math (a function can point two different functions but two functions can’t point to one, it is not one to one – note it is not a function).

[mickey_pt]

sorry but maybe i don't understand the first question...

my tables are something like this
Supplier (id, name, ...)
User (id, login, ...)
product (id, name, ...)

and the table that connect the tables
product_supplier (id_ps, id_sup,id_user, id_prod, date, ...)

[VBDT]

What i mean is that logically the Supplier table has nothing to do with the User table so there should not be any relation between them.

[mickey_pt]

and there isn't...

the point is that i have a user that receive products from one suplier

[jmcilhinney]

Open your DataSet in the designer and then you can right click to add tables, table adapters, queries, etc.

[mickey_pt]

Yes i know that but what i'm trying to say i don't know if i had a query like that to the query that perform the fill? or create a new query?

My current fill method (PS tableadapter) is like this:
SELECT n_ps, id_p, id_sup, quant , date_ps
FROM PS
WHERE (id_user = @id_util)

[jmcilhinney]

I've told you that you can either perform one query that includes a join, or two queries. If you have two tables then you already have two queries. Unless you've specifically created a query with a join then you don't have one. Also, I gave you two choices and you haven't indicated which one you want/need to use. We can't advise you how to proceed if we don't know what you need to achieve.

[mickey_pt]

sorry :S

I'm talking about the first choice, i only have to show the info so i don't have any kind of join... i drag the table form the data source to the form to create a new datagrid, and what i want it's to replace every id number with some field in the related table...

I don't know if u understand what i'm trying to do... Maybe my english isn't the best... Sorry

[jmcilhinney]

You don't have a table that contains both parent and child data so displaying a single table in a grid won't cut it unless you add a new table to your DataSet.

If you want to do this without new tables then I think that this should work. Once you've got your child data bound to the grid edit the columns and change the parent ID column to a combo box. You can now bind the parent table to that column. If you set the properties of the column correctly then the user will never see the combo box and not be able to edit the column values. That means assigning the parent table to the DataSource property, the appropriate columns of the parent table to the DisplayMember and ValueMember properties, the ReadOnly property to True and the DisplayStyle to Nothing.

[mickey_pt]

thks once again

i'll try this later!