0

I am using Visual Studio and have an Access database with a couple of tables that are related. They are using default drag/drop binding using WinForms to the dataset which was automatically generated as well.

I want the parent table to include a column with the count of the number of related records with that ID in the results. The tables look like this if it matters:

enter image description here

I added an additional query to the table adapter (as you can see from the screenshot) that does what it needs to do (without including the linked ID's) thinking that was how to do it. Am I at least on the right track? The query looks like this:

SELECT COUNT(*) AS WorkerCount
FROM (tblJobLaborTech INNER JOIN tblJobLabor ON tblJobLaborTech.JobLaborID = tblJobLabor.JobLaborID)
WHERE  (tblJobLabor.JobLaborID = 494)

And of course I need to change 494 to something variable which will match the corresponding record when it runs, but

WHERE (tblJobLabor.JobLaborID = tblJobLaborTech.tblJobLaborTechID)

doesnt seem to preview at all (no value given for parameter)

so i also tried

WHERE (tblJobLabor.JobLaborID = @JLID)

which also doesnt preview.

But that's only the start of the problem. Let's say I can get that part working, how do I get the result of that Scalar query to run for each record in the calling query and return in the dataset as if it were another field in that table?

braX
  • 11,506
  • 5
  • 20
  • 33

1 Answers1

1

You don't need to do this using the DB. A datatable can count the records present along a child relation

In your tblJobLabor add another datacolumn (right click, add>>column) and set its Expression property to:

Count(Child(XXX).JobLaborID)

Replace XXX with the name of this DataRelation I've highlighted:

enter image description here

More information can be found by looking at the fairly lengthy documentation on DataColumn.Expression at https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.8

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • I knew it had to be something simple like that... thanks a lot – braX Mar 15 '20 at 17:00
  • My apologies... quick follow up... the name of that relationship is `{8A0C0B82-4E50-40B2-8C22-8DAA393BFFA3}` (obviously not named by me) and it will not accept `Count(Child({8A0C0B82-4E50-40B2-8C22-8DAA393BFFA3}).JobLaborID)` as the expression. I must be doing something wrong? – braX Mar 15 '20 at 18:20
  • Well, I renamed it to `Test` and it gave me another error saying the other one was not found, so i replaced that copy buried in the `designer.vb` file, and then it worked. As a another test, I re-ran the wizard that sets up the xsd file, wondering if it would get changed back, and it did not get changed back. So I guess this works. I would still be curious as to why I am not able to use that weird long name it created if you know why. – braX Mar 15 '20 at 18:40
  • Possibly the { } characters - looks like the relationship was imported from access perhaps and named with a GUID. You could always delete it and make a new one (click in the grey area to the let of the parent primary key and drag drop onto the child foreign key - check that everything that appears in the dialog window is correct in terms of parent table, child table, parent column, child column etc.. and that the name of the relation is easy and sensible. Be aware that if you have forms that use parent/child bound data, changes to the relation might affect those too – Caius Jard Mar 15 '20 at 20:06
  • if you rename the relation the DataMember string of some BindingSOurce might not get updated – Caius Jard Mar 15 '20 at 20:06
  • OK. I'll keep my eye on it as I develop, as that was kinda what I was thinking would happen. I guess I got lucky so far. I appreciate the information. – braX Mar 16 '20 at 00:39