Access Update() Duplicate Exception

[Steve B.]

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)" for
the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying, in
affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve

[W.G. Ryan eMVP]

You're going to have to look at the Update code that you are sending back -
see what the proposed values vs the original values were and hunt it down
from there. It could be something with your uupdate command - or your app
could be modifiying a value it shouldn't be.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Steve B." <St****@discussions.microsoft.com> wrote in message
news:03**********************************@microsof t.com...

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)" for the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying, in affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve

[Sahil Malik]

As Bill suggested, look at the command being sent back.

BTW, try doing a fillschema on your ds before doing a fill. That *might* fix
it, though that'd be in my eyes a very inefficent and bad solution - not to
mention the automatic command population is a real inefficient pig to start
with.

The best approach is to specify your own UpdateCommand so you have control
on what you're doing.

However since you are using Access, I am lead to believe that this is a one
user one d/b or close to it kind of situation, so you might even get away
with FillSchema only.

Anyway, give it a try and yes, you need to look at the command.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

"Steve B." <St****@discussions.microsoft.com> wrote in message
news:03**********************************@microsof t.com...

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)"
for
the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying,
in
affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve

[Steve B.]

Thank you

1. How do I look at the Update code I’m sending back??

//my code
if (dataSet.HasChanges(DataRowState.Modified))
{
conn.Open();
//entire ds, not getChanges()
dataAdapter.Update(dataSet, tableName); //entire ds
conn.Close();
}

2. I’m looking into fillshema command now

//my code
setDataSetConstraintsOff(tempDataSet);
openDbConnection(configMgmtLogConn);
dbAdapter.Fill(dataSet);
setDataSetConstraintsOn(tempDataSet);
closeDbConnection(configMgmtLogConn);

Most everything is done through a Interface. If I change the Access Indexed
Field Property to “No� I don’t get the exception
"Steve B." wrote:

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)" for
the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying, in
affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve

[Sahil Malik]

Answer to #1 -- dataAdapter.UpdateCommand.CommandText.

Answer to #2 --
Not sure why are you disabling the constraints, is it for performance??
How much data are you filling? You will have a bigger performance chunk by
specifying your own UpdateCommand if the data being filled isn't > 100ish
rows (well the rowsize matters too). FillSchema is simply
dbAdapter.FillSchema, but the dataset constraints might contradict with
that, since those are counter intuitive to FillSchema. Also what process is
setting the dataset constraints in the first place before you call
setDataSetConstraintsOff

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


"Steve B." <St****@discussions.microsoft.com> wrote in message
news:C5**********************************@microsof t.com...

Thank you

1. How do I look at the Update code I'm sending back??

//my code
if (dataSet.HasChanges(DataRowState.Modified))
{
conn.Open();
//entire ds, not getChanges()
dataAdapter.Update(dataSet, tableName); //entire ds
conn.Close();
}

2. I'm looking into fillshema command now

//my code
setDataSetConstraintsOff(tempDataSet);
openDbConnection(configMgmtLogConn);
dbAdapter.Fill(dataSet);
setDataSetConstraintsOn(tempDataSet);
closeDbConnection(configMgmtLogConn);

Most everything is done through a Interface. If I change the Access
Indexed
Field Property to "No" I don't get the exception
"Steve B." wrote:

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)"
for
the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying,
in
affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve

[Steve B.]

The constraints were from another program I templated from. I’ve commented
them out for now. There are 1000 rows and about 16 columns.

The column I’m having the problem with is DocumentNumber. I also looked at
the VS SQL Builder. I'm just going to have to walk through the column and
declare the Indexed field as "No"

UPDATE StevesConfigMgmtLog
SET
[Acceptance(Y/N)] = ?,
ClassType = ?,
DateIn = ?,
DateOut = ?,
DCM_NDTApproved = ?,
DocumentNumber = ?,
DocumentType = ?,
Engine = ?,
Engineer = ?,
ErrorCategory = ?,
[GE_CauseCode/Type] = ?,
[IsCSI?] = ?,
Purpose = ?,
Remarks = ?,
ReviewerCauseCode = ?,
Subject = ?

WHERE
(ID = ?) AND
([Acceptance(Y/N)] = ? OR ? IS NULL AND [Acceptance(Y/N)] IS NULL) AND
(ClassType = ? OR ? IS NULL AND ClassType IS NULL) AND
(DCM_NDTApproved = ? OR ? IS NULL AND DCM_NDTApproved IS NULL) AND
(DateIn = ? OR ? IS NULL AND DateIn IS NULL) AND
(DateOut = ? OR ? IS NULL AND DateOut IS NULL) AND
(DocumentNumber = ? OR ? IS NULL AND DocumentNumber IS NULL) AND
(DocumentType = ?) AND (Engine = ? OR ? IS NULL AND Engine IS NULL) AND
(Engineer = ? OR ? IS NULL AND Engineer IS NULL) AND
(ErrorCategory = ? OR ? IS NULL AND ErrorCategory IS NULL) AND
([GE_CauseCode/Type] = ? OR ? IS NULL AND [GE_CauseCode/Type] IS NULL) AND
([IsCSI?] = ? OR ? IS NULL AND [IsCSI?] IS NULL) AND
(Purpose = ? OR ? IS NULL AND Purpose IS NULL) AND
(Remarks = ? OR ? IS NULL AND Remarks IS NULL) AND
(ReviewerCauseCode = ? OR ? IS NULL AND ReviewerCauseCode IS NULL) AND
(Subject = ? OR ? IS NULL AND Subject IS NULL)

"Steve B." wrote:

A particular column in my Access DB table, and the associated datagrid,
cannot have duplicate string entries. I've selected "Yes (no duplicates)" for
the Indexed Field Property of this column in the Access table.

When I call update(), the DB gets updated but throws an exception saying, in
affect, that I'm trying to change the key. This column is not the key.

How do I handle this? How do I stop the ex.message being thrown?

I guess I could always walk through the column searching for the value.

Steve