System.Data.ReadOnlyException with DataTable with Expression and Update command

  • Thread starter Thread starter Stan Sainte-Rose
  • Start date Start date
S

Stan Sainte-Rose

Hi

I get a System.Data.ReadOnlyException when I use the update adapter command.
I have a calculated column with the table and I suppose the problem is
there.
The problem occurs after running a InsertCommand.

Stan
 
Sure :

daLignes.InsertCommand = AjoutProduit()
daLignes.Update(Ds.Tables("lignes"))

Private Function AjoutProduit()

Dim cmd As OleDbCommand
strSQL = "INSERT INTO A_LIGCDES " & _
"(idcdeclient,idproduit,idclient,idfournisseur,qte,pu,remise)" & _
" values (?,?,?,?,?,?,?); SELECT SCOPE_IDENTITY() AS IDLIG "
cmd = New OleDbCommand(strSQL, cn)
cmd.Parameters.Add("idcdeclient", OleDb.OleDbType.Integer, 0, "idcdeclient")
cmd.Parameters.Add("idproduit", OleDb.OleDbType.Integer, 0, "idproduit")
cmd.Parameters.Add("idclient", OleDb.OleDbType.Integer, 0, "idclient")
cmd.Parameters.Add("idfournisseur", OleDb.OleDbType.Integer, 0,
"idfournisseur")
cmd.Parameters.Add("qte", OleDb.OleDbType.Integer, 0, "qte")
cmd.Parameters.Add("pu", OleDb.OleDbType.Decimal, 0, "pu")
cmd.Parameters.Add("remise", OleDb.OleDbType.Decimal, 0, "remise")
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
Return cmd
End Function

My datatable is like this :
With Ds.Tables.Add("Lignes")
col = .Columns.Add("idlig", GetType(Integer))
col.AutoIncrement = True
col.AutoIncrementSeed = -1
col.AutoIncrementStep = -1
..Columns.Add("idcdeclient", GetType(Integer))
..Columns.Add("idclient", GetType(Integer))
..Columns.Add("idproduit", GetType(Integer))
..Columns.Add("idfournisseur", GetType(Integer))
..Columns.Add("qte", GetType(Integer))
..Columns.Add("pu", GetType(Decimal))
..Columns.Add("remise", GetType(Decimal))
col = .Columns.Add("TotalLigne", GetType(Decimal))
col.Expression = "(Pu * Qte) * ((100 - remise) / 100)"
..PrimaryKey = New DataColumn() {.Columns("idlig")}
End With
 
Hi Stan,

I've though at beginning that you are using Access (OleDb namespace).
I see that you are using SqlServer - why don't you use SqlClient namespace?
Nevermind.
To me it seems a bug.
The adapter is updating all fields regardless if they are
readonly/expression or not.
This is only one peculiarity with expression columns - I've found them,
though an nice addition. too problematic for real use - I use them only for
readonly results.

Nevertheless, there is a solution - not very elegant:
Remove the expression column from DataTable before saving (you can re-add
it later) or switch from Sql stataments to stored procedures.
Btw, you should do your adapter operations on GetChanges() copy of dataset
anyway, so it is safe to remove expression columns.
 
I see that you are using SqlServer - why don't you use SqlClient namespace?
Nevermind.
Click to expand...

You are right :)
Nevertheless, there is a solution - not very elegant:
Remove the expression column from DataTable before saving (you can re-add
it later) or switch from Sql stataments to stored procedures.
Click to expand...

Btw, is it better to work with Stored Procedure ?
Btw, you should do your adapter operations on GetChanges() copy of dataset
anyway, so it is safe to remove expression columns.
Click to expand...

What do you mean ?

Thanks for your help
 
Hi Stan,
Btw, is it better to work with Stored Procedure ?
Click to expand...

The operations will be faster plus it will work in this case without need to
remove columns.
What do you mean ?
Click to expand...

You should do updates within a transaction on a copy of dataset.
That's why there is GetChanges() method which extracts only dirty rows from
DataSet.
The process would be something like:
DataSet changes = dataset.GetChanges();
begin transaction
try
{
dataadapter.Update(a table);
dataadapter.Update(a table);
dataadapter.Update(a table);
...
commit;
dataset.Merge(changes);
}
catch
{
error occured
rollback;
}
 
The sentence
ReadOnly = false; correct the problem

try{

dsPeticiones1.Tables["TratamientoPeticionesLog"].Columns["IdPeticion"].ReadOnly
= false;


DataRow RowPeticion =
dsPeticiones1.Tables["TratamientoPeticionesLog"].NewRow();
RowPeticion["NumPeticion"]
= "AAAAAA" ;
RowPeticion["Expediente"]
= "BBBBBB" ;
RowPeticion["ResultadoTratamiento"]
= 1;
RowPeticion["MensajeErrorTratamiento"]
= "Correcto";
RowPeticion["HoraTratamiento"]
= DateTime.Now;


dsPeticiones1.Tables["TratamientoPeticionesLog"].Rows.Add(RowPeticion);
odbcDataAdapter1.Update(dsPeticiones1,
"TratamientoPeticionesLog");

}
catch(System.Data.ReadOnlyException ex){

}




private void odbcDataAdapter1_RowUpdated(object sender,
System.Data.Odbc.OdbcRowUpdatedEventArgs e) {

if (e.Status == UpdateStatus.Continue &&
e.StatementType == StatementType.Insert ) {
// Obtener el valor de la columna
Identity
e.Row["IdPeticion"] =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
e.Row.AcceptChanges();
}


}
 
Back
Top