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

Q: Adding a primary key

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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
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

Nov 20 '05 #2

P: n/a
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
Nov 20 '05 #3

P: n/a
Hi Cor

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

Geoff

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:O$**************@TK2MSFTNGP12.phx.gbl...
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
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


Nov 20 '05 #4

P: n/a
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
Using this technique, would the rows already exisiting in the table have
values added to the new column?

Nov 20 '05 #5

P: n/a
Hi Richard

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

Geoff

"Richard Myers" <ri*********************@basd.co.nz> wrote in message
news:eL**************@TK2MSFTNGP09.phx.gbl...
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

Nov 20 '05 #6

P: n/a
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" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
Hi Richard

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

Geoff

"Richard Myers" <ri*********************@basd.co.nz> wrote in message
news:eL**************@TK2MSFTNGP09.phx.gbl...
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


Nov 20 '05 #7

P: n/a
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" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
Hi Richard

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

Geoff

"Richard Myers" <ri*********************@basd.co.nz> wrote in message
news:eL**************@TK2MSFTNGP09.phx.gbl...
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


Nov 20 '05 #8

P: n/a
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
Nov 20 '05 #9

P: n/a
Unreadable answer
This is not connected, to each other the database table has to have a
primary key and as well than a column.
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.
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

Nov 20 '05 #10

P: n/a
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" <no**********@planet.nl> wrote in message
news:uA****************@TK2MSFTNGP10.phx.gbl...
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

Nov 20 '05 #11

P: n/a
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" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
Hi Richard

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

Geoff

"Richard Myers" <ri*********************@basd.co.nz> wrote in message
news:eL**************@TK2MSFTNGP09.phx.gbl...
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



Nov 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.