Geoff,
ALTER TABLE ADD COLUMN MyTable FIRST id
then I'd be grateful if somebody could give me some example code (in VB) on
how this would work on a DataSet. (Again, I don't want to modify the
original file).
Again "ALTER TABLE" does not work with a DataSet!
Similarly, I'd like to add an index column to the second table. I would then
hope to be able to do a query on the DataSet (and I still don't know how to
do this on a dataset) something like:
SELECT tableA.id, tableB.id FROM tableA INNER JOIN tableB ON tableA.col1 =
tableB.col2
DataSets do not support Inner Joins. You need to define relationships
between the two DataTables, then you can use GetChildRows & GetParentRow &
GetParentRows to get the rows for a given relationship.
Alternatively you could use the JoinView sample custom DaveView class for
VB.NET.
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325682
A quick example of using the DataRelation, entirely in code (without adding
the key column).
Dim tableA As New DataTable("TableA")
With tableA.Columns
.Add("col1", GetType(Integer))
.Add("col2", GetType(Integer))
End With
With tableA.Rows
.Add(New Object() {23, 56})
.Add(New Object() {45, 87})
.Add(New Object() {21, 67})
.Add(New Object() {34, 9})
End With
Dim tableB As New DataTable("TableB")
With tableB.Columns
.Add("col1", GetType(Integer))
.Add("col2", GetType(Integer))
End With
With tableB.Rows
.Add(New Object() {56, 23})
.Add(New Object() {87, 45})
.Add(New Object() {67, 21})
.Add(New Object() {9, 34})
End With
Dim ds As New DataSet("Geoff")
ds.Tables.Add(tableA)
ds.Tables.Add(tableB)
' Start here if you read the DataSet from someplace else.
ds.Relations.Add("TableATableB", tableA.Columns("col1"),
tableB.Columns("col2"), False)
For Each row As DataRow In tableA.Rows
Debug.WriteLine(row!col1, "col1")
Debug.WriteLine(row!col2, "col2")
Debug.Indent()
For Each child As DataRow In row.GetChildRows("TableATableB")
Debug.WriteLine(child!col1, "col1")
Debug.WriteLine(child!col2, "col2")
Next
Debug.Unindent()
Debug.WriteLine(Nothing)
Next
Note the following line actually creates a many to many relationship, you
can use GetParentRows in this case.
ds.Relations.Add("TableATableB", tableA.Columns("col1"),
tableB.Columns("col2"), False)
For Each child As DataRow In tableB.Rows
Debug.WriteLine(child!col1, "child")
Debug.WriteLine(child!col2, "child")
Debug.Indent()
For Each parent As DataRow In
child.GetParentRows("TableATableB")
Debug.WriteLine(parent!col1, "parent")
Debug.WriteLine(parent!col2, "parent")
Next
Debug.Unindent()
Debug.WriteLine(Nothing)
Next
The False parameter in the Relations.Add prevents a Constraint from being
created, which means there could be duplicates in the parent's column.
Hope this helps
Jay
Geoff Jones said:
Many thanks again Jay - I'll try and get the books you recommended.
I don't seem to be able to find your "ALTER TABLE 'tablename' ADD
'columnName' 'type'" in the newsgroup. Strange! However, if it was
something like
ALTER TABLE ADD COLUMN MyTable FIRST id
then I'd be grateful if somebody could give me some example code (in VB) on
how this would work on a DataSet. (Again, I don't want to modify the
original file).
Indeed, it may a good point to explain why I'm trying to do all this (it may
make my insane requests a little more understandable - LOL!)
I actually have two tables, neither of which have unique index fields in
them. I've been able to connect to these tables using the standard
VB/ADO.NET methods i.e. connect, create data adaptor, data set etc. However,
and this is the problem, as I said earlier, each of the tables does not have
a unique index file. Suppose we have table one as such:
23 56
45 87
21 67
34 09
etc.
I'd like it to be
id col1 col2
1 23 56
2 45 87
3 21 67
4 34 09
etc.
Similarly, I'd like to add an index column to the second table. I would then
hope to be able to do a query on the DataSet (and I still don't know how to
do this on a dataset) something like:
SELECT tableA.id, tableB.id FROM tableA INNER JOIN tableB ON tableA.col1 =
tableB.col2
Thanks again for all your help.
Geoff
<<snip>>
