Get "table doesn't have a primary key exception"

[Alex]

I get a "table doesn't have a primary key" exception during a
..Rows.Find(string);
However the table has a primary key. I moved it from one column to an other.
But the table designer shows it.

What could be wrong?

 

[William Ryan eMVP]

Alex:

Just so I'm sure I understand the background, what do you mean you moved it
from one column to another? If you declare an array of datacolumns name
myArray
and use myArray = DataTable.Name.PrimaryKey; and then iterate through the
myArray, how may columns does it have, are they the same that you expect
them to be etc?

I know this sounds obvious, but the first thing to do is verify that what
you expect the key to be is in fact the key. Next, if you could show the
code, that'd be really helpful.

Let me know.

Bill

 

[Alex]

First part:
I have a table with several columns.
col 1 is of type string
col 2 is of type uniqueidentifier

Col1 used to be the primary key (my selection).
I went into the table designer in VS2003.Net and selected col2 to be the
primary key. The move seemed to be working. I had a graphical indicator.

The number of colums looks OK. Went through with debugger.

Alex

 

[Alex]

I found something interesting.
When debuging and looking at he tables.primarykey property it shows
undefined!
Despite the fact that the table designer shows one.

 

[William Ryan eMVP]

Alex, the table designer affects the structure or the table in the DB, not
in the datatable or dataset that you fill locally. I suspected this was the
problem thats why I wanted to verify the key. Fortunately, it's easy to
fix. On the one hand you can use a StronglyTyped Dataset and define the key
there. Then once you fill the table, it's automatically done for you. In
doing so, it's very straightforward to get your Datatable to match your
table in the db. The other alternative is to create the primary key
locally, which is pretty simple too
http://www.knowdotnet.com/articles/adopartiii.html . The only caveat is that
if you don't define a schema beforehand and use dataAdapter.Fill to build
the datatable,, you'll need to wait until after the fill to apply the key
(the column doesn't before the fill unless you define it). Similarly, (and
this shouldn't be a problem if your local key matches the key in the DB), if
you have a key defined locally and data doesn't conform ie duplicate values
or nulls, the fill won't work right.

HTH,

Bill

 

[Alex]

Ok, we are getting closer, here is some (still not working) code:
The find will return nothing.

myUniqueIDCol is of type uniqueidentifier.

Alex
//create the DataSet

DataSet myDataSet = new DataSet();

//Fill the dataset with the data adapter

myAdapter.Fill(myDataSet , "myTable");

//need to set the primary key or use strongly typed dataset

DataColumn[] keys = new DataColumn[1];

keys[0] = myDataSet .Tables["myTable"].Columns["myUniqueIDCol"];

myDataSet .Tables["Sponsors"].PrimaryKey = keys;

//create the Data Row

DataRow dr = myDataSet .Tables["myTable"].Rows.Find(sID);

dr.Delete();

 

[William Ryan eMVP]

Alex:

What value is sID?

Ok, we are getting closer, here is some (still not working) code:
The find will return nothing.

myUniqueIDCol is of type uniqueidentifier.

Alex
//create the DataSet

DataSet myDataSet = new DataSet();

//Fill the dataset with the data adapter

myAdapter.Fill(myDataSet , "myTable");

//need to set the primary key or use strongly typed dataset

DataColumn[] keys = new DataColumn[1];

keys[0] = myDataSet .Tables["myTable"].Columns["myUniqueIDCol"];

myDataSet .Tables["Sponsors"].PrimaryKey = keys;

//create the Data Row

DataRow dr = myDataSet .Tables["myTable"].Rows.Find(sID);

dr.Delete();

Alex, the table designer affects the structure or the table in the DB, not
in the datatable or dataset that you fill locally. I suspected this was the
problem thats why I wanted to verify the key. Fortunately, it's easy to
fix. On the one hand you can use a StronglyTyped Dataset and define the key
there. Then once you fill the table, it's automatically done for you. In
doing so, it's very straightforward to get your Datatable to match your
table in the db. The other alternative is to create the primary key
locally, which is pretty simple too
http://www.knowdotnet.com/articles/adopartiii.html . The only caveat is that
if you don't define a schema beforehand and use dataAdapter.Fill to build
the datatable,, you'll need to wait until after the fill to apply the key
(the column doesn't before the fill unless you define it). Similarly, (and
this shouldn't be a problem if your local key matches the key in the

DB),

if
you have a key defined locally and data doesn't conform ie duplicate values
or nulls, the fill won't work right.

HTH,

Bill
datacolumns
name to
an

 

[Alex]

It is a guid as a string. I have to convert it first into a GUID to make it
work.
GUID findguid = new GUID(sID);
And do the find with the findguid.

I was assuming that the find would accept the string and do the conversion
on its own.
The find didn't throw an exception.

Alex

Alex:

What value is sID?

Ok, we are getting closer, here is some (still not working) code:
The find will return nothing.

myUniqueIDCol is of type uniqueidentifier.

Alex
//create the DataSet

DataSet myDataSet = new DataSet();

//Fill the dataset with the data adapter

myAdapter.Fill(myDataSet , "myTable");

//need to set the primary key or use strongly typed dataset

DataColumn[] keys = new DataColumn[1];

keys[0] = myDataSet .Tables["myTable"].Columns["myUniqueIDCol"];

myDataSet .Tables["Sponsors"].PrimaryKey = keys;

//create the Data Row

DataRow dr = myDataSet .Tables["myTable"].Rows.Find(sID);

dr.Delete();

Alex, the table designer affects the structure or the table in the DB, not
in the datatable or dataset that you fill locally. I suspected this

was
the

problem thats why I wanted to verify the key. Fortunately, it's easy to
fix. On the one hand you can use a StronglyTyped Dataset and define

the
key

there. Then once you fill the table, it's automatically done for you. In
doing so, it's very straightforward to get your Datatable to match your
table in the db. The other alternative is to create the primary key
locally, which is pretty simple too
http://www.knowdotnet.com/articles/adopartiii.html . The only caveat

is
that

if you don't define a schema beforehand and use dataAdapter.Fill to build
the datatable,, you'll need to wait until after the fill to apply the key
(the column doesn't before the fill unless you define it). Similarly, (and
this shouldn't be a problem if your local key matches the key in the

DB),

if
you have a key defined locally and data doesn't conform ie duplicate values
or nulls, the fill won't work right.

HTH,

Bill
I found something interesting.
When debuging and looking at he tables.primarykey property it shows
undefined!
Despite the fact that the table designer shows one.

First part:
I have a table with several columns.
col 1 is of type string
col 2 is of type uniqueidentifier

Col1 used to be the primary key (my selection).
I went into the table designer in VS2003.Net and selected col2 to

be
the

primary key. The move seemed to be working. I had a graphical indicator.

The number of colums looks OK. Went through with debugger.

Alex

Alex:

Just so I'm sure I understand the background, what do you mean you
moved
it
from one column to another? If you declare an array of datacolumns
name
myArray
and use myArray = DataTable.Name.PrimaryKey; and then iterate through
the
myArray, how may columns does it have, are they the same that you
expect
them to be etc?

I know this sounds obvious, but the first thing to do is verify that
what
you expect the key to be is in fact the key. Next, if you could show
the
code, that'd be really helpful.

Let me know.

Bill
I get a "table doesn't have a primary key" exception during a
.Rows.Find(string);
However the table has a primary key. I moved it from one

column

to

 

[William Ryan eMVP]

Alex:

If you Debug.Writeline the value of the guid, have you checked that the
value you are creating actually matches the one in the DB? If they don't
match the rest is for not, so verify that first.

It is a guid as a string. I have to convert it first into a GUID to make it
work.
GUID findguid = new GUID(sID);
And do the find with the findguid.

I was assuming that the find would accept the string and do the conversion
on its own.
The find didn't throw an exception.

Alex

Alex:

What value is sID?

Ok, we are getting closer, here is some (still not working) code:
The find will return nothing.

myUniqueIDCol is of type uniqueidentifier.

Alex
//create the DataSet

DataSet myDataSet = new DataSet();

//Fill the dataset with the data adapter

myAdapter.Fill(myDataSet , "myTable");

//need to set the primary key or use strongly typed dataset

DataColumn[] keys = new DataColumn[1];

keys[0] = myDataSet .Tables["myTable"].Columns["myUniqueIDCol"];

myDataSet .Tables["Sponsors"].PrimaryKey = keys;

//create the Data Row

DataRow dr = myDataSet .Tables["myTable"].Rows.Find(sID);

dr.Delete();

Alex, the table designer affects the structure or the table in the

DB,
not

in the datatable or dataset that you fill locally. I suspected this was
the
problem thats why I wanted to verify the key. Fortunately, it's

easy

to you.
In the
key

to

be verify
that column