Update problem...

  • Thread starter Thread starter Paolo
  • Start date Start date
P

Paolo

I'm using an Access database in a .NET C# solution... I have a
document table and I have to insert documents in this table. The first
time I start the application I have to insert a datarow in the table
describing this document, but then the user can modify it and when he
presses save I have to update the datatable. The problem is that when
I call the Update method on the DataTable I'm using it doesn't update
it!

Here's the code...

....//modify the datarow

if(! this.bSaved) /*the document is not saved, that is the datarow
hasn't been inserted in the datatable yet*/
{
ds.Tables["Documents"].Rows.Add(datarow);
}
this.da.Update(ds.Tables["Documents"]);

The second time the program executes this code it doesn't insert the
datarow, but it just update the datatable. The problem is that it
throws a DBConcurrencyExcepption, saying that the update method didn't
affect any row. But I can't see how it is possible, since the datarow
state is Modified! What am I doing wrong?


Thank in advance for your help!

Paolo
 
Hi Paolo,

Do you create InsertCommand and UpdateCommand for
DataAdapter before you call the DataAdapter's Update
method?

If not, you can use CommandBuilder to automatically create
these Commands (if the data source is from single table):

OleDbDataAdapter da = new OleDbDataAdapter(sql,
connection);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Fill(ds,"Documents");
..
..
..

da.Update(ds,"Documents");

Hope it helps,

Elton Wang
(e-mail address removed)
 
I created the commands, but that's the result i get... It seems like I
can't update a row, because it just adds a new one or doesn't modify
the old one...
 
Elton Wang said:
Could give more details about your code, such as your
UpdateCommand?

Elton Wang
Click to expand...

I didn't create them by myself, I let Visual Studio create them for
me... Now I can't access my code I'm not at work, tomorrow I'll give
you more details...
Thank you for your help!
 
Here's the update comand that visula studio generated for me:
this.daDocumenti.UpdateCommand.CommandText = @"UPDATE Documenti SET
clienteID = ?, dataDocumento = ?, documentoPathName = ?, evaso = ?,
[note] = ?, numeroDocumento = ?, scontoPercentuale = ?,
tipoDocumentoID = ?, totaleDocumento = ?, totaleIVA = ?, totaleNetto =
?, totaleRighe = ? WHERE (documentoID = ?) AND (clienteID = ?) AND
(dataDocumento = ? OR ? IS NULL AND dataDocumento IS NULL) AND
(documentoPathName = ? OR ? IS NULL AND documentoPathName IS NULL) AND
(evaso = ?) AND (numeroDocumento = ? OR ? IS NULL AND numeroDocumento
IS NULL) AND (scontoPercentuale = ? OR ? IS NULL AND scontoPercentuale
IS NULL) AND (tipoDocumentoID = ?) AND (totaleDocumento = ? OR ? IS
NULL AND totaleDocumento IS NULL) AND (totaleIVA = ? OR ? IS NULL AND
totaleIVA IS NULL) AND (totaleNetto = ? OR ? IS NULL AND totaleNetto
IS NULL) AND (totaleRighe = ? OR ? IS NULL AND totaleRighe IS NULL)";

this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("clienteID",
System.Data.OleDb.OleDbType.Integer, 0, "clienteID"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dataDocumento",
System.Data.OleDb.OleDbType.DBDate, 0, "dataDocumento"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("documentoPathName",
System.Data.OleDb.OleDbType.VarWChar, 200, "documentoPathName"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("evaso",
System.Data.OleDb.OleDbType.Boolean, 2, "evaso"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("note",
System.Data.OleDb.OleDbType.VarWChar, 0, "note"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("numeroDocumento",
System.Data.OleDb.OleDbType.Integer, 0, "numeroDocumento"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("scontoPercentuale",
System.Data.OleDb.OleDbType.Double, 0, "scontoPercentuale"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("tipoDocumentoID",
System.Data.OleDb.OleDbType.Integer, 0, "tipoDocumentoID"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("totaleDocumento",
System.Data.OleDb.OleDbType.Double, 0, "totaleDocumento"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("totaleIVA",
System.Data.OleDb.OleDbType.Double, 0, "totaleIVA"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("totaleNetto",
System.Data.OleDb.OleDbType.Double, 0, "totaleNetto"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("totaleRighe",
System.Data.OleDb.OleDbType.Double, 0, "totaleRighe"));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_documentoID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "documentoID",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_clienteID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "clienteID", System.Data.DataRowVersion.Original,
null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_dataDocumento",
System.Data.OleDb.OleDbType.DBDate, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "dataDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_dataDocumento1",
System.Data.OleDb.OleDbType.DBDate, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "dataDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_documentoPathName",
System.Data.OleDb.OleDbType.VarWChar, 200,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "documentoPathName",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_documentoPathName1",
System.Data.OleDb.OleDbType.VarWChar, 200,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "documentoPathName",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_evaso",
System.Data.OleDb.OleDbType.Boolean, 2,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "evaso", System.Data.DataRowVersion.Original,
null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_numeroDocumento",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "numeroDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_numeroDocumento1",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "numeroDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_scontoPercentuale",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "scontoPercentuale",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_scontoPercentuale1",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "scontoPercentuale",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_tipoDocumentoID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "tipoDocumentoID",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleDocumento",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleDocumento1",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleDocumento",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleIVA",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleIVA", System.Data.DataRowVersion.Original,
null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleIVA1",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleIVA", System.Data.DataRowVersion.Original,
null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleNetto",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleNetto",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleNetto1",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleNetto",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleRighe",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleRighe",
System.Data.DataRowVersion.Original, null));
this.daDocumenti.UpdateCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_totaleRighe1",
System.Data.OleDb.OleDbType.Double, 0,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "totaleRighe",
System.Data.DataRowVersion.Original, null));

Some words are in italian but you should unerstand it anyway...

Let me know if you have any suggestion please!
 
I also tried using a OleDbCommmandBuilder but the problem is the
datasource has many tables so it dooesn't work...
 
Hi Paolo,

The UpdateCommand itself looks good. However, you
mentioned the select Sql queries multi-tables. If so, I
suppose the UpdateCommand is based on the select query,
which will cause trouble. In that case, it's better to
create UpdateCommand manually.

Elton Wang
 
Thank you for your help! It workks now! The problem was I created
UPDATE, INSERT and DELETE commands but I didn't create SELECt, I
tought it would create it by itself in the conctructor of the
OleDbDataAdapter!

Thank you again!
Paolo
 
Back
Top