Getting the primary keyy

I'm inserting a sort of data from a formview into the database. The primary key is set to increment automatically.

After the insertion I'm trying to get the primary key in order to use in another formview (same page) to insert data on different table.

The code:

asp:SqlDataSourceID="newSchemaSqlDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:logprocConnectionString1 %>"

InsertCommand="INSERT INTO [LogSchema] ([Title], [Type], [InitPattern], [EndPattern], [SetupDate]) VALUES (@.Title, @.Type, @.InitPattern, @.EndPattern, @.SetupDate); SELECT @.NewID = SCOPE_IDENTITY()" OnInserted="newSchemaSqlDataSource_Inserted">

<InsertParameters>

<asp:ParameterName="Title"Type="String"/>

<asp:ParameterName="Type"Type="String"/>

<asp:ParameterName="InitPattern"Type="String"/>

<asp:ParameterName="EndPattern"Type="String"/>

<asp:ParameterName="SetupDate"Type="DateTime"/>

<asp:ParameterName="NewID"Type="Int32"/>

</InsertParameters>

Backend:

protectedvoid newSchemaSqlDataSource_Inserted(object sender,SqlDataSourceStatusEventArgs e)

{

int newid = (int)e.Command.Parameters["@.NewID"].Value;

Response.Write(newid.ToString());

}

The problem is the e.Command.Parameters["@.NewID"].Value is returning NULL as I could see at the debug. What am I missing?

Try setting the Direction property of your "NewID" Parameter to "Output".

<asp:parameter direction="Output" name="NewID" type="Int32" />

|||Thanks a lot... it works!!!!

Getting the id generated by SQL Server for a new record

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
On Nov 29, 10:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.
|||On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.
|||On 30 Nov., 07:18, Dan Gartner <dgart...@.gmail.com> wrote:
> On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> You would want to return the SCOPE_IDENTITY() value as an output
You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @.@.IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.
Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @.@.identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback
Results:
1
1
3
3
id xyz
-- --
1 w
2 x
3 y
4 z
(4 Zeile(n) betroffen)
|||Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @.@.IDENTITY to return the last inserted indentity value.
--
Thanks,
Ibrahim
Software Consultant - Web Development, GB
"Nathan Sokalski" wrote:

> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokalski@.hotmail.com
> http://www.nathansokalski.com/
>
>
|||This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...[vbcol=seagreen]
> Hi
> You can use the output parameter of the stored procedure and return the
> value that is being inserted. If you are using identity column then you
> can
> use @.@.IDENTITY to return the last inserted indentity value.
> --
> --
> Thanks,
> Ibrahim
> Software Consultant - Web Development, GB
>
> "Nathan Sokalski" wrote:
|||Kevin,
Sure you can, like this:
cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
Then:
ID = cmd.ExecuteScalar
Kerry Moorman
"TheSQLGuru" wrote:

> This method obviously requires using stored procs (which is almost always a
> good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
> bit of a bind I think. IIRC you can't issue an insert statement and get a
> select back out in a single Execute... type command.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
> message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...
>
>
|||I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.
One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:D12AFECB-DC31-4CE3-A2A2-14F3AB3CD95D@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Sure you can, like this:
> cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
> (@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
> Then:
> ID = cmd.ExecuteScalar
> Kerry Moorman
>
> "TheSQLGuru" wrote:
|||Kevin,
ExecuteScalar returns the first column of the first row in the result set
returned by the query.
Kerry Moorman
"TheSQLGuru" wrote:

> One additional question since I am not an ADO guru. Does the Select
> Scope_identity() not return a single-column single-row result set, which the
> executescalar isn't expecting?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
|||I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:6AD6FF28-692A-4DFA-9BCB-7D8E9A2302C1@.microsoft.com...
> Kevin,
> ExecuteScalar returns the first column of the first row in the result set
> returned by the query.
> Kerry Moorman
>
> "TheSQLGuru" wrote:
>

Getting the automatically generated id after SqlDataSource.Insert()

Hello!

I have a SqlDataSource that inserts some data in a database. The field "id" is auto-increment. Is it possible to use the "id", this data-row got from the database automatically, directly after the SqlDataSource.Insert() command in my CodeBehind file?

Thank you!

You would need to append "SELECT SCOPE_IDENTITY()" to your insert command then retrieve the value in the Inserted event.

More info here (note, this shows a pure ADO.NET approach, but my be of use): http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

|||

Take a look at this FANTASTIC tutorial:

http://www.asp.net/learn/data-access/tutorial-01-cs.aspx

This tutorial has helped me a lot and I am using it now to build an application for our company.

Here you will see detailed instructions on how to create different queries and retrieve the id usingSELECT SCOPE_IDENTITY().

|||

Ah thank you!

I added the "SELECT SCOPE_IDENTITY"-statement at the end of my insertcommand. But if I try

int id = SqlDataSource1.Insert();

it returns me only "1", the number of affected rows.

 How can I tell him to return me the result from the SELECT SCOPE_IDENTITY-statement? 

|||I have never tried using the SqlDataSource to do this kind of thing. It is limited in it's useage. You may have uncovered another limit. You can try changing the insert command to a stored procedure which returns an output parameter, or add an output parameter to the current collection of InsertCommand parameters, or use plain ADO.NET.|||

Folow the instructions bellow and you will get what you need:

1- Create your sql connection statement:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);

2 - Create your insert and select statement together:

SqlCommand newCmd = new SqlCommand("INSERT INTO Orders (FirstName,LastName,Email)" + "Values (@.FirstName,@.LastName,@.Email)" + "SELECT SCOPE_IDENTITY()", conn);

3 - Define your parameters

newCmd.Parameters.Add("FirstName", SqlDbType.NChar, 50).Value = cFirstNTB.Text;

newCmd.Parameters.Add("LastName", SqlDbType.NChar, 50).Value = cLastNTB.Text;

newCmd.Parameters.Add("Email", SqlDbType.NVarChar, 50).Value = cEmailTB.Text;

4 - Then you perform insert/select and get your scope identity:

conn.Open();

newCmd.ExecuteNonQuery();

decimal ThisID =0;

SqlDataReader ServiceReader = newCmd.ExecuteReader();

while (ServiceReader.Read())

{

ThisID = (decimal)ServiceReader.GetValue(0);

}

ServiceReader.Close();

string ThisID = newLeadCmd.ExecuteScalar().ToString();

newLeadconn.Close();

Now, Istrongly advise you to start using Strongly-typed data sets. Take a look at the tutorial in the link I posted before. It will help you do some serious coding.

Take care!

|||

The SqlDataSource's insert internally does a ExecuteNonQuery. Unfortunately, that means that it will throw away the resultset generated by your SELECT SCOPE_IDENTITY().

The easiest way to work around this is to use a parameter value. Add an output parameter to your SqlDatasource's Insert Parameters declaritively. Then add this to the end of your insert command: " SET @.NewParameter=SCOPE_IDENTITY();" Then in the SqlDatasource_Inserted event, pick up the contents of the NewParameter parameter and do what you want with it (possibly setting a class/form level variable).

How to add an output parameter:

Select your SqlDatasource control. In the properties window, find the InsertQuery property and select the "..." button to call up the Command and Parameter editor. Click the Add property button. Click the show advanced properties button. Change the Direction property to "Output". Change the Type to "Int32".

How to retrieve the contents in the SqlDatasource_Inserted event:

Private LastInsertID as integer

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Inserted

LastInsertID = e.Command.Parameters("@.NewParameter").Value

EndSub

Now you can either do what you want to do after an insert in your SqlDataSource_Inserted event, or if you need to manually call the insert method of the SqlDatasource you can do something like the following:

SqlDatasource1.Insert()

label1.text=LastInsertID.ToString()

|||

Thank you for your replies!

@.Motley:

That looks good, but I have a little problem with implementing your code. If I place

 protectedvoid SqlDataSource2_On_Inserted(object sender, SqlDataSourceStatusEventArgs e) { newID = (int)e.Command.Parameters["newID"].Value; }

in my CodeBehind file, the newID is always zero. I guess, the problem is, that the name of this method might be wrong.

How do I have to name this method correctly?

Thank you!

|||

Yes you do. If you go to your page in Design view and select the SqlDataSource, hit F4 and you will get the properties window. There is a lightning bolt there. Click that and you will be presented with a list of the control's events. Double click the Inserted event, and it will create an event handler automatically in your code-behind.

|||

It works! Great! Thank you all very much!