Q: Adding a primary key

Hi

Can anybody help me with the following problem? I have a datasource i.e.
some data, which I'm accessing via some VB. This I have done; with the help
of you guys. However, the original data, and so the dataset I'm working
with, does not have a primary key column. I would obviously like to add one.
What is the easiest way to do this?

My first idea was to simply add a column, which as far as I can see will be
at the end of the existing columns of the dataset table, and then add unique
values to the column i.e. iterate through each row of the table and add a
vaule to the new column I have created.

Is there an easier or more efficient way of doing this?

Thanks in advance

Geoff

re: Q: Adding a primary key

Hi Geoff,

Yes have a look here (I assume you know how to create an primarykey column
using the object)

http://msdn.microsoft.com/library/de...entcolumns.asp

I hope this helps?

Cor
[color=blue]
> Can anybody help me with the following problem? I have a datasource i.e.
> some data, which I'm accessing via some VB. This I have done; with the[/color]
help[color=blue]
> of you guys. However, the original data, and so the dataset I'm working
> with, does not have a primary key column. I would obviously like to add[/color]
one.[color=blue]
> What is the easiest way to do this?
>
> My first idea was to simply add a column, which as far as I can see will[/color]
be[color=blue]
> at the end of the existing columns of the dataset table, and then add[/color]
unique[color=blue]
> values to the column i.e. iterate through each row of the table and add a
> vaule to the new column I have created.
>
> Is there an easier or more efficient way of doing this?
>
> Thanks in advance
>
> Geoff
>
>[/color]

re: Q: Adding a primary key

Hello

You would add an Autoincrementing column as per Cors link.
Then set this column as the primary key.

Dim myColArray(0) As DataColumn
myColArray(0) = workTable.Columns("MyAutoIncColumn")
workTable.PrimaryKey = myColArray

Then you feed your data into the datatable. The autoincrementing column will
obviously autoincrement such that no subsequent iteration is required.

hth
Richard

re: Q: Adding a primary key

Hi Cor

Using this technique, would the rows already exisiting in the table have
values added to the new column?

Geoff

"Cor Ligthert" <notfirstname@planet.nl> wrote in message
news:O$p86bkbEHA.3664@TK2MSFTNGP12.phx.gbl...[color=blue]
> Hi Geoff,
>
> Yes have a look here (I assume you know how to create an primarykey[/color]
column[color=blue]
> using the object)
>
>[/color]
http://msdn.microsoft.com/library/de...entcolumns.asp[color=blue]
>
> I hope this helps?
>
> Cor
>[color=green]
> > Can anybody help me with the following problem? I have a datasource i.e.
> > some data, which I'm accessing via some VB. This I have done; with the[/color]
> help[color=green]
> > of you guys. However, the original data, and so the dataset I'm working
> > with, does not have a primary key column. I would obviously like to add[/color]
> one.[color=green]
> > What is the easiest way to do this?
> >
> > My first idea was to simply add a column, which as far as I can see will[/color]
> be[color=green]
> > at the end of the existing columns of the dataset table, and then add[/color]
> unique[color=green]
> > values to the column i.e. iterate through each row of the table and add[/color][/color]
a[color=blue][color=green]
> > vaule to the new column I have created.
> >
> > Is there an easier or more efficient way of doing this?
> >
> > Thanks in advance
> >
> > Geoff
> >
> >[/color]
>
>[/color]

re: Q: Adding a primary key

Hi Geoff,

Normally you add rows to a datatable.

However when you have already an existing datatable, by instance in an XML
file dataset, than you have to add them yourself of course by instance in
the way you wrote, because you are not adding a row and therefore as well
not doing an autoincrement.

I hope this helps?

Cor
[color=blue]
> Using this technique, would the rows already exisiting in the table have
> values added to the new column?
>[/color]

re: Q: Adding a primary key

Hi Richard

As a matter of interest, what does the zero index i.e. (0), do in the
example you gave?

Geoff

"Richard Myers" <richard.spam-free.myers@basd.co.nz> wrote in message
news:eLuserkbEHA.1152@TK2MSFTNGP09.phx.gbl...[color=blue]
> Hello
>
> You would add an Autoincrementing column as per Cors link.
> Then set this column as the primary key.
>
> Dim myColArray(0) As DataColumn
> myColArray(0) = workTable.Columns("MyAutoIncColumn")
> workTable.PrimaryKey = myColArray
>
> Then you feed your data into the datatable. The autoincrementing column[/color]
will[color=blue]
> obviously autoincrement such that no subsequent iteration is required.
>
> hth
> Richard
>
>[/color]

re: Q: Adding a primary key

myColArray is an array of DataColumns. The PrimaryKey property expects an
array of datacolumns. Reason being, is that a primary key can be a
composite key, ie made up of more than one field.

In this instance, it happens to be just one field. Remember everything is
zero based.

Here is a example of two field composite key:

Dim myColArray(1) As DataColumn
myColArray(0) = workTable.Columns("Field1")
myColArray(1) = workTable.Columns("Field2")
workTable.PrimaryKey = myColArray

HTH,
Greg

"Geoff Jones" <geoff@NODAMNSPAM.com> wrote in message
news:40fd0695$0$303$cc9e4d1f@news.dial.pipex.com.. .[color=blue]
> Hi Richard
>
> As a matter of interest, what does the zero index i.e. (0), do in the
> example you gave?
>
> Geoff
>
> "Richard Myers" <richard.spam-free.myers@basd.co.nz> wrote in message
> news:eLuserkbEHA.1152@TK2MSFTNGP09.phx.gbl...[color=green]
> > Hello
> >
> > You would add an Autoincrementing column as per Cors link.
> > Then set this column as the primary key.
> >
> > Dim myColArray(0) As DataColumn
> > myColArray(0) = workTable.Columns("MyAutoIncColumn")
> > workTable.PrimaryKey = myColArray
> >
> > Then you feed your data into the datatable. The autoincrementing column[/color]
> will[color=green]
> > obviously autoincrement such that no subsequent iteration is required.
> >
> > hth
> > Richard
> >
> >[/color]
>
>[/color]

re: Q: Adding a primary key

Hi

I've added the new column and all appears well until I try to update the
datasource i.e. using

myDataAdaptor.Update(myDS)

When I do this, I get "Dynamic SQL generation for the UpdateCommand is not
supported against a selectCommand that does not return any key column
information"

Can anybody help? And more importantly, tell me the code I need to add the
new column to the datasource?

As Cor says, normally you add rows, not columns. Is what I'm trying to do
possible?

Thanks in advance

Geoff


"Geoff Jones" <geoff@NODAMNSPAM.com> wrote in message
news:40fd0695$0$303$cc9e4d1f@news.dial.pipex.com.. .[color=blue]
> Hi Richard
>
> As a matter of interest, what does the zero index i.e. (0), do in the
> example you gave?
>
> Geoff
>
> "Richard Myers" <richard.spam-free.myers@basd.co.nz> wrote in message
> news:eLuserkbEHA.1152@TK2MSFTNGP09.phx.gbl...[color=green]
> > Hello
> >
> > You would add an Autoincrementing column as per Cors link.
> > Then set this column as the primary key.
> >
> > Dim myColArray(0) As DataColumn
> > myColArray(0) = workTable.Columns("MyAutoIncColumn")
> > workTable.PrimaryKey = myColArray
> >
> > Then you feed your data into the datatable. The autoincrementing column[/color]
> will[color=green]
> > obviously autoincrement such that no subsequent iteration is required.
> >
> > hth
> > Richard
> >
> >[/color]
>
>[/color]

re: Q: Adding a primary key

Hi Geoff,

This is not connected, to each other the database table has to have a
primary key and as well than a column.

One of the things you can use which helps you maybe more than all we are
doing now is adding this row before your select.
da.MissingSchemaAction = MissingSchemaAction.AddWithKey

I hope this helps, otherwise reply?

Cor

re: Q: Adding a primary key

Unreadable answer[color=blue]
> This is not connected, to each other the database table has to have a
> primary key and as well than a column.[/color]

In my opinion is this error not direct connected to what we did before. I
was thinking about a not with a database connected dataset, that was the
reason I was writing about an XML file dataset. To update a database with a
dataset the primary key has to be in the same column as it is in the
database.

However I get the idea we can overcome all problems.
[color=blue]
> One of the things you can use which helps you maybe more than all we are
> doing now is adding this row before your select.
> da.MissingSchemaAction = MissingSchemaAction.AddWithKey
>
> I hope this helps, otherwise reply?
>
> Cor
>
>[/color]

re: Q: Adding a primary key

But according Geoff the original data doesn't have a primary key.

I am confused how he is going to update anything in this table without a
primary key.

Greg


"Cor Ligthert" <notfirstname@planet.nl> wrote in message
news:uA%232EzlbEHA.4092@TK2MSFTNGP10.phx.gbl...[color=blue]
> Hi Geoff,
>
> This is not connected, to each other the database table has to have a
> primary key and as well than a column.
>
> One of the things you can use which helps you maybe more than all we are
> doing now is adding this row before your select.
> da.MissingSchemaAction = MissingSchemaAction.AddWithKey
>
> I hope this helps, otherwise reply?
>
> Cor
>
>[/color]

re: Q: Adding a primary key

Geoff,
If your database itself does not have a Primary Key, you either need to add
a Primary Key to the database itself, or manually create the update
statements that are used.

Without a primary key in the database how is your program going to know that
row 1 in your datatable matches row 1 in the database? row 5 in your
datatable matches row 5 in the database? What happens if you delete row 4 in
the datatable?

Hope this helps
Jay

"Geoff Jones" <geoff@NODAMNSPAM.com> wrote in message
news:40fd1714$0$326$cc9e4d1f@news.dial.pipex.com.. .[color=blue]
> Hi
>
> I've added the new column and all appears well until I try to update the
> datasource i.e. using
>
> myDataAdaptor.Update(myDS)
>
> When I do this, I get "Dynamic SQL generation for the UpdateCommand is not
> supported against a selectCommand that does not return any key column
> information"
>
> Can anybody help? And more importantly, tell me the code I need to add the
> new column to the datasource?
>
> As Cor says, normally you add rows, not columns. Is what I'm trying to do
> possible?
>
> Thanks in advance
>
> Geoff
>
>
> "Geoff Jones" <geoff@NODAMNSPAM.com> wrote in message
> news:40fd0695$0$303$cc9e4d1f@news.dial.pipex.com.. .[color=green]
> > Hi Richard
> >
> > As a matter of interest, what does the zero index i.e. (0), do in the
> > example you gave?
> >
> > Geoff
> >
> > "Richard Myers" <richard.spam-free.myers@basd.co.nz> wrote in message
> > news:eLuserkbEHA.1152@TK2MSFTNGP09.phx.gbl...[color=darkred]
> > > Hello
> > >
> > > You would add an Autoincrementing column as per Cors link.
> > > Then set this column as the primary key.
> > >
> > > Dim myColArray(0) As DataColumn
> > > myColArray(0) = workTable.Columns("MyAutoIncColumn")
> > > workTable.PrimaryKey = myColArray
> > >
> > > Then you feed your data into the datatable. The autoincrementing[/color][/color][/color]
column[color=blue][color=green]
> > will[color=darkred]
> > > obviously autoincrement such that no subsequent iteration is required.
> > >
> > > hth
> > > Richard
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]