Hi Martin,
In Sql Server, the sequence is tied to the column.
Thus, when ADO.Net fetches the schema for the dataset, it can
automatically fetch the maximum value of the autonumber and properly
configure the AutoIncrementSeed, AutoIncrementStep values based on the
schema.
No, this has nothing to do with the RDBMS. I'm quite sure
I've never had such an issue with an auto-incrementing DataColumn.
As a matter of fact, if there are already records in the DataTable when a
new
record is added, the greatest number that is still unique is chosen
automatically, it seems.
I'm curious to see exactly what you are doing, but I understand if you
can't
post code that's not yours to post.
Maybe this will help:
DataSet data = new DataSet();
DataTable table = data.Tables.Add();
DataColumn cInt32 = table.Columns.Add("ID", typeof(int));
DataColumn cString = table.Columns.Add("Value", typeof(string));
cInt32.AllowDBNull = false;
cInt32.AutoIncrement = true;
cInt32.Unique = true;
cInt32.ReadOnly = true;
DataRow row1 = table.Rows.Add(null, "1"); // id=0
DataRow row2 = table.Rows.Add(null, "2"); // id=1
DataRow row3 = table.Rows.Add(null, "3"); // id=2
Console.WriteLine(row1["ID"]); // id=0
Console.WriteLine(row2["ID"]); // id=1
Console.WriteLine(row3["ID"]); // id=2
DataRow row4 = table.NewRow(); // id=3
row4["Value"] = "4";
Console.WriteLine(row4["ID"]);
table.Rows.Add(row4); // id=3
table.AcceptChanges();
Console.WriteLine(row1["ID"]); // id=0
Console.WriteLine(row2["ID"]); // id=1
Console.WriteLine(row3["ID"]); // id=2
Console.WriteLine(row4["ID"]); // id=3
Output:
0
1
2
3
0
1
2
3
--
Dave Sexton
Looking it over, I think I've figured out what's happening. In Oracle,
the autonumber sequence is external to the column - it's just a
sequencer object. In Sql Server, the sequence is tied to the column.
Thus, when ADO.Net fetches the schema for the dataset, it can
automatically fetch the maximum value of the autonumber and properly
configure the AutoIncrementSeed, AutoIncrementStep values based on the
schema.
In Oracle, it can't get that information automatically. I would have
to call a second query to find out what the current value of my
sequencer is to get the current AutoIncrementSeed. I could do a decent
faking of it with something like Max(myAutonumberColumnValue) + 1 as
the seed and set that as the AutoIncrementSeed.... either way though,
I'm creating values that will be thrown away when the data enters the
table, so I'm just using negatives. It works well.
Thanks for all your help. Personally, I'm thinking that the idea of
applying constraints to values that will never actually enter the
database is silly - but you've been extremely helpful at understanding
how things work within the dataset (and it's relation to the data
adaptors) - and found a workable solution to my problem.
Martin Z wrote:
Ah, I misunderstood. I imagined the behaviour for an autoincrement to
be "must be null for new rows, and apply primary key constraint for
existing ones" or something. Either way, I can't post the code because
I'm a new developer working on a large old company app (ported forward
from VB), trying to reapply their old crudscreen tool to a new table
I've added. So, my tweaks to the tool are here-there-and-everywhere,
unfortunately.
The crux of the matter is that the autoincrement system creates a
number that violates the uniqueness constraint of the table. I have a
table with 3 rows, primary key values 1, 2 and 3. When I create a new
row and attempt to add it to my dataset, it has a value of 2 that the
autoincrement column has autogenerated - which, logically, the system
rejects.
The idea of having to generate a
"unique-within-my-dataset-but-not-necessarily-within-the-actual-table"
value that will be replaced anyways seems very hackish, but I suppose I
have to satisfy the constraint on the dataset.
I'll try the "start at -1 and decrement" approach.
Dave Sexton wrote:
Hi Martin,
Oop, I missed the tail end of your message. Yes, the value should
always be null until the commit, when it should be populated.... but
this behaviour is not happening.
No, it shouldn't be null. If the column is Unique, then DBNull for
every
new
value wouldn't satisfy that constraint. AutoIncrement creates a new
number
that is temporary, and preserves the uniqueness of newly added rows
until
you
can perform the update and retrieve the actual value. The behavior
you
have
described is exactly what I would expect from an AutoIncrement column.
If I create an empty datarow and set
the other (non-autoincrementing-key-fields) it attempts to put a
value
in the key field too - either way it violates constraints. I can't
even get to update, because it complains the moment I add the row to
the table.
The values chosen by the DataTable for newly added rows should always
be
unique (I've never had a problem), so I'm curious to know what error
you're
getting when adding a new row. Care to post the code and the
exception?
The behaviour I want is obvious - the autoincrement field
should be stuck at DBNull until update, at which time the field
should
be fixed at the value that Oracle provided
That would violate the Unique constraint, so it's impossible. If you
want to
relax the constraints then you don't need the AutoIncrement column
either, but
I wouldn't recommend that approach.
Unfortunately, for some
reasons datasets don't provide that mechanism for me - maybe it only
works like that when using SqlServer - I don't know well enough how
DataSets work under the hood.
This particular issue you're having has nothing to do with the RDBMS.
The
DataSet and the database are distinct entities. AutoIncrement and its
behavior belongs solely to the DataSet, which has no relationship to
the
database or the data provider being used.
