Getting auto-number with DataSet

[Shahar]

Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.

 

[Jeff Ogata]

This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is what I
ended up doing.

 

[Frank Drebin]

Are you suggesting using a GUID as a primary key for a row, instead of using
an identity column in SQL??

This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is what I
ended up doing.

Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.

 

[Jeff Ogata]

Apologies for not being clearer -- I meant for Access. I believe the way to
do this with SQL Server is to write a stored procedure which does the update
and returns the value of @@IDENTY as an output param. The suggestion for
using the guid was to make up for the fact that you cannot do something
similar with Access. If you really need to use auto-number w/ Access, I
think the article I linked to shows you how, but in my particular case, it
was less work to use the guid.

Are you suggesting using a GUID as a primary key for a row, instead of using
an identity column in SQL??

This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is

what

I
ended up doing.

Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.

 

[Ron Allen]

Shahar,
You need to add a RowUpdatedEventHandler to your DataSet. It should
look something like this.

private void HandleRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
{
OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection, e.Command.Transaction);
e.Row["keyColumnNameHere"] = Convert.ToInt32(cmd.ExecuteScalar());
e.Row.AcceptChanges();
cmd.Dispose();
}
}

Ron Allen