BeginEdit is ignored

[Guest]

Hi,

Im pretty new to ADO.net and I ran into the following problem:

I try to add a row to a DataSet that has NotNull-Constraints on its columns.

When doing:

DataRow row = ds.Tables["docdescriptors"].NewRow();

DocTypeDB dt = new DocTypeDB(row);

NpgsqlCommand command = new NpgsqlCommand("select
nextval('docdescriptor_id_seq')", connection);

row["id"] = (int)(Int64)command.ExecuteScalar();

ds.Tables["docdescriptors"].Rows.Add(row);



I get the a ConstraintException because I did not fill all columns. That's
ok so far. But following the documentaion I added a BeginEdit



DataRow row = ds.Tables["docdescriptors"].NewRow();

row.BeginEdit();

DocTypeDB dt = new DocTypeDB(row);

NpgsqlCommand command = new NpgsqlCommand("select
nextval('docdescriptor_id_seq')", connection);

row["id"] = (int)(Int64)command.ExecuteScalar();

ds.Tables["docdescriptors"].Rows.Add(row);



But I still get the Exception I try to add the Row to the table.



Best



Thomas

 

[Dave Sexton]

Hi Thomas,

DataRow.BeginEdit does not disable constraints on the DataSet, it only prevents validation events from being raised while the row is
being edited and also seperates the Original and Proposed values.

You must either initialize the DataRow with default values (recommended option) or disable constraints on the DataSet before adding
it to the DataTable. To disable constraints on the DataSet use:

ds.EnforceConstraints = false;

Note that setting EnforceConstraints to true after adding the uninitialized DataRow will then throw the exception.

On a different note I'm concerned with your code that sets the id field. I'm not familiar with the database engine that you are
using, but I would imagine that setting the id by executing nextval on the database is not such a good idea. If the nextval changes
before you update the database with the changes to your DataRow then you'll be updating a different row in the table, or if you try
to insert the DataRow and your code isn't prepared to ignore the id then you'll get an error because the id is already in use,
considering that there is a unique constraint on that column in the database. Again, I'm not familiar with this database engine,
however I felt that it was important enough to mention.

If the nextval sequence simply uses incremental numbers then you could change the id column in your DataTable to AutoIncrement. The
id of a new DataRow will be incremented automatically and once the DataRow has been inserted into the database (ignoring the id
column on inserts) you could retrieve the current value and assign it to the DataRow at that time (after the insert operation).

HTH

 

[MSN]

Hi Dave,

thanks a lot for your answer!

DataRow.BeginEdit does not disable constraints on the DataSet, it only
prevents validation events from being raised while the row is being edited
and also seperates the Original and Proposed values.

Ok, that explains this And I don't have to think about it anymore!

The problem that I want to solve is the following:

I want to create a row in the dataset at one place in the programm. Then let
a user insert several values into it and then store it to the database. My
intention was to let the database verify that all columns where filled with
needed values instead of letting the application do it. If I initialize the
fields with default vaules the database won't check them anymore. Any idea
how I do this the best way?

If the nextval sequence simply uses incremental numbers then you could
change the id column in your DataTable to AutoIncrement. The id of a new
DataRow will be incremented automatically and once the DataRow has been
inserted into the database (ignoring the id column on inserts) you could
retrieve the current value and assign it to the DataRow at that time
(after the insert operation).

Indeed, (Im using PostgreSQL) the nextval just increments a counter and
returns the value. I've read about several problems with the AutoIncrement
Feature of Datasets in Combination with multi users, so I decided to go this
way. Another problem was that I was not able to get the value of the newly
inserted row back from the database.When just using the autoincrement
feature of the database.

Thanks again and best regards

Thomas

Hi,

Im pretty new to ADO.net and I ran into the following problem:

I try to add a row to a DataSet that has NotNull-Constraints on its
columns.

When doing:

DataRow row = ds.Tables["docdescriptors"].NewRow();

DocTypeDB dt = new DocTypeDB(row);

NpgsqlCommand command = new NpgsqlCommand("select
nextval('docdescriptor_id_seq')", connection);

row["id"] = (int)(Int64)command.ExecuteScalar();

ds.Tables["docdescriptors"].Rows.Add(row);



I get the a ConstraintException because I did not fill all columns.
That's ok so far. But following the documentaion I added a BeginEdit



DataRow row = ds.Tables["docdescriptors"].NewRow();

row.BeginEdit();

DocTypeDB dt = new DocTypeDB(row);

NpgsqlCommand command = new NpgsqlCommand("select
nextval('docdescriptor_id_seq')", connection);

row["id"] = (int)(Int64)command.ExecuteScalar();

ds.Tables["docdescriptors"].Rows.Add(row);



But I still get the Exception I try to add the Row to the table.



Best



Thomas

 

[Dave Sexton]

Hi Thomas,

I want to create a row in the dataset at one place in the programm. Then let a user insert several values into it and then store
it to the database. My intention was to let the database verify that all columns where filled with needed values instead of
letting the application do it. If I initialize the fields with default vaules the database won't check them anymore. Any idea how
I do this the best way?

In that case just relax the constraints on your DataTable. You can't have it both ways

Indeed, (Im using PostgreSQL) the nextval just increments a counter and returns the value. I've read about several problems with
the AutoIncrement Feature of Datasets in Combination with multi users, so I decided to go this way.

Well if multiple users are sharing the same instance of a DataSet, via some sort of remoting framework, them maybe there could be
some issues but I'm not sure that's what you mean.

Actually, I thought that using nextval would cause a problem with multiple users acting on the database and that's why I brought
this up in the first place but this depends entirely on the function of nextval. Does nextval return a different number every time
it's executed or only the next available number with respect to the existing set of data? If it's the latter then you might run
into concurrency issues with your code, however there might not be any exceptions so you'd just end up with bad or missing data.

Another problem was that I was not able to get the value of the newly inserted row back from the database.When just using the
autoincrement feature of the database.

If you were to use a stored procedure to update the row then you could return a result set from that procedure consisting of the new
row's values. If you're using an OdbcCommand, for instance, the UpdatedRowSource property specifies how the OdbcDataAdapter will
update the source DataRow from the result set or output parameters when you use the OdbcDataAdapter.Update method. By default, you
shouldn't have to make any changes to settings on either the OdbcCommand or OdbcDataAdapter objects. Just call
OdbcDataAdatper.Update and make sure your procedure returns the updated row's values and that the field names correspond to the
names of each DataColumn to be updated.