Q: Adding a primary key

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

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

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

Geoff
 
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
 
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
 
Hi Richard

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

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

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.
 
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
 
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
 
Back
Top