Retrieving the autoincremented primary key value

I am using several TextBox Controls, instead of a FormView, for inserting data into a Sql Database. The primary key (ID) is an integer which is automatically incremented by one at each insertion.
At each insertion I need to retrieve the ID value of the newly inserted record.
I have followed a suggestion from a help sample with this code:

Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data
Partial Class Insert
Inherits System.Web.UI.Page

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click

SqlDataSource1.Insert()
End Sub

Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
Dim InsertedKey As SqlParameter
InsertedKey = New SqlParameter("@.PK_GuestList", SqlDbType.Int)
InsertedKey.Direction = ParameterDirection.Output
e.Command.Parameters.Add(InsertedKey)
End Sub

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command
Label1.Text = command.Parameters("@.PK_GuestList").Value.ToString()
End Sub

End Class

No output appears on the Label1.
If in the last code row I replace "@.PK_GuestList" with the name of any TextBox used for inputting data, its content is correctly shown in Label1.

Where is the problem?

You need to use Select Scope_Identity() in your SQL to get the ID that was just created. Without seeing your SQL, it's impossible to tell if you are doing this correctly.|||

Thanks Mike.

This is my Sql:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [GuestList] WHERE [ID] = @.ID"InsertCommand="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost)"

OnInserting="On_Inserting"OnInserted="On_Inserted"SelectCommand="SELECT * FROM [GuestList]"UpdateCommand="UPDATE [GuestList] SET [IDD] = @.IDD, [UserName] = @.UserName, = @.Email, [Title] = @.Title, [Message] = @.Message, [Reason] = @.Reason, [Accento] = @.Accento, [Views] = @.Views, [Posts] = @.Posts, [Quando] = @.Quando, [LastPost] = @.LastPost WHERE [ID] = @.ID">

<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="IDD"Type="Int32"/><asp:ParameterName="UserName"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="Title"Type="String"/><asp:ParameterName="Message"Type="String"/><asp:ParameterName="Reason"Type="String"/><asp:ParameterName="Accento"Type="String"/><asp:ParameterName="Views"Type="String"/><asp:ParameterName="Posts"Type="String"/><asp:ParameterName="Quando"Type="DateTime"/><asp:ParameterName="LastPost"Type="String"/><asp:ParameterName="ID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ID"Type="Int32"/><asp:formparametername="IDD"formfield="IDD"/><asp:formparametername="UserName"formfield="UserName"/><asp:formparametername="Email"formfield="Email"/><asp:formparametername="Title"formfield="Title"/><asp:formparametername="Message"formfield="Message"/><asp:formparametername="Reason"formfield="Reason"/><asp:formparametername="Accento"formfield="Accento"/><asp:formparametername="Views"formfield="Views"/><asp:formparametername="Posts"formfield="Posts"/><asp:formparametername="Quando"formfield="Quando"/><asp:formparametername="LastPost"formfield="LastPost"/>

</InsertParameters>|||May be I missunderstood. I have written no SQL code.|||

nodar10:

May be I missunderstood. I have written no SQL code.

Yes you have, on both counts. The InsertCommand, SelectCommand etc are SQL code. Have a look at this to see if you can work out how to get the value of Scope_Identity() in a stored procedure rather than with the SqlDataSource:http://www.eggheadcafe.com/forumpost.aspx?topicid=1&forumpostid=68123

|||

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

I have solved the problem by simply adding ;SELECT @.PK_GuestList = SCOPE_IDENTITY() at the end of the InsertCommand SQL statement , which becomes:

InsertCommand

="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost);

SELECT @.PK_GuestList= SCOPE_IDENTITY()"

PK_GuestList is an auxiliary variable present in both .aspx and .aspx.vb files, and can be assume any name, but not ID.

Thank you very mauch for your help.

Federico

|||

nodar10:

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

....

Thank you very mauch for your help.

Federico

Then I don't understand why you unmarked my post as answer.

|||

Hi Mike

Sorry for doing some action inadvertely. I just tried to set the flag that my question has been properly answered.

Federico

Retrieve Bit type value

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?You're not selecting what you think you're selecting. You will get a 1 or 0 back, not S00817. Do any of the fields in the record have that value? If you miss a comma in your field list, you can get goofy-looking, unexpected data. Also, when the bit comes back, you can feed it right into boolean properties. You don't have to change 1 into true, for example. Pretty handy. I'd run your SQL statement in Query Analyzer, some where outside of ASP. Actually, ADO might convert the 1 to -1. Might want to check that also. Check your query; something's up there I would suspect.|||Yes you were quite right. I was trying to read data from another field. I should have using Mydataset.tables(0).Rows(0)(13)

Thank you for your help