473,836 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 20 '05 #1
11 2625
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
Hello

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

Dim myColArray(0) As DataColumn
myColArray(0) = workTable.Colum ns("MyAutoIncCo lumn")
workTable.Prima ryKey = myColArray

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

hth
Richard
Nov 20 '05 #3
Hi Cor

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

Geoff

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:O$******** ******@TK2MSFTN GP12.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
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
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******** ******@TK2MSFTN GP09.phx.gbl...
Hello

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

Dim myColArray(0) As DataColumn
myColArray(0) = workTable.Colum ns("MyAutoIncCo lumn")
workTable.Prima ryKey = myColArray

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

hth
Richard

Nov 20 '05 #6
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.Colum ns("Field1")
myColArray(1) = workTable.Colum ns("Field2")
workTable.Prima ryKey = myColArray

HTH,
Greg

"Geoff Jones" <ge***@NODAMNSP AM.com> wrote in message
news:40******** *************@n ews.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******** ******@TK2MSFTN GP09.phx.gbl...
Hello

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

Dim myColArray(0) As DataColumn
myColArray(0) = workTable.Colum ns("MyAutoIncCo lumn")
workTable.Prima ryKey = myColArray

Then you feed your data into the datatable. The autoincrementin g column

will
obviously autoincrement such that no subsequent iteration is required.

hth
Richard


Nov 20 '05 #7
Hi

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

myDataAdaptor.U pdate(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***@NODAMNSP AM.com> wrote in message
news:40******** *************@n ews.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******** ******@TK2MSFTN GP09.phx.gbl...
Hello

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

Dim myColArray(0) As DataColumn
myColArray(0) = workTable.Colum ns("MyAutoIncCo lumn")
workTable.Prima ryKey = myColArray

Then you feed your data into the datatable. The autoincrementin g column

will
obviously autoincrement such that no subsequent iteration is required.

hth
Richard


Nov 20 '05 #8
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.MissingSchem aAction = MissingSchemaAc tion.AddWithKey

I hope this helps, otherwise reply?

Cor
Nov 20 '05 #9
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.MissingSchem aAction = MissingSchemaAc tion.AddWithKey

I hope this helps, otherwise reply?

Cor

Nov 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2960
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am trying to insert. I therefore do not think that I can use the sql "Insert Into" command. Here is a simplified illustration of my tables: tblFather NaturalKey1 NatuarlKey2
5
3756
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for Office 2000. I am creating a database to track student athletes. I have created the following tables. The table title is to the far left, with fields under each. The common field will be the StudentID field, which is their student number assigned...
0
966
by: Paul | last post by:
I have a working system for AutoIncrementing a new row added to a DataGrid but I keep feeling like it's a kludge. It depends on assigning a zero to the Primary Key ID field when the row is added to the DataTable. I use that zero for a couple of things when I go into EditMode, like setting the RowFilter, etc. The good thing is that zero is a unique number that would never be duplicated for the row ID of the table. I also use zero's...
20
2156
by: Bryan | last post by:
hello all... im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status. ive tried in quotes and not, giving it an integer variable with the number 1
10
27882
by: Bernie Yaeger | last post by:
I have a need to add a primary key to a dataset/datatable. How can this be done using a standard oledb data provider? Tx for any help.
3
3227
by: Hugh O | last post by:
Hi, I am not sure if this type of question should be raised in this Newsgroup. If not please direct me. I am new to using RDO.Net data access but I thought I understood it. The 6 lines of code below is simply trying to load an empty table that has only three columns. In the code I set the Name and Type fields. The third column is a unique primary key set to Integer. I have set the Primary Key to Identity with a seed of 1 and...
8
3278
by: shumaker | last post by:
I'm wondering if adding an autonumber primary key will improve the performance of a multiuser access database on a network share. I have a website that lists many tips for improving performance of access, but doesn't mention primary keys. However, it seems logical to think that having no primary key means that when a user updates a record, the database has to do comparisons on multiple fields to identify the specific record being...
1
2663
by: accessbee | last post by:
(Sorry this is so long, was trying to fully explain the situation) There are two tables that handle information on our clients. Every client has a unique ClientID, and the ClientID is the Primary Key for the records. I have a query that displays fields from both tables, but while one of the tables, the main one that hold the Contact information has ALL of the clients in it; the other table holds additional, optional information, so does not...
3
4839
by: Shestine | last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is delete the whole table and recreates it, adding in the extra column. I don't want that. I want the data that is currently there to stay there and then add anew column. How do I reword this (If possible) to make it work? if exists (select * from...
0
10846
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10594
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9376
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4458
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4020
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.