Querying a dataset

[kelmkone]

Hi, I'm looking for an easy way to query a table in a dataset( such as find smith) I was just wondering if anyone had any easy examples to follow, I’ve got a form which displays the table in the dataset and a subform which displays related records for each row in the table. I did try an example from a book which partially worked but an error message appears saying that the relationship between the tables can not be sustained
thanks

 

[Arg81]

Why don't you just use parameterised queries to your database?

I.E. SELECT * FROM USERS WHERE Surname = @Surname

Then on your form have a textbox, type a name and set this text to be the parameter value...your main grid will then fill with rows only that have a surname matching the entered text...

 

[kelmkone]

querying datasets

Cheers for that it seems to have simplified things but im still getting the problem with the relationships between the parent and child tables, it says that it's failed to enable constrains.

 

[cjard]

And what does the rest of the message say? Constraint Exceptions arise from more than just orphan records

 

[Arg81]

You will still get errors, my bad for not describing it fully.

You then need to use a parameter query on your child data. this is where it matches the parent row, so you need to load your child data by

SELECT * FROM Child_TABLE WHERE PriKey = @PriKey

Now, this can cause havok when you are browsing lots of parent rows. what I ended up doing was using recusion, and this was something that the All-Mighty (haha) Cjard helped me out with ages ago.

Here's an example;

Private Sub Form_Load (blah blah blah) ........

me.childDataTable.ClearBeforeFill = False
try
me.parent_tableadapter.fillBySurname(me.dataset1.parent, me.txtSurname.text)
me.LoadChildData()
catch ex as exception
messagebox.show(ex.message)
end try

End Sub

-----

Private Sub LoadChildData()

try
for each r as dataset1.parentRow in dataset1.parent
if r isnot nothing then
me.child_tableadapter.fill(me.dataset1.child, r.primary_key)
end if
next
catch ex as exception
messagebox.show(ex.message)
end try

End Sub

Now, that's done off the top of my head which tells you how many times I've done it as I can remember what it is...

What this does is load ALL parent rows that match the surname. It then goes through the child table and loads ONLY child rows who's FK match the PK of the parent row(s) that was loaded.

Although this may seem long winded, it's extremely useful when you need to load only a subset of data.
example: I load all records created by an account manager.

a) The account manager is selected and the parent table loads all rows which were created by that account manager.
b) Child data is loaded for all rows that exist in my parent datatable.

So instead of loading say 100 parent rows and 1000 child rows, I load 20 parent rows and 38 child rows....


However, as I mentioned at the start you can do it slightly different.

I.E.

Put a textbox, a label and a button on your form.

Set your parent data to load with @Surname, as you currently have done. On the button_click event, load this data.

Now, databind the label to the primary key of the parent data. Set up a query for your Child data to SELECT * FROM wherever WHERE yourFK = @yourFK (replace yourFK with correct column name)

Add to your button the fillByID query you just added, setting the parameter as the label.text
i.e.

private sub PushMe_click (.....)

me.ParentTableAdapter.FillBySurname(me.dataset.Parent, me.txtSurname.text)
me.childTableAdapter.FillByID(me.dataset.Child, me.lblID.text)

End Sub

That way, everytime you push the button, the parent and the child data for that parent row is loaded.

Hope that can help you along...