By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,784 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,784 IT Pros & Developers. It's quick & easy.

Getting auto-number with DataSet

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.