Modification of the primary key !!

Hi

I have binded a DataGradView (DGV) with a Dataset (DS) , i execute and modify on the DataGradView the primary key (IDObj) and also the two other columns, by clicking on the UpDate button i call the function UpDateTable :

public void UpdateTable(string nameTable)
{
// con : ma connection OLE à un fichier Access 2003
// DS : dataSet
// DAdp : Dataadapter
// nameTable : nom de ma table
OleDbCommand comdUPDATE;
string CommandText = "UPDATE " + nameTable + " SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj";
comdUPDATE=DAdp.UpdateCommand = new OleDbCommand(CommandText, con);
// IDObj : cl principale
comdUPDATE.Parameters.Add(new OleDbParameter("@.IDObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.NameParent", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.TypeObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters["@.IDObj"].SourceVersion= DataRowVersion.Original; //!!!
comdUPDATE.Parameters["@.NameParent"].SourceVersion= DataRowVersion.Current;
comdUPDATE.Parameters["@.TypeObj"].SourceVersion= DataRowVersion.Current;

comdUPDATE.Parameters["@.IDObj"].SourceColumn = "IDObj";
comdUPDATE.Parameters["@.NameParent"].SourceColumn = "NameParent";
comdUPDATE.Parameters["@.TypeObj"].SourceColumn = "TypeObj";
DataSet modifiedDS = DS.GetChanges(DataRowState.Modified);

try
{
con.Open();
DAdp.Update(modifiedDS.Tables[nameTable]);
DS.Clear();
DAdp.Fill(DS, nameTable);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
finally { con.Close(); }

}

But i have this exception : Concurrency violation : the update command affected 0 of the expected 1 records.

how is it possible to modify a primary key witch is the reference of that modified row?

Thanks for help

The more inportant question is WHY would you ever want to modify a primary key?

Primary keys are commonly used to create relationships between data. If the Primary Key was altered, the relationship would break.

The concurrency violation is because you are attempting to create two rows with the same primary key value -and that is not allowed.

|||

I know that each row has to be identified with that primary key it's like its coordinates.

but sometimes the identification of an object (equipments, tasks..) is wrong and need to be corrected.

is there i way to do it?

|||

Hi,

You are going to alter the database design. It better if you make amendment at database level.

Cheers!!

|||

Yes, you just update the table and set the Primary Key column equal to the new value.

However, if that new value already exists in the table, you will get an error -like the error you are currently getting. There cannot be two rows in the table with the exact same primary key value.

However, in your query, there is a problem:

" SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj"[/quote]

This is written like this: SET A = B WHERE A = B

I hope you can see the problem. You need two parameters, one for the old value to use in the WHERE clause, and another for the new value assignment. Perhaps more like:

Code Snippet

UPDATE MyTable
SET
IDObj = @.NewIDObj,
NameParent = @.NameParent,
TypeObj = @.TypeObj
WHERE IDObj = @.OldIDObj

|||

I'll trey this :

MyUpdateCommd.Parameters["@.OldIDObj"].SourceVersion =DataRowVersion.Original;

and let :

MyUpdateCommd.Parameters["@.NewIDObj"].SourceVersion =DataRowVersion.Current;

is it that?

|||That looks like it should work.|||

Hi Gays

always same exception.

A) what i have :

i binded i DataGradeView with Dataset.

i try to modify value of any columns.

the Update command failed and exception rised.

B) What i code :

string CommandText =
"UPDATE " + nameTable +
" SET IDObj=@.IDObj , NameParent=@.NameParent, TypeObj=@.TypeObj"+
" WHERE IDObj=@.IDObjOrig ";

I indicate the VersionSource wich is "Current" for all parameters exept for @.IDObjOrig that is "Original"

i did not use the value property of parameters because i modify by editing on Datagradview then i call the UpDate method of the DataAdapter by passing my dataset and the name of my table.

? exception......I don't understand ..

Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie