newbie: why does DataSet AutoIncrement advance by 2?

6 Mar 2006 8:01 AM

deko

I create a DataSet called database and add a table to it as shown below.
Why does the AutoIncrement value advance by 2 every time I add a record?
How do I get it to advance by 1?

DataTable tblProject = database.Tables.Add("TableProject");
DataColumn project_ID = tblProject.Columns.Add("Project_ID", typeof(Int32));
project_ID.AllowDBNull = false;
project_ID.Unique = true;
project_ID.AutoIncrement = true;
project_ID.AutoIncrementSeed = 1;
project_ID.AutoIncrementStep = 1;
tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns[0] };

//other columns added here...

6 Mar 2006 9:01 AM

Miha Markic [MVP C#]

Hi,

You should use negative values for Auto*. This way they won't interfere with
database generated values and you can immediately spot new row.
project_ID.AutoIncrementSeed = -1;
project_ID.AutoIncrementStep = -1;

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"deko" <deko@nospam.com> wrote in message
news:kvidnfkWfuW2cZbZ4p2dnA@comcast.com...
>I create a DataSet called database and add a table to it as shown below.
>Why does the AutoIncrement value advance by 2 every time I add a record?
>How do I get it to advance by 1?
>
> DataTable tblProject = database.Tables.Add("TableProject");
> DataColumn project_ID = tblProject.Columns.Add("Project_ID",
> typeof(Int32));
> project_ID.AllowDBNull = false;
> project_ID.Unique = true;
> project_ID.AutoIncrement = true;
> project_ID.AutoIncrementSeed = 1;
> project_ID.AutoIncrementStep = 1;
> tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns[0] };
>
> //other columns added here...

6 Mar 2006 9:20 AM

Patrice

And make sure you don't do call NewRow twice...

--

Show quote

"Miha Markic [MVP C#]" <miha at rthand com> a écrit dans le message de
news:exLhHyPQGHA.2628@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> You should use negative values for Auto*. This way they won't interfere
with
> database generated values and you can immediately spot new row.
> project_ID.AutoIncrementSeed = -1;
> project_ID.AutoIncrementStep = -1;
>
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "deko" <deko@nospam.com> wrote in message
> news:kvidnfkWfuW2cZbZ4p2dnA@comcast.com...
> >I create a DataSet called database and add a table to it as shown below.
> >Why does the AutoIncrement value advance by 2 every time I add a record?
> >How do I get it to advance by 1?
> >
> > DataTable tblProject = database.Tables.Add("TableProject");
> > DataColumn project_ID = tblProject.Columns.Add("Project_ID",
> > typeof(Int32));
> > project_ID.AllowDBNull = false;
> > project_ID.Unique = true;
> > project_ID.AutoIncrement = true;
> > project_ID.AutoIncrementSeed = 1;
> > project_ID.AutoIncrementStep = 1;
> > tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns[0] };
> >
> > //other columns added here...
>
>

6 Mar 2006 2:52 PM

deko

> And make sure you don't do call NewRow twice...

Here's how I add a row:

public int insertNewProject(string projectName)
{
   dtP = database.Tables[tblP]; //tblP is a string const
   int rowCount = dtP.Rows.Count;
   Console.WriteLine(rowCount);
   foreach (DataRow row in dtP.Rows)
   {
      Console.WriteLine(row[colPid]); //colP is a string const
   }
   DataRow newRow = dtP.NewRow();
   Console.WriteLine(projectName);
   newRow[colP] = projectName;
   dtP.Rows.Add();
   database.AcceptChanges();
   DataRow lastRow = dtP.Rows[rowCount];
   int pid = (int)lastRow[colPid]; //I want to get the ID of the newly added
row
   return pid;
}

The AutoNumber PK still increments by 2 with the negative value, and
projectName never makes it into the table for some reason.

6 Mar 2006 3:06 PM

Patrice

dtP.Rows.Add();

Never used this. It's likely adding a blank row. Try dtp.Rows.Add(newRow);
instead to add the row you previoulsy initialized rather than IMO a new
blank one...

--
Patrice

Show quote

"deko" <deko@nospam.com> a écrit dans le message de
news:tLKdnbWSIpnJ0ZHZRVn-pg@comcast.com...
> > And make sure you don't do call NewRow twice...
>
> Here's how I add a row:
>
> public int insertNewProject(string projectName)
> {
>    dtP = database.Tables[tblP]; //tblP is a string const
>    int rowCount = dtP.Rows.Count;
>    Console.WriteLine(rowCount);
>    foreach (DataRow row in dtP.Rows)
>    {
>       Console.WriteLine(row[colPid]); //colP is a string const
>    }
>    DataRow newRow = dtP.NewRow();
>    Console.WriteLine(projectName);
>    newRow[colP] = projectName;
>    dtP.Rows.Add();
>    database.AcceptChanges();
>    DataRow lastRow = dtP.Rows[rowCount];
>    int pid = (int)lastRow[colPid]; //I want to get the ID of the newly
added
> row
>    return pid;
> }
>
> The AutoNumber PK still increments by 2 with the negative value, and
> projectName never makes it into the table for some reason.
>

6 Mar 2006 3:32 PM

deko

> dtP.Rows.Add();
>
> Never used this. It's likely adding a blank row. Try dtp.Rows.Add(newRow);
> instead to add the row you previoulsy initialized rather than IMO a new
> blank one...

You are 100% correct.

This also fixed the AutoIncrement double advance:

dtP.Rows.Add(newRow);

Thanks for the help!

6 Mar 2006 2:46 PM

deko

> You should use negative values for Auto*. This way they won't interfere
> with database generated values and you can immediately spot new row.
> project_ID.AutoIncrementSeed = -1;
> project_ID.AutoIncrementStep = -1;

Thanks for the tip.

But what database generated code?

6 Mar 2006 3:32 PM

Kerry Moorman

deko,

Database generated VALUES. The database is going to generate autonumber
primary key values when you update the database with new rows from the
dataset.

Kerry Moorman

Show quote

"deko" wrote:

> > You should use negative values for Auto*. This way they won't interfere
> > with database generated values and you can immediately spot new row.
> > project_ID.AutoIncrementSeed = -1;
> > project_ID.AutoIncrementStep = -1;
>
> Thanks for the tip.
>
> But what database generated code?
>
>

6 Mar 2006 3:56 PM

deko

> Database generated VALUES. The database is going to generate autonumber
> primary key values when you update the database with new rows from the
> dataset.

10-4. But it was said (at least I think it was said) that it's better to
step the AutoIncrement PK with -1 so it does not conflict with database
generated values. My question was WHAT database-generated values? Are we
talking about other AutoIncrement columns?

I could have a dozen AutoIncrement fields in the same table - there is no
conflict there.

Is stepping with -1 just a preference?