|
P: n/a
|
Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new column
to the beginning of the table
and to fill it with incremental values e.g. if the tables looks
like this:
23 56
45 87
21 67
34 09
I'd like it to be changed into:
1 23 56
2 45 87
3 21 67
4 34 09
In fact, what I'm trying to do is to give the table an index key in the
first column.
Can anybody help?
Thanks in advance
Geoff
| |
Share this Question
|
P: n/a
|
This will add a new column after creating two initial columns.The new column
will not be at ordinal 0, however, if you are using named columns as we are
here then it should not matter really.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim t As New DataTable("MyTable")
Dim c1 As New DataColumn("Name")
Dim c2 As New DataColumn("Address")
t.Columns.Add(c1)
t.Columns.Add(c2)
'Add Some Data
Dim r As DataRow = t.NewRow()
r("Name") = "Terry"
r("Address") = "The World"
t.Rows.Add(r)
'Add Some More
r = t.NewRow()
r("Name") = "Crystal Gayle"
r("Address") = "The World"
t.Rows.Add(r)
For Each r In t.Rows
Debug.WriteLine(r("Name") + " - " + r("Address"))
Next
'Add the new PK Column
t.Columns.Add("ID")
Dim i As Int32 = 0
For Each r In t.Rows
r("ID") = (i).ToString
Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
r("Address"))
i += 1
Next
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new
column to the beginning of the table
and to fill it with incremental values e.g. if the tables looks
like this:
23 56
45 87
21 67
34 09
I'd like it to be changed into:
1 23 56
2 45 87
3 21 67
4 34 09
In fact, what I'm trying to do is to give the table an index key in the
first column.
Can anybody help?
Thanks in advance
Geoff
| |
|
P: n/a
|
Hi Terry
Many thanks for the reply and the code.
Is there a way of doing this using an SQL command e.g. using ALTER?
Geoff
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:uz**************@TK2MSFTNGP09.phx.gbl... This will add a new column after creating two initial columns.The new
column will not be at ordinal 0, however, if you are using named columns as we
are here then it should not matter really.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim t As New DataTable("MyTable")
Dim c1 As New DataColumn("Name")
Dim c2 As New DataColumn("Address")
t.Columns.Add(c1)
t.Columns.Add(c2)
'Add Some Data
Dim r As DataRow = t.NewRow()
r("Name") = "Terry"
r("Address") = "The World"
t.Rows.Add(r)
'Add Some More
r = t.NewRow()
r("Name") = "Crystal Gayle"
r("Address") = "The World"
t.Rows.Add(r)
For Each r In t.Rows
Debug.WriteLine(r("Name") + " - " + r("Address"))
Next
'Add the new PK Column
t.Columns.Add("ID")
Dim i As Int32 = 0
For Each r In t.Rows
r("ID") = (i).ToString
Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
r("Address"))
i += 1
Next
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new
column to the beginning of the table
and to fill it with incremental values e.g. if the tables looks
like this:
23 56
45 87
21 67
34 09
I'd like it to be changed into:
1 23 56
2 45 87
3 21 67
4 34 09
In fact, what I'm trying to do is to give the table an index key in the
first column.
Can anybody help?
Thanks in advance
Geoff
| |
|
P: n/a
|
ALTER TABLE Sales ADD ID Integer;
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry
Many thanks for the reply and the code.
Is there a way of doing this using an SQL command e.g. using ALTER?
Geoff
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message news:uz**************@TK2MSFTNGP09.phx.gbl... This will add a new column after creating two initial columns.The new
column will not be at ordinal 0, however, if you are using named columns as we
are here then it should not matter really.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim t As New DataTable("MyTable")
Dim c1 As New DataColumn("Name")
Dim c2 As New DataColumn("Address")
t.Columns.Add(c1)
t.Columns.Add(c2)
'Add Some Data
Dim r As DataRow = t.NewRow()
r("Name") = "Terry"
r("Address") = "The World"
t.Rows.Add(r)
'Add Some More
r = t.NewRow()
r("Name") = "Crystal Gayle"
r("Address") = "The World"
t.Rows.Add(r)
For Each r In t.Rows
Debug.WriteLine(r("Name") + " - " + r("Address"))
Next
'Add the new PK Column
t.Columns.Add("ID")
Dim i As Int32 = 0
For Each r In t.Rows
r("ID") = (i).ToString
Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
r("Address"))
i += 1
Next
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new
column to the beginning of the table
and to fill it with incremental values e.g. if the tables looks
like this:
23 56
45 87
21 67
34 09
I'd like it to be changed into:
1 23 56
2 45 87
3 21 67
4 34 09
In fact, what I'm trying to do is to give the table an index key in
the first column.
Can anybody help?
Thanks in advance
Geoff
| |
|
P: n/a
|
Geoff,
Remember that SQL commands execute on the SQL Server itself. The Dataset
itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry
Many thanks for the reply and the code.
Is there a way of doing this using an SQL command e.g. using ALTER?
Geoff
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message news:uz**************@TK2MSFTNGP09.phx.gbl... This will add a new column after creating two initial columns.The new
column will not be at ordinal 0, however, if you are using named columns as we
are here then it should not matter really.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim t As New DataTable("MyTable")
Dim c1 As New DataColumn("Name")
Dim c2 As New DataColumn("Address")
t.Columns.Add(c1)
t.Columns.Add(c2)
'Add Some Data
Dim r As DataRow = t.NewRow()
r("Name") = "Terry"
r("Address") = "The World"
t.Rows.Add(r)
'Add Some More
r = t.NewRow()
r("Name") = "Crystal Gayle"
r("Address") = "The World"
t.Rows.Add(r)
For Each r In t.Rows
Debug.WriteLine(r("Name") + " - " + r("Address"))
Next
'Add the new PK Column
t.Columns.Add("ID")
Dim i As Int32 = 0
For Each r In t.Rows
r("ID") = (i).ToString
Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
r("Address"))
i += 1
Next
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new
column to the beginning of the table
and to fill it with incremental values e.g. if the tables looks
like this:
23 56
45 87
21 67
34 09
I'd like it to be changed into:
1 23 56
2 45 87
3 21 67
4 34 09
In fact, what I'm trying to do is to give the table an index key in
the first column.
Can anybody help?
Thanks in advance
Geoff
| |
|
P: n/a
|
Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so still
feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the column I
require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an
SQL command to the DataSet) which holds the table. However, and you may want
to correct me on this, I don't think this is possible. From what I have
read, you can only issue an SQL command to populate the DataSet via the
DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original line of
thought i.e. somehow copy the table that I have loaded from file and then
add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ek**************@TK2MSFTNGP10.phx.gbl... Geoff,
Remember that SQL commands execute on the SQL Server itself. The Dataset
itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry
Many thanks for the reply and the code.
Is there a way of doing this using an SQL command e.g. using ALTER?
Geoff
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message news:uz**************@TK2MSFTNGP09.phx.gbl... This will add a new column after creating two initial columns.The new
column will not be at ordinal 0, however, if you are using named columns as
we are here then it should not matter really.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim t As New DataTable("MyTable")
Dim c1 As New DataColumn("Name")
Dim c2 As New DataColumn("Address")
t.Columns.Add(c1)
t.Columns.Add(c2)
'Add Some Data
Dim r As DataRow = t.NewRow()
r("Name") = "Terry"
r("Address") = "The World"
t.Rows.Add(r)
'Add Some More
r = t.NewRow()
r("Name") = "Crystal Gayle"
r("Address") = "The World"
t.Rows.Add(r)
For Each r In t.Rows
Debug.WriteLine(r("Name") + " - " + r("Address"))
Next
'Add the new PK Column
t.Columns.Add("ID")
Dim i As Int32 = 0
For Each r In t.Rows
r("ID") = (i).ToString
Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
r("Address"))
i += 1
Next
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. ..
> Hi
>
> Can anybody help me with the following, hopefully simple, question?
>
> I have a table which I've connected to a dataset. I wish to add a
new column
> to the beginning of the table
> and to fill it with incremental values e.g. if the tables looks
> like this:
>
> 23 56
> 45 87
> 21 67
> 34 09
>
> I'd like it to be changed into:
>
> 1 23 56
> 2 45 87
> 3 21 67
> 4 34 09
>
> In fact, what I'm trying to do is to give the table an index key in
the > first column.
>
> Can anybody help?
>
> Thanks in advance
>
> Geoff
>
>
| |
|
P: n/a
|
Didnt you try my ALTER TABLE 'tablename' ADD 'columnName' 'type', it does
work !
????
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so still
feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the column I
require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an
SQL command to the DataSet) which holds the table. However, and you may
want to correct me on this, I don't think this is possible. From what I have
read, you can only issue an SQL command to populate the DataSet via the
DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original line of
thought i.e. somehow copy the table that I have loaded from file and then
add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ek**************@TK2MSFTNGP10.phx.gbl... Geoff,
Remember that SQL commands execute on the SQL Server itself. The Dataset
itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry
Many thanks for the reply and the code.
Is there a way of doing this using an SQL command e.g. using ALTER?
Geoff
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message news:uz**************@TK2MSFTNGP09.phx.gbl...
> This will add a new column after creating two initial columns.The
new column
> will not be at ordinal 0, however, if you are using named columns as we are
> here then it should not matter really.
>
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles Button1.Click
> Dim t As New DataTable("MyTable")
> Dim c1 As New DataColumn("Name")
> Dim c2 As New DataColumn("Address")
>
> t.Columns.Add(c1)
> t.Columns.Add(c2)
>
> 'Add Some Data
> Dim r As DataRow = t.NewRow()
> r("Name") = "Terry"
> r("Address") = "The World"
> t.Rows.Add(r)
>
> 'Add Some More
> r = t.NewRow()
> r("Name") = "Crystal Gayle"
> r("Address") = "The World"
> t.Rows.Add(r)
>
> For Each r In t.Rows
> Debug.WriteLine(r("Name") + " - " + r("Address"))
> Next
>
> 'Add the new PK Column
> t.Columns.Add("ID")
> Dim i As Int32 = 0
> For Each r In t.Rows
> r("ID") = (i).ToString
> Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
> r("Address"))
> i += 1
> Next
>
> --
>
> OHM ( Terry Burns )
> . . . One-Handed-Man . . .
>
>
> "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> news:40**********************@news.dial.pipex.com. ..
> > Hi
> >
> > Can anybody help me with the following, hopefully simple,
question? > >
> > I have a table which I've connected to a dataset. I wish to add a new > column
> > to the beginning of the table
> > and to fill it with incremental values e.g. if the tables looks
> > like this:
> >
> > 23 56
> > 45 87
> > 21 67
> > 34 09
> >
> > I'd like it to be changed into:
> >
> > 1 23 56
> > 2 45 87
> > 3 21 67
> > 4 34 09
> >
> > In fact, what I'm trying to do is to give the table an index key
in the > > first column.
> >
> > Can anybody help?
> >
> > Thanks in advance
> >
> > Geoff
> >
> >
>
>
| |
|
P: n/a
|
Terry, ????
I think we confused Geoff...
At least I am confused on what Geoff really wants.
Jay
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl... Didnt you try my ALTER TABLE 'tablename' ADD 'columnName' 'type', it does
work !
????
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so still
feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the column
I require by using ALTER COLUMN etc. on either the DataAdaptor (or issue
an SQL command to the DataSet) which holds the table. However, and you may
want to correct me on this, I don't think this is possible. From what I have
read, you can only issue an SQL command to populate the DataSet via the
DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original line
of thought i.e. somehow copy the table that I have loaded from file and
then add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:ek**************@TK2MSFTNGP10.phx.gbl... Geoff,
Remember that SQL commands execute on the SQL Server itself. The
Dataset itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. ..
> Hi Terry
>
> Many thanks for the reply and the code.
>
> Is there a way of doing this using an SQL command e.g. using ALTER?
>
> Geoff
>
> "One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message
> news:uz**************@TK2MSFTNGP09.phx.gbl...
> > This will add a new column after creating two initial columns.The new > column
> > will not be at ordinal 0, however, if you are using named columns
as we > are
> > here then it should not matter really.
> >
> >
> > Private Sub Button1_Click(ByVal sender As System.Object, ByVal
e As > > System.EventArgs) Handles Button1.Click
> > Dim t As New DataTable("MyTable")
> > Dim c1 As New DataColumn("Name")
> > Dim c2 As New DataColumn("Address")
> >
> > t.Columns.Add(c1)
> > t.Columns.Add(c2)
> >
> > 'Add Some Data
> > Dim r As DataRow = t.NewRow()
> > r("Name") = "Terry"
> > r("Address") = "The World"
> > t.Rows.Add(r)
> >
> > 'Add Some More
> > r = t.NewRow()
> > r("Name") = "Crystal Gayle"
> > r("Address") = "The World"
> > t.Rows.Add(r)
> >
> > For Each r In t.Rows
> > Debug.WriteLine(r("Name") + " - " + r("Address"))
> > Next
> >
> > 'Add the new PK Column
> > t.Columns.Add("ID")
> > Dim i As Int32 = 0
> > For Each r In t.Rows
> > r("ID") = (i).ToString
> > Debug.WriteLine(r("ID") + " - " + r("Name") + " - " +
> > r("Address"))
> > i += 1
> > Next
> >
> > --
> >
> > OHM ( Terry Burns )
> > . . . One-Handed-Man . . .
> >
> >
> > "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> > news:40**********************@news.dial.pipex.com. ..
> > > Hi
> > >
> > > Can anybody help me with the following, hopefully simple, question? > > >
> > > I have a table which I've connected to a dataset. I wish to add
a new > > column
> > > to the beginning of the table
> > > and to fill it with incremental values e.g. if the tables looks
> > > like this:
> > >
> > > 23 56
> > > 45 87
> > > 21 67
> > > 34 09
> > >
> > > I'd like it to be changed into:
> > >
> > > 1 23 56
> > > 2 45 87
> > > 3 21 67
> > > 4 34 09
> > >
> > > In fact, what I'm trying to do is to give the table an index key
in the
> > > first column.
> > >
> > > Can anybody help?
> > >
> > > Thanks in advance
> > >
> > > Geoff
> > >
> > >
> >
> >
>
>
| |
|
P: n/a
|
Geoff,
Let's back up a step.
Do you want to change just the DataSet?
Or do you want to change the SQL Table itself?
A DataAdapter is used to copy (adapt) data to & from your SQL Table into a
Dataset/DataTable. It can implicitly create the structure of the
DataSet/DataTable when you do the Fill, or you can explicitly create the
structure with the code Terry gave.
A Data Command is used to execute commands against your SQL Table (such as
ALTER COLUMN, ALTER TABLE). A DataAdapter can have upto 4 Data Commands, for
Select, Delete, Insert & Update.
So you can use an SQL command to modify the SQL Table, which will implicitly
change your DataSet the next time you do a Fill.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so still
feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the column I
require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an
SQL command to the DataSet) which holds the table. However, and you may
want to correct me on this, I don't think this is possible. From what I have
read, you can only issue an SQL command to populate the DataSet via the
DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original line of
thought i.e. somehow copy the table that I have loaded from file and then
add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ek**************@TK2MSFTNGP10.phx.gbl... Geoff,
Remember that SQL commands execute on the SQL Server itself. The Dataset
itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
<<snip>> | |
|
P: n/a
|
I think all he wanted was an SQL query to Add a column which I have given
him. But that was not really clear in the OP, thats why I posted the code.
Anyway, he should be able to do this either way now.
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eP**************@TK2MSFTNGP09.phx.gbl... Terry, ????
I think we confused Geoff...
At least I am confused on what Geoff really wants.
Jay
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in
message news:u3**************@TK2MSFTNGP09.phx.gbl... Didnt you try my ALTER TABLE 'tablename' ADD 'columnName' 'type', it
does work !
????
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so
still feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the
column I require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an SQL command to the DataSet) which holds the table. However, and you
may want to correct me on this, I don't think this is possible. From what I
have read, you can only issue an SQL command to populate the DataSet via
the DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original
line of thought i.e. somehow copy the table that I have loaded from file and then add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:ek**************@TK2MSFTNGP10.phx.gbl...
> Geoff,
> Remember that SQL commands execute on the SQL Server itself. The Dataset > itself (and the code Terry gave) is executed within your program.
>
> Hope this helps
> Jay
>
> "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> news:40**********************@news.dial.pipex.com. ..
> > Hi Terry
> >
> > Many thanks for the reply and the code.
> >
> > Is there a way of doing this using an SQL command e.g. using
ALTER? > >
> > Geoff
> >
> > "One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote
in > message
> > news:uz**************@TK2MSFTNGP09.phx.gbl...
> > > This will add a new column after creating two initial
columns.The new > > column
> > > will not be at ordinal 0, however, if you are using named
columns as we
> > are
> > > here then it should not matter really.
> > >
> > >
> > > Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As
> > > System.EventArgs) Handles Button1.Click
> > > Dim t As New DataTable("MyTable")
> > > Dim c1 As New DataColumn("Name")
> > > Dim c2 As New DataColumn("Address")
> > >
> > > t.Columns.Add(c1)
> > > t.Columns.Add(c2)
> > >
> > > 'Add Some Data
> > > Dim r As DataRow = t.NewRow()
> > > r("Name") = "Terry"
> > > r("Address") = "The World"
> > > t.Rows.Add(r)
> > >
> > > 'Add Some More
> > > r = t.NewRow()
> > > r("Name") = "Crystal Gayle"
> > > r("Address") = "The World"
> > > t.Rows.Add(r)
> > >
> > > For Each r In t.Rows
> > > Debug.WriteLine(r("Name") + " - " + r("Address"))
> > > Next
> > >
> > > 'Add the new PK Column
> > > t.Columns.Add("ID")
> > > Dim i As Int32 = 0
> > > For Each r In t.Rows
> > > r("ID") = (i).ToString
> > > Debug.WriteLine(r("ID") + " - " + r("Name") + " - "
+ > > > r("Address"))
> > > i += 1
> > > Next
> > >
> > > --
> > >
> > > OHM ( Terry Burns )
> > > . . . One-Handed-Man . . .
> > >
> > >
> > > "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> > > news:40**********************@news.dial.pipex.com. ..
> > > > Hi
> > > >
> > > > Can anybody help me with the following, hopefully simple, question? > > > >
> > > > I have a table which I've connected to a dataset. I wish to
add a new
> > > column
> > > > to the beginning of the table
> > > > and to fill it with incremental values e.g. if the tables
looks > > > > like this:
> > > >
> > > > 23 56
> > > > 45 87
> > > > 21 67
> > > > 34 09
> > > >
> > > > I'd like it to be changed into:
> > > >
> > > > 1 23 56
> > > > 2 45 87
> > > > 3 21 67
> > > > 4 34 09
> > > >
> > > > In fact, what I'm trying to do is to give the table an index
key in > the
> > > > first column.
> > > >
> > > > Can anybody help?
> > > >
> > > > Thanks in advance
> > > >
> > > > Geoff
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
|
P: n/a
|
Thanks for your continuing help. Maybe I should explain exactly what I'm
doing:
I don't want to modify the original table so I guess I'm looking to change
the DataSet i.e. my understanding is that the DataAdaptor can be used as a
wall between the original table and the table I want to work on.
Terry's code did indeed work however did you send one set of code or two? I
can't find the "ALTER TABLE 'tablename' ADD 'columnName' 'type'" code you
referred to. I'm assuming you also sent a solution using SQL. Which message
is it in?
If I understand you correctly, am I right in thinking that I can only use
SQL commands on the original table and not on the generated DataSet? If so,
this puzzles me. I would of thought it very useful to be able to do such
things.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:u7****************@tk2msftngp13.phx.gbl... Geoff,
Let's back up a step.
Do you want to change just the DataSet?
Or do you want to change the SQL Table itself?
A DataAdapter is used to copy (adapt) data to & from your SQL Table into a
Dataset/DataTable. It can implicitly create the structure of the
DataSet/DataTable when you do the Fill, or you can explicitly create the
structure with the code Terry gave.
A Data Command is used to execute commands against your SQL Table (such as
ALTER COLUMN, ALTER TABLE). A DataAdapter can have upto 4 Data Commands,
for Select, Delete, Insert & Update.
So you can use an SQL command to modify the SQL Table, which will
implicitly change your DataSet the next time you do a Fill.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so still
feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the column
I require by using ALTER COLUMN etc. on either the DataAdaptor (or issue
an SQL command to the DataSet) which holds the table. However, and you may
want to correct me on this, I don't think this is possible. From what I have
read, you can only issue an SQL command to populate the DataSet via the
DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original line
of thought i.e. somehow copy the table that I have loaded from file and
then add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:ek**************@TK2MSFTNGP10.phx.gbl... Geoff,
Remember that SQL commands execute on the SQL Server itself. The
Dataset itself (and the code Terry gave) is executed within your program.
Hope this helps
Jay
<<snip>>
| |
|
P: n/a
|
P.S. So, in a nutshell, I'm trying to find out if I can issue SQL commands
directly to a DataSet?
Geoff
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Thanks for your continuing help. Maybe I should explain exactly what I'm
doing:
I don't want to modify the original table so I guess I'm looking to change
the DataSet i.e. my understanding is that the DataAdaptor can be used as a
wall between the original table and the table I want to work on.
Terry's code did indeed work however did you send one set of code or two?
I can't find the "ALTER TABLE 'tablename' ADD 'columnName' 'type'" code you
referred to. I'm assuming you also sent a solution using SQL. Which
message is it in?
If I understand you correctly, am I right in thinking that I can only use
SQL commands on the original table and not on the generated DataSet? If
so, this puzzles me. I would of thought it very useful to be able to do such
things.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:u7****************@tk2msftngp13.phx.gbl... Geoff,
Let's back up a step.
Do you want to change just the DataSet?
Or do you want to change the SQL Table itself?
A DataAdapter is used to copy (adapt) data to & from your SQL Table into
a Dataset/DataTable. It can implicitly create the structure of the
DataSet/DataTable when you do the Fill, or you can explicitly create the
structure with the code Terry gave.
A Data Command is used to execute commands against your SQL Table (such
as ALTER COLUMN, ALTER TABLE). A DataAdapter can have upto 4 Data Commands, for Select, Delete, Insert & Update.
So you can use an SQL command to modify the SQL Table, which will
implicitly change your DataSet the next time you do a Fill.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so
still feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the
column I require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an SQL command to the DataSet) which holds the table. However, and you
may want to correct me on this, I don't think this is possible. From what I
have read, you can only issue an SQL command to populate the DataSet via
the DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original
line of thought i.e. somehow copy the table that I have loaded from file and then add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:ek**************@TK2MSFTNGP10.phx.gbl...
> Geoff,
> Remember that SQL commands execute on the SQL Server itself. The Dataset > itself (and the code Terry gave) is executed within your program.
>
> Hope this helps
> Jay
>
<<snip>>
| |
|
P: n/a
|
Geoff,
I did not send any code, I only offered a warning about Terry's ALTER Table
sample...
Terry's ALTER table sample was immediately preceding my warning. If I understand you correctly, am I right in thinking that I can only use
SQL commands on the original table and not on the generated DataSet? If
so, this puzzles me. I would of thought it very useful to be able to do such
things.
A DataSet is a set of objects that represents a set of Data, it is not a
mini-SQL engine. To use SQL Commands you would need a mini-SQL engine.
The expressions used in the DataSet object model resemble SQL expressions,
such as DataTable.Compute, DataTable.Select, DataView.RowFilter, however
they are not complete SQL statements.
I do not consider DataAdapters & SQL Command objects part of the DataSet
object model per se... However DataAdapters, SQL Command objects, and the
DataSet object model are part of ADO.NET as a whole.
For details on this and other exciting questions on ADO.NET (Datasets) I
would recommend Sceppa's book, which is a good tutorial on ADO.NET as well
as a good desk reference once you know ADO.NET. David Sceppa's book is
"Microsoft ADO.NET - Core Reference" from MS press.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Thanks for your continuing help. Maybe I should explain exactly what I'm
doing:
I don't want to modify the original table so I guess I'm looking to change
the DataSet i.e. my understanding is that the DataAdaptor can be used as a
wall between the original table and the table I want to work on.
Terry's code did indeed work however did you send one set of code or two?
I can't find the "ALTER TABLE 'tablename' ADD 'columnName' 'type'" code you
referred to. I'm assuming you also sent a solution using SQL. Which
message is it in?
If I understand you correctly, am I right in thinking that I can only use
SQL commands on the original table and not on the generated DataSet? If
so, this puzzles me. I would of thought it very useful to be able to do such
things.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:u7****************@tk2msftngp13.phx.gbl... Geoff,
Let's back up a step.
Do you want to change just the DataSet?
Or do you want to change the SQL Table itself?
A DataAdapter is used to copy (adapt) data to & from your SQL Table into
a Dataset/DataTable. It can implicitly create the structure of the
DataSet/DataTable when you do the Fill, or you can explicitly create the
structure with the code Terry gave.
A Data Command is used to execute commands against your SQL Table (such
as ALTER COLUMN, ALTER TABLE). A DataAdapter can have upto 4 Data Commands, for Select, Delete, Insert & Update.
So you can use an SQL command to modify the SQL Table, which will
implicitly change your DataSet the next time you do a Fill.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Hi Terry/Jay
You'll have to forgive my ignorance guys. New to all this stuff so
still feeling my way I'm afraid.
Thanks to both of you I think I may be now close to a solution. I'd
originally thought that I could execute an SQL command to add the
column I require by using ALTER COLUMN etc. on either the DataAdaptor (or issue an SQL command to the DataSet) which holds the table. However, and you
may want to correct me on this, I don't think this is possible. From what I
have read, you can only issue an SQL command to populate the DataSet via
the DataAdaptor.
So, in a nutshell, it looks like I have to follow Terry's original
line of thought i.e. somehow copy the table that I have loaded from file and then add an extra column to it (in the process of creating the table).
I'll give it a go anyway.
Thanks again for your help.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:ek**************@TK2MSFTNGP10.phx.gbl...
> Geoff,
> Remember that SQL commands execute on the SQL Server itself. The Dataset > itself (and the code Terry gave) is executed within your program.
>
> Hope this helps
> Jay
>
<<snip>>
| |
|
P: n/a
|
Hi Geoff,
I saw so many messages about this in this newsgroup and the ADONET newsgroup
that I could not resist to make as well a sample.
I do also not know any solution creating that extra row when filing the
dataset with the dataadapter, however that much work is it not to do it in
the routine I show you in the button event.
The reason I did not made it earlier is that I do not know a method as a
Column.insertAt to place the column as the first one and was hoping to see a
solution for that here.
Here the sample I hope it gives you some more ideas in addition to Terry's.
Cor
\\\
'sample a form with 3 textboxes a button and a listbox
'with two tabpages
'on tabpage 1 a textbox and a button
'on tabpage 2 a listbox
Private cma As CurrencyManager
Dim dt As DataTable
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
dt = New DataTable
dt.Columns.Add("B")
dt.Columns.Add("C")
For i As Integer = 0 To 1
dt.Rows.Add(dt.NewRow)
Next
dt.Rows(0)(0) = "23"
dt.Rows(1)(0) = "45"
dt.Rows(0)(1) = "56"
dt.Rows(1)(1) = "87"
'This before only to make a starting table without reading
cma = CType(BindingContext(dt), CurrencyManager)
Me.TextBox2.DataBindings.Add("text", dt, "B")
Me.TextBox3.DataBindings.Add("text", dt, "C")
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
dt.Columns.Add("A")
For i As Integer = 0 To dt.Rows.Count - 1
dt.Rows(i)("A") = (i + 1).ToString
Next
Me.TextBox1.DataBindings.Add("text", dt, "A")
Me.ListBox1.DataSource = dt
Me.ListBox1.DisplayMember = "A"
End Sub
///
| |
|
P: n/a
|
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
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Ow***************@tk2msftngp13.phx.gbl... Geoff,
I did not send any code, I only offered a warning about Terry's ALTER
Table sample...
Terry's ALTER table sample was immediately preceding my warning.
If I understand you correctly, am I right in thinking that I can only
use SQL commands on the original table and not on the generated DataSet? If so, this puzzles me. I would of thought it very useful to be able to do such
things.
A DataSet is a set of objects that represents a set of Data, it is not a
mini-SQL engine. To use SQL Commands you would need a mini-SQL engine.
The expressions used in the DataSet object model resemble SQL expressions,
such as DataTable.Compute, DataTable.Select, DataView.RowFilter, however
they are not complete SQL statements.
I do not consider DataAdapters & SQL Command objects part of the DataSet
object model per se... However DataAdapters, SQL Command objects, and the
DataSet object model are part of ADO.NET as a whole.
For details on this and other exciting questions on ADO.NET (Datasets) I
would recommend Sceppa's book, which is a good tutorial on ADO.NET as well
as a good desk reference once you know ADO.NET. David Sceppa's book is
"Microsoft ADO.NET - Core Reference" from MS press.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. Thanks for your continuing help. Maybe I should explain exactly what I'm
doing:
I don't want to modify the original table so I guess I'm looking to
change the DataSet i.e. my understanding is that the DataAdaptor can be used as
a wall between the original table and the table I want to work on.
Terry's code did indeed work however did you send one set of code or
two? I can't find the "ALTER TABLE 'tablename' ADD 'columnName' 'type'" code
you referred to. I'm assuming you also sent a solution using SQL. Which
message is it in?
If I understand you correctly, am I right in thinking that I can only
use SQL commands on the original table and not on the generated DataSet? If
so, this puzzles me. I would of thought it very useful to be able to do such
things.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:u7****************@tk2msftngp13.phx.gbl... Geoff,
Let's back up a step.
Do you want to change just the DataSet?
Or do you want to change the SQL Table itself?
A DataAdapter is used to copy (adapt) data to & from your SQL Table
into a Dataset/DataTable. It can implicitly create the structure of the
DataSet/DataTable when you do the Fill, or you can explicitly create
the structure with the code Terry gave.
A Data Command is used to execute commands against your SQL Table
(such as ALTER COLUMN, ALTER TABLE). A DataAdapter can have upto 4 Data
Commands, for Select, Delete, Insert & Update.
So you can use an SQL command to modify the SQL Table, which will
implicitly change your DataSet the next time you do a Fill.
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. ..
> Hi Terry/Jay
>
> You'll have to forgive my ignorance guys. New to all this stuff so still > feeling my way I'm afraid.
>
> Thanks to both of you I think I may be now close to a solution. I'd
> originally thought that I could execute an SQL command to add the column I > require by using ALTER COLUMN etc. on either the DataAdaptor (or
issue an > SQL command to the DataSet) which holds the table. However, and you
may want
> to correct me on this, I don't think this is possible. From what I have > read, you can only issue an SQL command to populate the DataSet via the > DataAdaptor.
>
> So, in a nutshell, it looks like I have to follow Terry's original
line of > thought i.e. somehow copy the table that I have loaded from file and
then > add an extra column to it (in the process of creating the table).
>
> I'll give it a go anyway.
>
> Thanks again for your help.
>
> Geoff
>
> "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message > news:ek**************@TK2MSFTNGP10.phx.gbl...
> > Geoff,
> > Remember that SQL commands execute on the SQL Server itself. The
Dataset > > itself (and the code Terry gave) is executed within your program.
> >
> > Hope this helps
> > Jay
> >
<<snip>>
| |
|
P: n/a
|
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...en-us%3B325682
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" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. 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>> | |
|
P: n/a
|
Many, many thanks for your help.
I've ordered the book you recommended on Amazon last night - once it arrives
let the learning commence!!!
Thanks again
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eG**************@TK2MSFTNGP09.phx.gbl... 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...en-us%3B325682
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" <ge***@NODAMNSPAM.com> wrote in message
news:40**********************@news.dial.pipex.com. .. 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>>
| | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 1906
- replies: 16
- date asked: Nov 20 '05
|
