By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

Q: Adding a column to a dataset

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
Nov 20 '05 #1
Share this Question
Share on Google+
16 Replies


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

Nov 20 '05 #2

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


Nov 20 '05 #3

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



Nov 20 '05 #4

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



Nov 20 '05 #5

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
>
>



Nov 20 '05 #6

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
> >
> >
>
>



Nov 20 '05 #7

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
> > >
> > >
> >
> >
>
>



Nov 20 '05 #8

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>>
Nov 20 '05 #9

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
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #10

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>>

Nov 20 '05 #11

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>>


Nov 20 '05 #12

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>>


Nov 20 '05 #13

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
///
Nov 20 '05 #14

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>>



Nov 20 '05 #15

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>>
Nov 20 '05 #16

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>>

Nov 20 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.