adding a primary key to a dataset/datatable

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.

re: adding a primary key to a dataset/datatable

OleDataAdapter should have primary key in its select command/statement,
for e.g Select Employee_ID from employees

Then use fill command to fill the dataset

OleDataAdapter.Fill(Dataset)
thats it, you'll have primary key in ur dataset


"Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
news:eHvghQRGFHA.2568@TK2MSFTNGP10.phx.gbl...[color=blue]
>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.
>[/color]

re: adding a primary key to a dataset/datatable

Hi Arsalan.

I am working with vfp free tables - there are no primary keys in the
original tables and they can't be added to the backend (because they are not
mine and adding a column would screw them up).

Bernie

"Arsalan" <arsalan_aslam@hotmail.com> wrote in message
news:%23dt5KVRGFHA.3596@TK2MSFTNGP12.phx.gbl...[color=blue]
> OleDataAdapter should have primary key in its select command/statement,
> for e.g Select Employee_ID from employees
>
> Then use fill command to fill the dataset
>
> OleDataAdapter.Fill(Dataset)
> thats it, you'll have primary key in ur dataset
>
>
> "Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
> news:eHvghQRGFHA.2568@TK2MSFTNGP10.phx.gbl...[color=green]
>>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.
>>[/color]
>
>[/color]

re: adding a primary key to a dataset/datatable

If the table structure doesn't contain primary key then its impossible to
add it to the dataset.
How about making a column alias from two or more columns [like composite
primary key] that would make it possible to select records from the table,
it all depends on what kind of column u have in ur database table

"Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
news:uvmEmaRGFHA.560@TK2MSFTNGP15.phx.gbl...[color=blue]
> Hi Arsalan.
>
> I am working with vfp free tables - there are no primary keys in the
> original tables and they can't be added to the backend (because they are
> not mine and adding a column would screw them up).
>
> Bernie
>
> "Arsalan" <arsalan_aslam@hotmail.com> wrote in message
> news:%23dt5KVRGFHA.3596@TK2MSFTNGP12.phx.gbl...[color=green]
>> OleDataAdapter should have primary key in its select command/statement,
>> for e.g Select Employee_ID from employees
>>
>> Then use fill command to fill the dataset
>>
>> OleDataAdapter.Fill(Dataset)
>> thats it, you'll have primary key in ur dataset
>>
>>
>> "Bernie Yaeger" <berniey@cherwellinc.com> wrote in message
>> news:eHvghQRGFHA.2568@TK2MSFTNGP10.phx.gbl...[color=darkred]
>>>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.
>>>[/color]
>>
>>[/color]
>
>[/color]

re: adding a primary key to a dataset/datatable

I assume you are asking to add a primary key just for processing while
the dataset/datatable is in memory and aren't trying to write it back
out anywhere.

You can add a primary key by using the PrimaryKey property on a
DataTable

You can use the following code as an example:

Dim lds as DataSet = GetSomeDataSet()

For Each ldt As DataTable In lds.Tables
ldt.PrimaryKey = New DataColumn() {ldt.Columns("ColumnName")}
Next

-------

A couple things to note here. This iterates through each datatable
that's returned in the dataset, you can run the line inside the
for..each by itself if you only want to create a PrimaryKey for just one
table.

Also, if you want to make a multi-column primary key then the line
inside the for..each would look something like this:

ldt.PrimaryKey = New DataColumn() _
{ldt.Columns("ColumnName1"), ldt.Columns("ColumnName2")}

Hopefully this makes sense...

Brian Swanson
[color=blue][color=green]
> > I am working with vfp free tables - there are no primary keys in the
> > original tables and they can't be added to the backend (because they are[/color][/color]

re: adding a primary key to a dataset/datatable

One other note...

When adding the primary key in this way the system doesn't automatically
set the column you assign to the primary key as unique. You have to do
this as well by using:

ldt.Columns("ColumnName").Unique = True


---
Brian Swanson


"Brian Swanson" <purpleant@gREMOVETHISmail.com> wrote in message
news:purpleant@gREMOVETHISmail.com:[color=blue]
> I assume you are asking to add a primary key just for processing while
> the dataset/datatable is in memory and aren't trying to write it back
> out anywhere.
>
> You can add a primary key by using the PrimaryKey property on a
> DataTable
>
> You can use the following code as an example:
>
> Dim lds as DataSet = GetSomeDataSet()
>
> For Each ldt As DataTable In lds.Tables
> ldt.PrimaryKey = New DataColumn() {ldt.Columns("ColumnName")}
> Next
>
> -------
>
> A couple things to note here. This iterates through each datatable
> that's returned in the dataset, you can run the line inside the
> for..each by itself if you only want to create a PrimaryKey for just one
>
> table.
>
> Also, if you want to make a multi-column primary key then the line
> inside the for..each would look something like this:
>
> ldt.PrimaryKey = New DataColumn() _
> {ldt.Columns("ColumnName1"), ldt.Columns("ColumnName2")}
>
> Hopefully this makes sense...
>
> Brian Swanson
>[color=green][color=darkred]
> > > I am working with vfp free tables - there are no primary keys in the
> > > original tables and they can't be added to the backend (because they
> > > are[/color][/color][/color]

re: adding a primary key to a dataset/datatable

Hi Brian,

I looked at a couple of books I have on my shelf and figured this out, but
thank you.

Unfortunately, I do want to write it back to the backend, and, as luck would
have it, this does me absolutely no good! (as your caveat suggested). But
thanks for responding.

Bernie

"Brian Swanson" <purpleant@gREMOVETHISmail.com> wrote in message
news:%238$yyiVGFHA.2180@TK2MSFTNGP12.phx.gbl...[color=blue]
> One other note...
>
> When adding the primary key in this way the system doesn't automatically
> set the column you assign to the primary key as unique. You have to do
> this as well by using:
>
> ldt.Columns("ColumnName").Unique = True
>
>
> ---
> Brian Swanson
>
>
> "Brian Swanson" <purpleant@gREMOVETHISmail.com> wrote in message
> news:purpleant@gREMOVETHISmail.com:[color=green]
>> I assume you are asking to add a primary key just for processing while
>> the dataset/datatable is in memory and aren't trying to write it back
>> out anywhere.
>>
>> You can add a primary key by using the PrimaryKey property on a
>> DataTable
>>
>> You can use the following code as an example:
>>
>> Dim lds as DataSet = GetSomeDataSet()
>>
>> For Each ldt As DataTable In lds.Tables
>> ldt.PrimaryKey = New DataColumn() {ldt.Columns("ColumnName")}
>> Next
>>
>> -------
>>
>> A couple things to note here. This iterates through each datatable
>> that's returned in the dataset, you can run the line inside the
>> for..each by itself if you only want to create a PrimaryKey for just one
>>
>> table.
>>
>> Also, if you want to make a multi-column primary key then the line
>> inside the for..each would look something like this:
>>
>> ldt.PrimaryKey = New DataColumn() _
>> {ldt.Columns("ColumnName1"), ldt.Columns("ColumnName2")}
>>
>> Hopefully this makes sense...
>>
>> Brian Swanson
>>[color=darkred]
>> > > I am working with vfp free tables - there are no primary keys in the
>> > > original tables and they can't be added to the backend (because they
>> > > are[/color][/color]
>[/color]

re: adding a primary key to a dataset/datatable

Bernie,

What is it you want to achieve setting a primary key in the tables of your
foxpro database.
Or use as Brian wrote use it internally by instance to set a relation.

For me it is as well not clear which of the two it is (because I have readed
your previous questions).

Cor

re: adding a primary key to a dataset/datatable

Hi Cor,

I tried it, and it doesn't achieve my goal. I was hoping that it would
somehow enable me to update the back end more effectively, but it did not.

Bernie

"Cor Ligthert" <notmyfirstname@planet.nl> wrote in message
news:u5Ss6ulGFHA.2732@TK2MSFTNGP15.phx.gbl...[color=blue]
> Bernie,
>
> What is it you want to achieve setting a primary key in the tables of your
> foxpro database.
> Or use as Brian wrote use it internally by instance to set a relation.
>
> For me it is as well not clear which of the two it is (because I have
> readed your previous questions).
>
> Cor
>[/color]

re: adding a primary key to a dataset/datatable

Bernie,

Your goal is not clear in my opinion.

Do you want to set the keys in the database
Or
Do you want to set the primary keys in the dataset.

Where I assume setting the primary keys in the database using the SQL Alter
command and the

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

the SQLcommaned.executenonquery to process that.

:-)

Cor

re: adding a primary key to a dataset/datatable

Hi Cor,

The problem with that is, I am not allowed to alter the tables - they belong
to a third party. I am simply hooking into them.

I've come to the conclusion that I will only be able to do this by using the
foxpro environment itself, but I probably won't have to - it appears my
client will be happy with 'view access' only, so I can hook in, gather the
data I need, and display it.

Tx for your help.

Bernie

"Cor Ligthert" <notmyfirstname@planet.nl> wrote in message
news:epmaPrnGFHA.400@TK2MSFTNGP14.phx.gbl...[color=blue]
> Bernie,
>
> Your goal is not clear in my opinion.
>
> Do you want to set the keys in the database
> Or
> Do you want to set the primary keys in the dataset.
>
> Where I assume setting the primary keys in the database using the SQL
> Alter command and the
>
> http://msdn.microsoft.com/library/de...able___sql.asp
>
> the SQLcommaned.executenonquery to process that.
>
> :-)
>
> Cor
>[/color]