Bug or own fault in ADO.NET

  • Thread starter Thread starter Christian Findt
  • Start date Start date
C

Christian Findt

Hi there,

I'm trying to load two tables in one dataset and connecting them with a
relation. Unfortunately, the result isn't as expected. Instead to load both
tables correctly, the field "KdNr" in the child table stays empty. In the
end, the function "GetChildRows" cannot find anything.

Well, the question is why? Is it my fault which would be my guess or is it a
know bug in the system. I was working close on examples I found on numerous
sites but I'm not sure how I transformed them. I would appreciate any kind
of help on this one.

I'm working with a local Access database.

These are my Select-Strings: (shortend)
----------------------------------------------------------------------------

myOrdersSel = "SELECT Orders.Nr, Orders.ODate, Orders.InDate, Orders.KdNr,
[...], Orders.MdValDat, Customers.KdNr," & _

" Customers.KdName, Customers.KdTyp FROM Orders
INNER JOIN Customers ON Orders.KdNr = Customers.KdNr;"

myCustSel = "SELECT Customers.KdNr, Customers.KdAnrede, Customers.KdTitel,
Customers.KdName, Customers.KdVorn," & _

[...] "Customers.KdTyp, Customers.KdBriefanr FROM
Customers;"

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


The Code:
----------------------------------------------------------------------------


daC = New OleDb.OleDbDataAdapter(myCustSel, conn)

daO = New OleDb.OleDbDataAdapter(myOrdersSel, conn)

conn.Open()

If f = False Then

daO.Fill(dsO, "Orders")

Else

daC.Fill(dsO, "Customers")

daO.Fill(dsO, "Orders")

dr = New DataRelation("RelCO", dsO.Tables("Customers").Columns("KdNr"),
dsO.Tables("Orders").Columns("KdNr"))

dsO.Relations.Add(dr)

Dim rC, rO As DataRow

For Each rC In dsO.Tables("Customers").Rows

Console.WriteLine(rC("KdNr") & " - " & rC("KdName"))

For Each rO In rC.GetChildRows("RelCO")

Console.WriteLine(vbTab & rO("Nr") & " - " & rO("KdNr"))
'No result because field Orders.KdNr is empty.

Next

Next

End If

conn.Close()

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

The result:
----------------------------------------------------------------------------

50075 - Kram

13469 - Albin

55408 - Freytag *without child info!

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

checking the field directly:
Console.WriteLine(rO("Nr") & " - " & rO("KdNr")):
----------------------------------------------------------------------------

3288 -

3300 -

3284 - *KdNr is empty
 
Hi Hardip,

thx for your help but unfortunately, it didn't work. Still the same result.

Christian :)
 
Here is an example with NorthWind that works. Looks similar to yours and
does work. Maybe its the data in the data set thats not right?


SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
SqlConnection1 = New System.Data.SqlClient.SqlConnection
SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
SqlSelectCommand2.CommandText = "SELECT * FROM Customers" + vbCrLf + "SELECT
* FROM ORders"
SqlSelectCommand2.Connection = Me.SqlConnection1
SqlConnection1.ConnectionString = "Some connection string"
Dim DS As DataSet = New DataSet
SqlDataAdapter2.Fill(DS)
Dim DR As DataRelation = New DataRelation("CustOrder",
DS.Tables(0).Columns("CustomerID"), _
DS.Tables(1).Columns("CustomerID"))
DS.Relations.Add(DR)
For Each DR2 As DataRow In DS.Tables(0).Rows
For Each DR3 As DataRow In DR2.GetChildRows("CustOrder")
Console.WriteLine(DR3.Item("OrderId"))
Next
Next
 
Thanks, I'll work on this thought.

Christian :)

JD said:
Here is an example with NorthWind that works. Looks similar to yours and
does work. Maybe its the data in the data set thats not right?


SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
SqlConnection1 = New System.Data.SqlClient.SqlConnection
SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
SqlSelectCommand2.CommandText = "SELECT * FROM Customers" + vbCrLf + "SELECT
* FROM ORders"
SqlSelectCommand2.Connection = Me.SqlConnection1
SqlConnection1.ConnectionString = "Some connection string"
Dim DS As DataSet = New DataSet
SqlDataAdapter2.Fill(DS)
Dim DR As DataRelation = New DataRelation("CustOrder",
DS.Tables(0).Columns("CustomerID"), _
DS.Tables(1).Columns("CustomerID"))
DS.Relations.Add(DR)
For Each DR2 As DataRow In DS.Tables(0).Rows
For Each DR3 As DataRow In DR2.GetChildRows("CustOrder")
Console.WriteLine(DR3.Item("OrderId"))
Next
Next
Click to expand...
 
Back
Top