467,184 Members | 1,258 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,184 developers. It's quick & easy.

Getting auto-number with DataSet

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.
Nov 15 '05 #1
  • viewed: 9905
Share:
4 Replies
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.

"Shahar" <sh****@log-on.com> wrote in message
news:e8**************************@posting.google.c om...
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.

Nov 15 '05 #2
Are you suggesting using a GUID as a primary key for a row, instead of using
an identity column in SQL??
"Jeff Ogata" <jo****@eatmyspam.com> wrote in message
news:OP**************@TK2MSFTNGP10.phx.gbl...
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.

"Shahar" <sh****@log-on.com> wrote in message
news:e8**************************@posting.google.c om...
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.


Nov 15 '05 #3
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.

"Frank Drebin" <no*****@imsickofspam.com> wrote in message
news:kg**********************@newssvr28.news.prodi gy.com...
Are you suggesting using a GUID as a primary key for a row, instead of using an identity column in SQL??
"Jeff Ogata" <jo****@eatmyspam.com> wrote in message
news:OP**************@TK2MSFTNGP10.phx.gbl...
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.

"Shahar" <sh****@log-on.com> wrote in message
news:e8**************************@posting.google.c om...
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.



Nov 15 '05 #4
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

"Shahar" <sh****@log-on.com> wrote in message
news:e8**************************@posting.google.c om...
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.

Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Ian | last post: by
3 posts views Thread by Heiko Milke | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.