Having real difficulty with datatable relation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there - this is driving me nuts...

I have a dataset that contains two datatables. One datatable
(SalesMainTable) contains the main crux of an SQL SPROC and I've had to
create another datatable (InvSalesTable) that is not the same shape or size
as the first that contains a reference that it has improved pretty impossible
to retrieve on my first SPROC (obviously mainly because it is a different
shape and size) - this I must get joined one way or another

I initially thought I had a unique field (INVOICE_NUMBER) in InvSalesTable
that I could create a relationship to SalesMainTable - which has an
INVOICE_NUMBER field which is definately not unique - and shift the required
field across from InvSalesTable to each row in SalesMainTable - but no, I've
since found out in a testing procedure that under certain circumstances this
field is not unique and this obviously upsets my ADO !

So - I have included a second field (ITEM_NUMBER) - which isn't really a
number incidentally (I didn't make the database !) - in InvSalesTable which
in conjunction with INVOICE_NUMBER becomes adequately unique and will enable
me to pull the correct field across - but how do I do this ???

I don't seem to be able to find any information or examples where more than
one field in a parent-child relationship can be used to create that
relationship - this is kind of irrelevant though as worse than that.... my
values are not unique on either side of the relationship anyway

I've started to have a look at something like:

Dim i As Integer
For i = 0 To SalesMainTable.Rows.Count - 1

'TRY AND RETURN THE SALEPERSON NAME FROM THE OTHER QUERY
If SalesMainTable.Rows(i).Item("SALES_NAME") = "NONE" Then
Dim selInvNumber As String =
SalesMainTable.Rows(i).Item("INVOICE_NUMBER")
Dim selItemNumber As String =
SalesMainTable.Rows(i).Item("ITEM_NUMBER")
SalesMainTable.Rows(i).Item("SALES_NAME") =
InvSalesTable.Select("(INVOICE_NUMBER = " & selInvNumber & " AND ITEM_NUMBER
= " & selItemNumber & ")")
End If

Next

I know the above is obviously wrong and probably rediculously expensive...
one of the things I am struggling with is how the SYNTAX works where I want
to retrieve field 1 from a datatable where field 2 = somthing and field 3 =
somethingelse

If anyone can help me with this I would really appreciate it - I've been
banging my head against the wall in SQL with this problem - thought I had it
sussed by moving the process in to the application instead - but I haven't
and I'm sure this is second nature to one of you kind fellows...

Thanks very much
 
Hi Stuart,

There are two ways to solve the problem.

1. In both queries for SalesMainTable and InvSalesTable
add a conjunction field, LTRIM(RTRIM(INVOICE_NUMBER)) +
LTRIM(RTRIM(ITEM_NUMBER)), and create parent-child
relationship based on the conjunction field in DataSet.

Or
2. Using code like:

Dim dv As DataView = InvSalesTable.DefaultView
For Each row As DataRow In SalesMainTable.Rows
dv.RowFilter = "INVOICE_NUMBER=" + row
("INVOICE_NUMBER") + " AND ITEM_NUMBER=" + row
("ITEM_NUMBER")
For Each rv As DataRowView In dv
' rv("FieldName")
' ...
Next
Next

HTH

Elton Wang
(e-mail address removed)
 
Back
Top