DataTable AutoIncrement Problem

[Pat Pattillo]

Hi,

I am doing a 3 tier thick client app and use a middle tier to get and put
dataset contents. I am using an approach, very similar to many prescribed by
Microsoft and in books to create a pseudokey as an identity/autoincrement
column with seed and step both -1. Then when database table is updated, the
stored procedure that ultimately updates returns the true key via
SCOPE_IDENTITY().

The problem does not seem to involve the database at all, or in any case,
directly.

Here is the Microsoft example:

DataSet ds = new DataSet();
try
{

//Fill DataSet, and then set DataRelation to move through
the DataGrid.
conn.Open();

daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
daOrder.Fill(ds,"Orders");

daDetails.FillSchema(ds,SchemaType.Mapped,"Details");
daDetails.Fill(ds,"Details");

ds.Relations.Add("OrdDetail",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Details"].Columns["OrderID"]);

DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());

}
I think I am doing approximately the same thing but I am not using
FillSchema but rather MissingSchemaAction = AddWithKey...

My problem is that despite my having specified seed and step of -1 after
filling the DataSet, when I add a new row to a DataSet table it attempts to
use a new key that is already in the table, thus violating the primary key
constraint.

Here is my code to fill the DataSet:

DataSet theDataSet = new DataSet(Name);

message = null;

try

{

SqlDataAdapter da = new SqlDataAdapter(SelectCommand());

// could cache schema and use it if available and not expired vs. adding
each time

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

for (int i = 0; i < Tables.Length; i++)

{

string tablename = "Table" + i.ToString();

if (tablename == "Table0") tablename = "Table";

da.TableMappings.Add(Tables, tablename);

}

theDataSet.EnforceConstraints = false;

da.Fill(theDataSet);


int numrelations = Relations.GetLength(0);

for (int i = 0; i < numrelations; i++)

{

theDataSet.Relations.Add(Tables[i+1], theDataSet.Tables[Relations[i,
0]].Columns[Relations[i, 1]], theDataSet.Tables[Relations[i,
2]].Columns[Relations[i, 3]]);

}

for (int i = 0; i < Tables.Length; i++)

{

// NOTE : This assumes that all primary keys are identity

DataColumn dc = theDataSet.Tables[0].PrimaryKey[0];

theDataSet.Tables.Columns["pk"].AutoIncrement = true;

theDataSet.Tables.Columns["pk"].AutoIncrementSeed = -1;

theDataSet.Tables.Columns["pk"].AutoIncrementStep = -1;

theDataSet.Tables.Columns["pk"].ReadOnly = true;

}

theDataSet.EnforceConstraints = true;

}

catch(System.Exception ex)

{

theDataSet = null;

message = ex.Message;

}



Finally when the fill routine returns the following code is executed to add
a new row:

DataColumn dc = ds.Tables["Table"].Columns["pk"];

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

row["f1"] = "f1";

row["f2"] = "f2";

ds.Tables[0].Rows.Add(row);

The last statement incurs the primary constraint error but examination of
the row after the execution of NewRow() reveals that a row with a positive
primary key value is being created, the value already being a key of a row
that is already in both the table and the database.

Any ideas? Anyone? I am stumped, and all ears. I may try some things in the
meantime but if I find anything I'll reply to this post myself.

Thanks for any help!!! Really!!! I must be missing something either
incredibly obvious or incredibly subtle.

 

[Pat Pattillo]

I made a change that works but I do not understand why it works, nor do I
understand why the Microsoft example would work. There are some subtle
aspects of the way fillschema vs. fill/addwithkey work that are not clearly
documented.

The change I make that works is that I use MissingSchemaAction.Add instead
of AddWithKey and that change results in NewRow() returning a row with the
appropriate negative key value.

Hopefully, someeone might know the answer...

Thanks!
Pat Pattillo

Hi,

I am doing a 3 tier thick client app and use a middle tier to get and put
dataset contents. I am using an approach, very similar to many prescribed
by Microsoft and in books to create a pseudokey as an
identity/autoincrement column with seed and step both -1. Then when
database table is updated, the stored procedure that ultimately updates
returns the true key via SCOPE_IDENTITY().

The problem does not seem to involve the database at all, or in any case,
directly.

Here is the Microsoft example:

DataSet ds = new DataSet();
try
{

//Fill DataSet, and then set DataRelation to move through
the DataGrid.
conn.Open();

daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
daOrder.Fill(ds,"Orders");

daDetails.FillSchema(ds,SchemaType.Mapped,"Details");
daDetails.Fill(ds,"Details");

ds.Relations.Add("OrdDetail",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Details"].Columns["OrderID"]);

DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());

}
I think I am doing approximately the same thing but I am not using
FillSchema but rather MissingSchemaAction = AddWithKey...

My problem is that despite my having specified seed and step of -1 after
filling the DataSet, when I add a new row to a DataSet table it attempts
to use a new key that is already in the table, thus violating the primary
key constraint.

Here is my code to fill the DataSet:

DataSet theDataSet = new DataSet(Name);

message = null;

try

{

SqlDataAdapter da = new SqlDataAdapter(SelectCommand());

// could cache schema and use it if available and not expired vs. adding
each time

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

for (int i = 0; i < Tables.Length; i++)

{

string tablename = "Table" + i.ToString();

if (tablename == "Table0") tablename = "Table";

da.TableMappings.Add(Tables, tablename);

}

theDataSet.EnforceConstraints = false;

da.Fill(theDataSet);


int numrelations = Relations.GetLength(0);

for (int i = 0; i < numrelations; i++)

{

theDataSet.Relations.Add(Tables[i+1], theDataSet.Tables[Relations[i,
0]].Columns[Relations[i, 1]], theDataSet.Tables[Relations[i,
2]].Columns[Relations[i, 3]]);

}

for (int i = 0; i < Tables.Length; i++)

{

// NOTE : This assumes that all primary keys are identity

DataColumn dc = theDataSet.Tables[0].PrimaryKey[0];

theDataSet.Tables.Columns["pk"].AutoIncrement = true;

theDataSet.Tables.Columns["pk"].AutoIncrementSeed = -1;

theDataSet.Tables.Columns["pk"].AutoIncrementStep = -1;

theDataSet.Tables.Columns["pk"].ReadOnly = true;

}

theDataSet.EnforceConstraints = true;

}

catch(System.Exception ex)

{

theDataSet = null;

message = ex.Message;

}



Finally when the fill routine returns the following code is executed to
add a new row:

DataColumn dc = ds.Tables["Table"].Columns["pk"];

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

row["f1"] = "f1";

row["f2"] = "f2";

ds.Tables[0].Rows.Add(row);

The last statement incurs the primary constraint error but examination of
the row after the execution of NewRow() reveals that a row with a positive
primary key value is being created, the value already being a key of a row
that is already in both the table and the database.

Any ideas? Anyone? I am stumped, and all ears. I may try some things in
the meantime but if I find anything I'll reply to this post myself.

Thanks for any help!!! Really!!! I must be missing something either
incredibly obvious or incredibly subtle.

 

[Pat Pattillo]

Found out more but still no answer. I think the answer is but a gleam in
some Microsoft developer's eye -- One that was not passed of to
documentation at release time. Of course, I could always have missed
something; Here is the next thing I found out while attempting to narror
this down further:

By changing :

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

to:

da.MissingSchemaAction = MissingSchemaAction.Add;

it worked and new datatable rows conformed to AutoIncrementSeed etc (i.e.
were negative)

However, I manually added a primary key manually and this did not "break" it
by causing the error again.

So, the question seems to be one of "Exactly what is caused by
MissingSchemaAction of Add vs. AddWithKey besides just a primary key
constraint?



Here is the code that worked with Add and manual indication of PrimaryKey.

Maybe someone from Microsoft can explain what is really going on.....

- start of code example -
da.MissingSchemaAction = MissingSchemaAction.Add;

for (int i = 0; i < Tables.Length; i++)

{

string tablename = "Table" + i.ToString();

if (tablename == "Table0") tablename = "Table";

da.TableMappings.Add(Tables, tablename);

}

// theDataSet.EnforceConstraints = false;

da.Fill(theDataSet);


int numrelations = Relations.GetLength(0);

for (int i = 0; i < numrelations; i++)

{

theDataSet.Relations.Add(Tables[i+1], theDataSet.Tables[Relations[i,
0]].Columns[Relations[i, 1]], theDataSet.Tables[Relations[i,
2]].Columns[Relations[i, 3]]);

}

for (int i = 0; i < Tables.Length; i++)

{

DataColumn[] dcarray = new DataColumn[1];

dcarray[0] = theDataSet.Tables.Columns[0];

theDataSet.Tables.PrimaryKey = dcarray;

// NOTE : This assumes that all primary keys are identity

// TODO : Assumes that primary key is always first. Use some other approach

theDataSet.Tables.Columns[0].AutoIncrement = true;

theDataSet.Tables.Columns[0].AutoIncrementSeed = -1;

theDataSet.Tables.Columns[0].AutoIncrementStep = -1;

theDataSet.Tables.Columns[0].ReadOnly = true;

- end of code example -

I made a change that works but I do not understand why it works, nor do I
understand why the Microsoft example would work. There are some subtle
aspects of the way fillschema vs. fill/addwithkey work that are not clearly
documented.

The change I make that works is that I use MissingSchemaAction.Add instead
of AddWithKey and that change results in NewRow() returning a row with the
appropriate negative key value.

Hopefully, someeone might know the answer...

Thanks!
Pat Pattillo

Hi,

I am doing a 3 tier thick client app and use a middle tier to get and put
dataset contents. I am using an approach, very similar to many prescribed
by Microsoft and in books to create a pseudokey as an
identity/autoincrement column with seed and step both -1. Then when
database table is updated, the stored procedure that ultimately updates
returns the true key via SCOPE_IDENTITY().

The problem does not seem to involve the database at all, or in any case,
directly.

Here is the Microsoft example:

DataSet ds = new DataSet();
try
{

//Fill DataSet, and then set DataRelation to move through
the DataGrid.
conn.Open();

daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
daOrder.Fill(ds,"Orders");

daDetails.FillSchema(ds,SchemaType.Mapped,"Details");
daDetails.Fill(ds,"Details");

ds.Relations.Add("OrdDetail",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Details"].Columns["OrderID"]);

DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());

}
I think I am doing approximately the same thing but I am not using
FillSchema but rather MissingSchemaAction = AddWithKey...

My problem is that despite my having specified seed and step of -1 after
filling the DataSet, when I add a new row to a DataSet table it attempts
to use a new key that is already in the table, thus violating the primary
key constraint.

Here is my code to fill the DataSet:

DataSet theDataSet = new DataSet(Name);

message = null;

try

{

SqlDataAdapter da = new SqlDataAdapter(SelectCommand());

// could cache schema and use it if available and not expired vs. adding
each time

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

for (int i = 0; i < Tables.Length; i++)

{

string tablename = "Table" + i.ToString();

if (tablename == "Table0") tablename = "Table";

da.TableMappings.Add(Tables, tablename);

}

theDataSet.EnforceConstraints = false;

da.Fill(theDataSet);


int numrelations = Relations.GetLength(0);

for (int i = 0; i < numrelations; i++)

{

theDataSet.Relations.Add(Tables[i+1], theDataSet.Tables[Relations[i,
0]].Columns[Relations[i, 1]], theDataSet.Tables[Relations[i,
2]].Columns[Relations[i, 3]]);

}

for (int i = 0; i < Tables.Length; i++)

{

// NOTE : This assumes that all primary keys are identity

DataColumn dc = theDataSet.Tables[0].PrimaryKey[0];

theDataSet.Tables.Columns["pk"].AutoIncrement = true;

theDataSet.Tables.Columns["pk"].AutoIncrementSeed = -1;

theDataSet.Tables.Columns["pk"].AutoIncrementStep = -1;

theDataSet.Tables.Columns["pk"].ReadOnly = true;

}

theDataSet.EnforceConstraints = true;

}

catch(System.Exception ex)

{

theDataSet = null;

message = ex.Message;

}



Finally when the fill routine returns the following code is executed to
add a new row:

DataColumn dc = ds.Tables["Table"].Columns["pk"];

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

row["f1"] = "f1";

row["f2"] = "f2";

ds.Tables[0].Rows.Add(row);

The last statement incurs the primary constraint error but examination of
the row after the execution of NewRow() reveals that a row with a
positive primary key value is being created, the value already being a
key of a row that is already in both the table and the database.

Any ideas? Anyone? I am stumped, and all ears. I may try some things in
the meantime but if I find anything I'll reply to this post myself.

Thanks for any help!!! Really!!! I must be missing something either
incredibly obvious or incredibly subtle.

 

[David Sceppa]

Pat,

You need to set the AutoIncrement properties prior to adding rows to
the DataTable in order to have the desired effect.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.