Howto remove a primary key column in a datatable?

[Martin]

Hi,

I understand you must first remove the constraint. The following bit does
not work (where t is a DataTable). It gives a 'reference not set to an
object' error

t.PrimaryKey.Clear(t.PrimaryKey, 0, 1)
If t.Columns.Contains("MYKEY") Then
If t.Columns.CanRemove(t.Columns("MYKEY")) Then
t.Columns.Remove("MYKEY")
End If
End If

[Steven Cheng[MSFT]]

Hi ,

Thanks for your posting. As for the problem you mentioned, it seems that
the "non reference exception" is due to we didn't provide a correctly
ColumnName or DataColumn instance. I've tried do some tests locally and we
can remove a certain datacolumn from datatable as long as the CanRemove
return true.

I think you can try provide the DataColumn object instance into the Remove
method instead of the columnName to see whether it helps. For example:

If t.Columns.Contains("KEY") Then
If t.Columns.CanRemove(t.Columns("KEY")) Then
dim col as DataColumn = t.Columns("KEY")
t.Columns.Remove(col)
End If
End If

If you have anyother findings, please also feel free to post here. Thanks.
Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Martin]

All Right, Yeah, I'll regard that as a quick fix, but I am sure that there's
something more to it.

I never needed to get the instance of the column when trying to remove it.
It has to do with it being a Primary Key Column, and removing the Primary Key
seems to hurt the column name somehow. I have other pieces of code where I
get a 'Column not found' error after removing the Primary Key from the Keys
collection. E.g. the t.AcceptChanges also gets confused after removing a
Primary Key.

Do we have a bug here?

[Steven Cheng[MSFT]]

Hi Martin,

I've done some further test on myside and seems the Remove method which
take the columnName as param also works well. Is the DataTable(DAtaSet) in
your app retrieved from the DataBase and is the table has a single columns
PK or composite PK? I've test on DataTable retrieved from database or
programly built on the fly which contains a single column PK. I remove the
PK setting and then remove the PK column via Colums.Remove("ColumnName") ,
that worked ok. So I'm not sure whether there're anything else particular
in your app. Would also try the thing I mentioned above or provide a simple
demo code sample so that I can try repro it on my side?
Also, if you have any other concerns, please feel free to let me know.
Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Martin]

incomprehensible!

I've done some further test on myside and seems the Remove method which
take the columnName as param also works well. In my code it does not ...
Is the DataTable(DataSet) in your app retrieved from the DataBase
and is the table has a single columns PK or composite PK?

Programatic table, single column PK

I'll send you my test application

[Steven Cheng[MSFT]]

Hi Martin,

I've recieved your mail and the attached test app, I'll made some tests on
my local side and will update you when I got some values. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Hi Martin,

After some testing , I've got some results and here is the thing I've found:

==========================
t = GetMyTable()

t.PrimaryKey.Clear(t.PrimaryKey, 0, 1)

col = t.Columns("COL_KEY")

t.Columns.Remove(col)
===========================

The above code will throw NullReference exception , and the following code
worked well

=======================
t = GetMyTable()

t.PrimaryKey = Nothing

col = t.Columns("COL_KEY")

t.Columns.Remove(col)
==========================

And I found the PrimaryKey.Clear(..) ( infact it is Array.Clear) will set
the items(specified via the index range) to Nothing. But the PrimaryKey
array's length is still 1. So I'm think this should be the cause. But still
not sure about the root cause. Currently I'm doing some further research on
this and will let you know if I got any progresses. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Martin]

Actually I had tried [ t.PrimaryKey = Nothing ] in my code earlier, but I
got errors on [ t.Acceptchanges ] after manipulating the data in the table.

I believed it had to do with the way I cleared the Primary Key, and then
started looking for another way of clearing the key. Then I found the
Primary.Clear method. Actually I was suprised it worked this way since it
asks for a System.Array as the first argument, and although the PrimaryKey is
in fact an array of type Data.DataColumn it looks a bit tricky to me.

Nevertheless, I'll start using [ t.PrimaryKey = Nothing ] and hope I do
not run into the earlier errors that may show up because of that. I'll keep
you informed.

[Steven Cheng[MSFT]]

Thanks for your followup Martin,

If you find any further issues on this, please feel free to post here.
Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Martin]

Hi Steven,

In you post you wrote:
....But the PrimaryKey array's length is still 1. So I'm think this should be
the cause. but still not sure about the root cause ...

Just out of curiosity; were you able to trace this further? I remains a bit
spooky to me.

[Steven Cheng[MSFT]]

Hi Martin,

En, yes, I'm also wondering the remain "1" length in the pk collection.
Currently I still haven't got any further info, but I'll hold on this issue
and let you know if I got any feed back from the DEV guys. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Hi Martin,

After some further research and consulting to the dev guys, they've also
noticed the problem and currently the workaround is just as we mentioned in
the above message:

Use the datatable.PrimaryKey = NOthing rather than Array.Clear to drop the
pk.
If there is constraints on the datatable, do explicitly remove the
constraint

Then, we can delete the pk column.

Also, we'll improve this behavior in the furture version, at least to
provide some more informative and useful message so as not to mislead the
developer. Also, I'm sorry for any inconvenience it has brought you.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)