Getting Autoincrement ID in ADO.NET

  • Thread starter Thread starter tascienu
  • Start date Start date
T

tascienu

The C# & vb.net examples presented in the MS Help below are good
examples. Unfortunately, I am using a CommandBuilder as such, I do not
have access to the InsertCommand. the CB creates those behind the
scene.

How do i then use examples below to get AutoIncrement ID? I do not want
to use StoredProcedures.

Thanks...


MS Help Article
-----------------------

You can set a column in a DataTable to be an auto-
incrementing primary key in order to ensure a unique value
for each row in the table. However, you may have multiple
clients for your application, and each of those clients
may be working with a separate instance of the DataTable.
In this case, you might end up with duplicate values
between the separate instances of the DataTable. Because
all your clients will be working with a single data
source, you can resolve this conflict by letting the data
source define the auto-incremented value. To accomplish
this you use Identity fields in Microsoft SQL Server, or
Autonumber fields in Microsoft Access.


Using the data source to populate an Identity or
Autonumber column for a new row added to a DataSet creates
a unique situation because the DataSet has no direct
connection to the data source. As a result, the DataSet is
unaware of any values generated automatically by the data
source. However, with a data source that can create stored
procedures with output parameters, such as Microsoft SQL
Server, you can specify the automatically generated
values, such as a new identity value, as an output
parameter and use the DataAdapter to map that value back
to the column in the DataSet.


Your data source may not support stored procedures with
output parameters. In this case you may be able to use the
RowUpdated event to retrieve an automatically generated
value and place it in the inserted or updated row in the
DataSet. This section includes a sample that shows how,
with Microsoft Access 2000 or later, and using the Jet 4.0
OLE DB Provider, you can add code to the RowUpdated event
to determine if an insert has occurred and to retrieve the
auto-incremented value and store it in the currently
updated row.


The following stored procedure and code example show how
to map the auto-incremented identity value from a
Microsoft SQL Server table back to its corresponding
column in a row added to a table in a DataSet. The stored
procedure is used to insert a new row into the Categories
table of the Northwind database and to return the identity
value returned from SCOPE_IDENTITY() as an output
parameter.


CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
The InsertCategory stored procedure can then be specified
as the source of the DataAdapter.InsertCommand. A
parameter is created to receive the identity output
parameter. That parameter has a Direction of
ParameterDirection.Output, and has a SourceColumn
specified as the CategoryID column of the local Categories
table in the DataSet.When the InsertCommand is processed
for an added row, the auto-incremented identity value is
returned as this output parameter and is placed in the
CategoryID column of the current row.


The following code example shows the how to return the
auto-incremented value as the output parameter and specify
it as the source value for the CategoryID column in the
DataSet.


[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind")


Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID, CategoryName FROM Categories", nwindConn)


catDA.InsertCommand = New SqlCommand("InsertCategory",
nwindConn)
catDA.InsertCommand.CommandType =
CommandType.StoredProcedure


catDA.InsertCommand.Parameters.Add("@CategoryName",
SqlDbType.NChar, 15, "CategoryName")


Dim myParm As SqlParameter =
catDA.InsertCommand.Parameters.Add("@Identity",
SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output


nwindConn.Open()


Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")


Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)


catDA.Update(catDS, "Categories")


nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind");


SqlDataAdapter catDA = new SqlDataAdapter("SELECT
CategoryID, CategoryName FROM Categories", nwindConn);


catDA.InsertCommand = new SqlCommand("InsertCategory",
nwindConn);
catDA.InsertCommand.CommandType =
CommandType.StoredProcedure;


catDA.InsertCommand.Parameters.Add("@CategoryName",
SqlDbType.NChar, 15, "CategoryName");


SqlParameter myParm = catDA.InsertCommand.Parameters.Add
("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;


nwindConn.Open();


DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");


DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);


catDA.Update(catDS, "Categories");


nwindConn.Close();
Microsoft Access does not support stored procedures or
batch command processing, so it is not possible to map an
output parameter to the source column in the table in the
preceding example. However, Microsoft Access 2000 or later
does support the @@IDENTITY property to retrieve the value
of an Autonumber field after an INSERT. Using the
RowUpdated event, you can determine if an INSERT has
occurred, retrieve the latest @@IDENTITY value, and place
that in the identity column of the local table in the
DataSet.


The following code example inserts a new value into the
Categories table of the Microsoft Access 2000 Northwind
database. The example uses the RowUpdated event to fill in
the Autonumber values generated by the Jet engine and the
Access database when a record is inserted into the
Categories table. Note that this will only work with the
Jet 4.0 OLE DB provider and Microsoft Access 2000 or later.


[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic


Public class Sample


Shared nwindConn As OleDbConnection = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\Program Files\Microsoft
Office\Office\Samples\northwind.mdb;")


Public Shared Sub Main()


' Use the DataAdapter to fill and update the DataSet.
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter
("SELECT CategoryID, CategoryName FROM Categories ORDER BY
CategoryID", nwindConn)


catDA.InsertCommand = New OleDbCommand("INSERT INTO
Categories (CategoryName) Values(?)", nwindConn)
catDA.InsertCommand.CommandType = CommandType.Text


catDA.InsertCommand.Parameters.Add("@CategoryName",
OleDbType.Char, 15, "CategoryName")


nwindConn.Open()


' Fill the DataSet.
Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")


' Add a new row.
Dim newRow As DataRow = catDS.Tables
("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)


' Include an event to fill in the Autonumber value.
AddHandler catDA.RowUpdated, New
OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)


' Update the DataSet.
catDA.Update(catDS, "Categories")


nwindConn.Close()
End Sub


Private Shared Sub OnRowUpdated(sender As Object, args
As OleDbRowUpdatedEventArgs)
' Include a variable and a command to retrieve the
identity value from the Access database.
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT
@@IDENTITY", nwindConn)


If args.StatementType = StatementType.Insert
' Retrieve the identity value and store it in the
CategoryID column.
newID = CInt(idCMD.ExecuteScalar())
args.Row("CategoryID") = newID
End If
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;


public class Sample
{
static OleDbConnection nwindConn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data
Source=c:\Program Files\Microsoft
Office\Office\Samples\northwind.mdb;");


public static void Main()
{
// Use the DataAdapter to fill and update the DataSet.
OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT
CategoryID, CategoryName FROM Categories ORDER BY
CategoryID", nwindConn);


catDA.InsertCommand = new OleDbCommand("INSERT INTO
Categories (CategoryName) Values(?)", nwindConn);
catDA.InsertCommand.CommandType = CommandType.Text;


catDA.InsertCommand.Parameters.Add("@CategoryName",
OleDbType.Char, 15, "CategoryName");


nwindConn.Open();


// Fill the DataSet.
DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");


// Add a new row.
DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);


// Include an event to fill in the Autonumber value.
catDA.RowUpdated += new OleDbRowUpdatedEventHandler
(OnRowUpdated);


// Update the DataSet.
catDA.Update(catDS, "Categories");


nwindConn.Close();
}


protected static void OnRowUpdated(object sender,
OleDbRowUpdatedEventArgs args)
{
// Include a variable and a command to retrieve the
identity value from the Access database.
int newID = 0;
OleDbCommand idCMD = new OleDbCommand("SELECT
@@IDENTITY", nwindConn);


if (args.StatementType == StatementType.Insert)
{
// Retrieve the identity value and store it in the
CategoryID column.
newID = (int)idCMD.ExecuteScalar();
args.Row["CategoryID"] = newID;
}
}


}


See Also
Sample ADO.NET Scenarios | Accessing Data with ADO.NET |
Using .NET Data Providers to Access Data
 
I have a cat that I want to come in out of the rain. It won't come when I
call it and I don't want to go out and get her. What should I do?
If you insist on using the CommandBuilder against all advice and you don't
want to use stored procedures, then your alternatives are very limited. VS
solves this problem by using the CommandBuilder and THEN capturing the
UPDATE SQL and concatenating the needed extra query to fetch the new
Identity. You might try the same. I, for one would not. I would take off the
training wheels and learn to write my own UpdateCommand... You might also
execute another round trip to the server to get the value, but this only
really works with JET.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

The C# & vb.net examples presented in the MS Help below are good
examples. Unfortunately, I am using a CommandBuilder as such, I do not
have access to the InsertCommand. the CB creates those behind the
scene.

How do i then use examples below to get AutoIncrement ID? I do not want
to use StoredProcedures.

Thanks...


MS Help Article
-----------------------

You can set a column in a DataTable to be an auto-
incrementing primary key in order to ensure a unique value
for each row in the table. However, you may have multiple
clients for your application, and each of those clients
may be working with a separate instance of the DataTable.
In this case, you might end up with duplicate values
between the separate instances of the DataTable. Because
all your clients will be working with a single data
source, you can resolve this conflict by letting the data
source define the auto-incremented value. To accomplish
this you use Identity fields in Microsoft SQL Server, or
Autonumber fields in Microsoft Access.


Using the data source to populate an Identity or
Autonumber column for a new row added to a DataSet creates
a unique situation because the DataSet has no direct
connection to the data source. As a result, the DataSet is
unaware of any values generated automatically by the data
source. However, with a data source that can create stored
procedures with output parameters, such as Microsoft SQL
Server, you can specify the automatically generated
values, such as a new identity value, as an output
parameter and use the DataAdapter to map that value back
to the column in the DataSet.


Your data source may not support stored procedures with
output parameters. In this case you may be able to use the
RowUpdated event to retrieve an automatically generated
value and place it in the inserted or updated row in the
DataSet. This section includes a sample that shows how,
with Microsoft Access 2000 or later, and using the Jet 4.0
OLE DB Provider, you can add code to the RowUpdated event
to determine if an insert has occurred and to retrieve the
auto-incremented value and store it in the currently
updated row.


The following stored procedure and code example show how
to map the auto-incremented identity value from a
Microsoft SQL Server table back to its corresponding
column in a row added to a table in a DataSet. The stored
procedure is used to insert a new row into the Categories
table of the Northwind database and to return the identity
value returned from SCOPE_IDENTITY() as an output
parameter.


CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
The InsertCategory stored procedure can then be specified
as the source of the DataAdapter.InsertCommand. A
parameter is created to receive the identity output
parameter. That parameter has a Direction of
ParameterDirection.Output, and has a SourceColumn
specified as the CategoryID column of the local Categories
table in the DataSet.When the InsertCommand is processed
for an added row, the auto-incremented identity value is
returned as this output parameter and is placed in the
CategoryID column of the current row.


The following code example shows the how to return the
auto-incremented value as the output parameter and specify
it as the source value for the CategoryID column in the
DataSet.


[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind")


Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID, CategoryName FROM Categories", nwindConn)


catDA.InsertCommand = New SqlCommand("InsertCategory",
nwindConn)
catDA.InsertCommand.CommandType =
CommandType.StoredProcedure


catDA.InsertCommand.Parameters.Add("@CategoryName",
SqlDbType.NChar, 15, "CategoryName")


Dim myParm As SqlParameter =
catDA.InsertCommand.Parameters.Add("@Identity",
SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output


nwindConn.Open()


Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")


Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)


catDA.Update(catDS, "Categories")


nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind");


SqlDataAdapter catDA = new SqlDataAdapter("SELECT
CategoryID, CategoryName FROM Categories", nwindConn);


catDA.InsertCommand = new SqlCommand("InsertCategory",
nwindConn);
catDA.InsertCommand.CommandType =
CommandType.StoredProcedure;


catDA.InsertCommand.Parameters.Add("@CategoryName",
SqlDbType.NChar, 15, "CategoryName");


SqlParameter myParm = catDA.InsertCommand.Parameters.Add
("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;


nwindConn.Open();


DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");


DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);


catDA.Update(catDS, "Categories");


nwindConn.Close();
Microsoft Access does not support stored procedures or
batch command processing, so it is not possible to map an
output parameter to the source column in the table in the
preceding example. However, Microsoft Access 2000 or later
does support the @@IDENTITY property to retrieve the value
of an Autonumber field after an INSERT. Using the
RowUpdated event, you can determine if an INSERT has
occurred, retrieve the latest @@IDENTITY value, and place
that in the identity column of the local table in the
DataSet.


The following code example inserts a new value into the
Categories table of the Microsoft Access 2000 Northwind
database. The example uses the RowUpdated event to fill in
the Autonumber values generated by the Jet engine and the
Access database when a record is inserted into the
Categories table. Note that this will only work with the
Jet 4.0 OLE DB provider and Microsoft Access 2000 or later.


[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic


Public class Sample


Shared nwindConn As OleDbConnection = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\Program Files\Microsoft
Office\Office\Samples\northwind.mdb;")


Public Shared Sub Main()


' Use the DataAdapter to fill and update the DataSet.
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter
("SELECT CategoryID, CategoryName FROM Categories ORDER BY
CategoryID", nwindConn)


catDA.InsertCommand = New OleDbCommand("INSERT INTO
Categories (CategoryName) Values(?)", nwindConn)
catDA.InsertCommand.CommandType = CommandType.Text


catDA.InsertCommand.Parameters.Add("@CategoryName",
OleDbType.Char, 15, "CategoryName")


nwindConn.Open()


' Fill the DataSet.
Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")


' Add a new row.
Dim newRow As DataRow = catDS.Tables
("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)


' Include an event to fill in the Autonumber value.
AddHandler catDA.RowUpdated, New
OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)


' Update the DataSet.
catDA.Update(catDS, "Categories")


nwindConn.Close()
End Sub


Private Shared Sub OnRowUpdated(sender As Object, args
As OleDbRowUpdatedEventArgs)
' Include a variable and a command to retrieve the
identity value from the Access database.
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT
@@IDENTITY", nwindConn)


If args.StatementType = StatementType.Insert
' Retrieve the identity value and store it in the
CategoryID column.
newID = CInt(idCMD.ExecuteScalar())
args.Row("CategoryID") = newID
End If
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;


public class Sample
{
static OleDbConnection nwindConn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data
Source=c:\Program Files\Microsoft
Office\Office\Samples\northwind.mdb;");


public static void Main()
{
// Use the DataAdapter to fill and update the DataSet.
OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT
CategoryID, CategoryName FROM Categories ORDER BY
CategoryID", nwindConn);


catDA.InsertCommand = new OleDbCommand("INSERT INTO
Categories (CategoryName) Values(?)", nwindConn);
catDA.InsertCommand.CommandType = CommandType.Text;


catDA.InsertCommand.Parameters.Add("@CategoryName",
OleDbType.Char, 15, "CategoryName");


nwindConn.Open();


// Fill the DataSet.
DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");


// Add a new row.
DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);


// Include an event to fill in the Autonumber value.
catDA.RowUpdated += new OleDbRowUpdatedEventHandler
(OnRowUpdated);


// Update the DataSet.
catDA.Update(catDS, "Categories");


nwindConn.Close();
}


protected static void OnRowUpdated(object sender,
OleDbRowUpdatedEventArgs args)
{
// Include a variable and a command to retrieve the
identity value from the Access database.
int newID = 0;
OleDbCommand idCMD = new OleDbCommand("SELECT
@@IDENTITY", nwindConn);


if (args.StatementType == StatementType.Insert)
{
// Retrieve the identity value and store it in the
CategoryID column.
newID = (int)idCMD.ExecuteScalar();
args.Row["CategoryID"] = newID;
}
}


}


See Also
Sample ADO.NET Scenarios | Accessing Data with ADO.NET |
Using .NET Data Providers to Access Data
Click to expand...
 
William:

As I agree with you whole hartedly on that one, I still feel that hardware
and networking is moving tward providing back end support where that won't
be an issue. Also, Many experienced people who were slow to jump on the .NET
wagon mostly because they didn't want to learn the new environment. Many
also because they didn't want to deal with the lack of server side cursors
which I felt were frustrating at times anyway. Also because of how long it
took for COM to do it's garbage collection. Nothing I hated more than
getting up in morning after a long night of finaly getting something to work
only to see it explode before my eyes.

Anyways, I'm having a hard time with this whole concept of disconnected,
connected, statefull, stateless, data binding.

I'm only guessing that statefull is better for windows apps where the data
source is on a local network and stateless is better for web apps.. Thats
one of my questions. Also, I can't find a VB.NET example of a Data Access
Layer built for a windows app where SQL Server is on the local network. Are
DAL's better suited for web apps? Can you give us a pointer to an article
that clearly gives examples for both stateless and statfull DAL's for
windows apps(If one exists) that also shows how to bind the controle's to
the fileds in the datatable? Getting autonumber from parent seems to be an
often asked question. The solution seems simple for someone witrh maybe a
Customers -- Orders -- OrderDetails table. For me However, with one parent,
26 children and 12 lookup tables, well, you see? this leads to my next
point.

One quandrey for me is that MS advertised this new dev environment as with
all these new features for setting up relational data visually. Many people
went out and spent money(Paid someone to go out in the rain and get the cat)
only to find that people strongly recomend anything other than the use of
these new features. So, do I want to hard code my paticular app? No, I want
to get upset with Cor when he points me to an example that misses my needs
by one or two topics that I realy need help with.

Thanks

Bob
 
It's Bill...;)
I hear ya. I'm writing a new book that's rich with examples for the
Windows Forms client developer. Even without server-side cursors you can
build a performant app--you just have to do more of the work yourself. They
made an attempt to get server-side cursors in Whidbey, they'll try again
with Orcas. If there are situations where you really need a Server Side
cursors (SSC), you can build one with the ANSI CREATE CURSOR SQL commands.
It's also a lot of work, but you get to manage server-side state yourself. I
wrote an article on this for someone awhile back. It's on my site.
Those who've been around for awhile are used to moving to the new
platforms every 5-10 years or so (and more often it seems of late). Frankly,
the should have switched a lot sooner but the OS people weren't ready (and
Office is still not there)--perhaps next year. Longhorn was supposed to be
the universal platform but it's still getting tuned up.
Stateful design can be better for Windows Forms applications but it also
limits scalability--or so they say. Frankly, most of the people I talk to
don't find it necessary to scale to 5000 users--most will settle for several
hundred. Statefull design simply doesn't make sense for ASP. I'm not a fan
of DALs in a universal sense. Layers for the sake of layers does not do
anything but make the code more expensive. If it makes sense to segment the
layers for your application, do it. It often helps segregate the "data"
people/skills from the "UI" people. I don't spend much time now-a-days
working with ASP architectures. There are already too many "experts" in this
field--and most are smarter than I. Rob MacDonald's book on ASP.NET has
proven helpful for me. I did write an article on managing the Identity
Crisis (see my web site) that deals with hierarchical identity issues.
I'm also one of those that really want the new features to meet my (and
my reader's) needs. They often fall short as it seems they're designed for
fairly simple scenarios. Once you start getting complex having to go back to
the IDE and re-drag and drop can get to be a pain. This brittleness is
helped a bit with the new Partial Class concept (it's about time they did
this) so your modifications to the generated code is not arbitrarily
overlaid. This Winter I'll be digging into these development paradigms more
fully. I've saved these parts 'till last as they seemed the most fragile and
I wanted to give MS every chance to get it right before I started a critical
review in my book.
I'm doing an ADO.NET workshop that might help this time next month (if
there is space left) at VSLive Orlando and possibly Sydney (not settled
yet). Hope to see you there--and don't bring your wet cat. ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
But really no one has answered my question as far. I am moving to
ADO.NET from ADO. And i just want to do the same thing i was doig in
ADO, but using the new technology. New technologies should be able to
do what Old technology did (easily), and even do it better (Am I wrong
here?). THEY SAID THAT .NET WILL CUT OUR DEVELOPMENT TIME. SO, I WANT
TO SEE THAT.

I have found a partial solution using ExecuteScalar.

CmdConn.ExecuteScalar("INSERT INTO tblCustomers(Name)VALUES('j. smith')
; SELECT @@IDENTITY")

But when I am doing my inserts, i don't usually use execute. I do
DataAdapter.Update( Dataset );

In ADO, I did this easily:

recordSet rs
rs.AddNew
rs("Name")="J. Smith"
rs.UpdateBatch()
NewID = rs("ID").value

there you go. 3 lines of code, and you are there. Now, I don't mean to
say that ADO.NET is bad. I think it's better. I just need to learn the
new way to do the same thing. As I am migrating my code, I usually do
this:

SQLDataAdapter da
Dataset ds
Datarow dr = ds.Tables(0).NewRow
dr("Name")="J. Smith"
da.Update(ds)
NewID = dr("ID") <-- Unlike in ADO, this one contains NOTHING.

so, I need some pointers as to how i could accomplish this... Is my
question clear ???
 
Bill:
I hear ya. I'm writing a new book that's rich with examples for the
Windows Forms client developer. Even without server-side cursors you can
build a performant app--you just have to do more of the work yourself.
Click to expand...

I'll certainly have to checkout the book when it comes out. I'm ok without
the server side cursores(I think). My app will have no more than 5 users but
thats not say I wouldn't want to scale it to something that would give
multiple offices access.

I have seen the article. It got me to look over the VB generated code in the
data access class and learn a little more about whats going on in the
basement. When I see others recomend using GUID's, it kinda makes me want to
yak up my insides and go back to VB 6. Whats another round trip to the DB
compared to GUID's? I've seen how apps with GUID's in the key fields perform
when you get past 100,000 records and 25 users.
I'm not a fan of DALs in a universal sense. Layers for the sake of layers
does not do anything but make the code more expensive.
Click to expand...

Yeah I agree and I can't see how they would be transportable to other apps
either as everyones needs are unique. I can see me always going back to it
and changing something that would cascade through the whole app source.
I'm also one of those that really want the new features to meet my (and
my reader's) needs. They often fall short as it seems they're designed for
fairly simple scenarios. Once you start getting complex having to go back
to the IDE and re-drag and drop can get to be a pain. This brittleness is
helped a bit with the new Partial Class concept (it's about time they did
this) so your modifications to the generated code is not arbitrarily
overlaid.
Click to expand...

Oh man !! Your not kidding. Nothing like starting all over again or
pouring over the class to remove what VS doesn't when you make one little
change. Kinda reminds me of DrumBeat.but without the messy source.
I'm doing an ADO.NET workshop that might help this time next month (if
there is space left) at VSLive Orlando and possibly Sydney (not settled
yet). Hope to see you there--and don't bring your wet cat. ;)
Click to expand...

I wish I could make it. I've yet to attend any workshops. Hopefuly someday.
For now, I have to stick with the books and ask questions here and hope to
be clear enough in my questions to get the answer I need.

The cat hates me now because I still have'nt found a way to get someone to
go out in the rain and get him.

Cya someday

Bob
 
One of the principle complaints about ADO classic was how it generated DML
action commands at runtime. While this approach worked for several simple
situations, it fell apart quickly when you stepped over the line. While ADOc
gave you several options to determine how concurrency checks were made,
developers found that they could not use the Update method because of other
limitations.

ADO.NET on the other hand is totally agnostic of how you have built your
tables and relationships or how you want to perform concurrency checks. This
means that all of the code auto-generated by ADOc has to be either coded by
hand in ADO.NET at design time or you have to depend on the CommandBuilder
which has fewer options than ADOc. Many developers eventually migrate to
creating the DML UpdateCommand, InsertCommand and DeleteCommand SQL on their
own.

In your example, you'll need to tell ADO.NET that the column in question is
autoincrementing unless you populated the schema from a database table that
already has this attribute set. When the new row is created, ADO.NET
supplies a value. However, post Update, you're right, you need to fetch the
current value from the server (or DBMS like JET). Each DBMS has it's own
technique--I discuss these in my article on "Managing an @@Identity Crisis"
(see http://www.betav.com/msdn_magazine.htm).

My book "ADO.NET and ADO Examples and Best Practices" discusses these issues
in detail.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top