DataTable not filling correctly

[CEB3088]

Confused!!
I am trying to fill a DataTable with a DataSet using a SQL query with one parameter. The parameter is the Node text of a treeview whose checkbox has been checked. It will retrieve Products from one table and any associated Packages from a different table. I am using a FOR loop to get which Products are 'checked' in the treeview and re-assigning the parameter for each loop. When the DataTable is populated it is giving me the correct data for the first item but just the last line of data for all others.

ie: If there are 4 Products with 6 Packages each the DataTable will list all 6 packages for the first Product and then only Package 6 for all others. I thought it was an issue with constraints in my database but have determined that is not the case. My query works fine for any individual Product that is checked in the treeview and my parameter is being picked up correctly throughout the For loop. Any ideas why I am only getting the last Package for any checked Products beyond the first? Any ideas would be most appreciated.

Thanks

My Query and VB code follows:

--------------------------------------------------------------------------

SELECT Products.ProductNumber, Products.Name,
Products.Mnemonic, AddOnPackages.PackageNumber,
AddOnPackages.Name AS Expr1,
AddOnPackages.Mnemonic AS Expr2
FROM Products LEFT OUTER JOIN
AddOnPackages ON
Products.ProductNumber = AddOnPackages.ProductNumber
WHERE (Products.Name = @ProdName)
ORDER BY Products.ProductNumber, AddOnPackages.PackageNumber
--------------------------------------------------------------------------

Dim ProdName As String = ""

For Each node As TreeNode In TreeView1.Nodes

If node.Checked = True Then
ProdName = node.Text
ReportForm.ProductsTableAdapter.ClearBeforeFill = False

ReportForm.ProductsTableAdapter.Fill
(ReportForm.ProductInfo.Products, ProdName)

End If
Next

 

[cjard]

Run the SQL commands individually in a query analyzer; do you get the right results each time?

 

[CEB3088]

Thanks for the reply. For anyone else who might run into this and find this thread:

I just figured it out about a half hour ago but spent quite a bit of time trying different things. I did figure out that my query was working fine so why would the datatable not fill correctly? Apparently filling a datatable through a loop will not work correctly.

You must create a second datatable and merge the two at the end of each loop. The merge will add all information (depending on your database constraints) to the second table. In my case I needed to clear the constraints on my datatable since this was a read only query and I needed to get duplicate records of my ProductNumber. Setting ClearBeforeFill = True for the first datatable will ensure that all records in the child table of the query will load. That is where the fill was only including the last item of my child table after the first item.
Since I was using the table with a ReportViewer that I already had the first datatable attached to I simply cleared the first table after the loop and then merged the second into the first. The result was all child items where child items existed along with just the Products where no children existed.


Private Sub ProductInformationReportForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' Variable to hold the text of the current node of the treeview.
Dim ProdName As String = ""
' Create a second datatable to merge the items from the first datatable.
Dim dtHold As New DataTable

For Each Node As TreeNode In PrintProductInformationForm.tvwProducts.Nodes

If Node.Checked = True Then
' Assign the text of the current node to the parameter.
ProdName = Node.Text

' Clear the datatable before each loop in order to get all child items in the
' query if they exist.
Me.ProdInfoTableAdapter.ClearBeforeFill = True
' Since this is a read only query and I may need to list a product more than
' once if it has child products, clear the constraint on the Product Number.
Me.ProductInformation.ProdInfo.Constraints.Clear()
' Fill the datatable.
Me.ProdInfoTableAdapter.Fill(Me.ProductInformation.ProdInfo, ProdName)

' Merge the first table into the second. This will add all new records
' to the existing ones.
dtHold.Merge(Me.ProductInformation.ProdInfo)
End If
Next
' Clear the first table and then merge the second back into the first to load all
' the wanted records back into the first table.
Me.ProductInformation.ProdInfo.Clear()
Me.ProductInformation.ProdInfo.Merge(dtHold)

Me.ReportViewer1.RefreshReport()

End Sub

 

[cjard]

From where do you get the authority that filling in a loop will not work correctly? I fill in loops on a regular basis and I have not experienced this problem..

 

[CEB3088]

The problem that I was running into had to do with getting items from a child table. I am running the query based on checked items in a treeview. If the item was checked then the query would obtain the 'Product' based on the checked node text. It would also search a second table to see it any associated items(packages) were available. If so it would return all packages. The datatable would add all associated packages for the very first Product with associated packages. If there were any Products after that which had associated packages, only the last associated package would populate in the datatable. The only way around it that I could find was just what I suggested works. Running a query and filling the datatable based on one table only works like a charm, I agree. Have you been able to do what I was doing in a straight forward way?

 

[Tom]

You can call a single stored procedure to return multiple result sets and map them to as many tables as needed. For example, I pass a package number to the stored procedure and it returns the package record to the package datatable and also fills all the package item records to its child table.

 

[CEB3088]

Thanks for the reply Tom. That is what I would have expected and pretty much what I was doing. See my original post at the top. Why was that not working correctly? Again, I would get what I expected for the very first 'Product' that was queried. It would populate the 'Product' record and all 'Package' records which are coming from the child table. The very next 'Product' would only populate with the 'Product' and last 'Package' from the child table. Why is that? In order for me to get all records from the child table I need to clear out the datatable. Hence, going with the merge into a second datatable. What am I missing?

Thanks.