Hi I'm a newbie from Delphi and I am trying to build a data application. I have set up my xsd dataset but I think I might be missing something in my relationship. In Delphi when you can set relation ships the same but the big difference is when the dataset goes to retrieve the data. In Delphi a child table only fetches data from the database that relates to the parent table and not the whole table as my dataset appears to be performing.
The only way I can seem to stop this is by limiting the query in my dataset to say in the child table only bring but what is in the parent table (customer is the parent table) ie.
mySqlCommand.CommandText =
"SELECT TOP 2 CustomerID, CompanyName " +
"FROM Customers " +
"ORDER BY CustomerID;" +
"SELECT OrderID, CustomerID " +
"FROM Orders " +
"WHERE CustomerID IN (" +
" SELECT TOP 2 CustomerID " +
" FROM Customers " +
" ORDER BY CustomerID" +
")";
If I try anything else then the correct relational data is displayed in the gridviews but the whole Order table is fetched from the database and filtered on my computer. Is there a property in the dataset I have missed to prevent the full fetch.
Cheers
Am I missing a seting in my relationship
DetBerMer
hi,
then i guess you have to deal with stored procedures , which i don't know much about it
http://www.vbdotnetheaven.com/Code/Jun2003/2102.asp
http://msdn2.microsoft.com/en-us/library/ms171918(VS.80).aspx
hope this helps
Sander3
hi,
i guess i'm lost here, you can't insert data in the child table that doesn't relate to the parent in first place , or other wise what is the use of the (relationships, foreign keys, primaykeys) so all the records in your child table are related to the parent table
unless you filtered the parent table but didn't filter the child table, so you have to filter the child table as well you can achive that by different ways first of all load the entire tables to the dataset and use bindingsourcs one bindingsource for each table, i assume you have a combobox to select from the parent table and you want the filter the child bindingsource according to that selection
1)set your parentbindingsource datasource = your dataset, datamemeber = your parent table
2) set the combobox datasource = your parentBindingSource
1) select the Childbindingsource which your datagrid is bound to and go to properties
2) go to datasource property and select your parentbindingsource (that combobox uses) as a darasource to your childbindingsource
3) in the datamember property select the relationship between the 2 tables
like that the records that will appear in the datagrid are those that related to your selection from combobox
you can also use your binding source to filter your table or you can use dataview without need to connect to your database every time , by using dataset and bindingsource you just connect once and deal with data in memory
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=317471&SiteID=1
hope this help
Amde
Thank shakalama
I'm not trying to update table only read from them. On my form I have 2 data grids and yes the second one is changing its relational records when I select each record in the first table. The thing I asking is to achieve this in the child tables query I said only include the records that relate to the first query. Don’t get me wrong it works if I leave this where cause out but it returns all the records to the clients computer then filters them out on the clients machine to display the right records in the datagrid (ie returns a much bigger record set).
Say, if the first query only returns 100 records(because a use a where clause) and in the database table there are 100000 million records in the child table but only 1000 relate to the above 100 parent records, then I only want the dataset to get the 1000 records from the database and not the 100000 records from the database and filter them to 1000.