I am using Microsoft Visual Studios 2005 Pro creating a web form in asp.net with a vb.net code page. I am trying to get the id of a newly inserted record. I am using the sqldatasource insert command to insert the record. Does anybody know how to return the new id. during the insert? Thanks, Any help is appreciated
there are basically 2 ways of doing that.
1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column
2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like
SET @.ID = scope_identity()
Where @.ID is your output parameter or you can return it as a return value.
|||
Here is a modified sample from Quickstarts tutorial with inline SQL for insert (ContactID is the Identity field of the table):
ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@.ContactID").Value.ToString()) +"<br/>")
EndSub
<asp:GridViewAutoGenerateColumns="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"
ID="GridView1"runat="server">
<Columns>
<asp:CommandFieldShowDeleteButton="True"/>
<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"
ReadOnly="True"SortExpression="ContactID"/>
<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>
</Columns>
</asp:GridView>
<asp:SqlDataSourceConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"ID="SqlDataSource1"
runat="server"SelectCommand="select ContactID, ContactName from Contacts"
InsertCommand="INSERT INTO Contacts (ContactName) VALUES (@.contactName);SELECT @.contactID = Scope_Identity()"
OnInserted="SqlDataSource1_Inserted"DeleteCommand="DELETE FROM Contacts WHERE ContactID = @.ContactID">
<InsertParameters>
<asp:ParameterName="contactName"Type="String"/>
<asp:ParameterDirection="Output"Name="contactID"Type="Int32"/>
</InsertParameters>
<DeleteParameters>
<asp:ParameterName="ContactID"/>
</DeleteParameters>
</asp:SqlDataSource>
<br/>
<asp:DetailsViewAutoGenerateRows="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"
DefaultMode="Insert"HeaderText="Insert New Contact"Height="50px"ID="DetailsView1"
runat="server"Width="125px">
<Fields>
<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"
ReadOnly="True"SortExpression="ContactID"/>
<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>
<asp:CommandFieldShowInsertButton="True"/>
</Fields>
</asp:DetailsView>
|||
orka777:
there are basically 2 ways of doing that.
1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column
2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like
SET @.ID = scope_identity()
Where @.ID is your output parameter or you can return it as a return value.
For Number 1, is correct but might no be that good even it will do the job for you.
For Number 2, the code should be like:
1SET @.MyIdentity =@.@.IDENTITY
Where @.MyIdentity in an output parameter of type int.
Here I prefere to not return the identity of the inserted record as an output parameter, I perefere to return it using the Return statment in the stored procedure., like:
1CREATE PROCEDURE MySP23AS45SET NOCOUNT ON67declare MyIdentityint-- as a normal variable in the stored procedure body89Insert Into MyTable (col1, col2)values ('A','B')10SET @.MyIdentity =ISNULL(@.@.IDENTITY,-1)-- if no record is inserted then you will get -1111213Return @.MyIdentity1415SET NOCOUNT OFF16
Good luck.|||
I am trying to do this through the sqldatasource1.insert statement that I have built using the querybuilder. When I try to put in the @.ID as an output or as a return value It says I need to declare the scalar variable. I have already added in the parameters area and it is set to output with the control being the textbox that I want the Id in. But when I add the line "SET @.NewID = scope_identity()" or "SET @.NewID = @.@.identity" then I get an error on both @.NewId and @.SN which is another scalar variable which normally works fine and works fine again after I pull that line out. But I'm not doing it as a stored procedure. Could that be the problem? I don't know about stored procedures. How do I create them?
|||
Carehalf:
I don't know about stored procedures. How do I create them?
Just check my last post, it is a complete running fine example.
Anyway.. here is a very simple example:
1CREATE PROCEDURE MyStoredProcedureName2@.MyInputParameterint,-- input parameter of type integer3@.MyOutputParameterint OUTPUT-- output parameter of type integer45AS67-- write you statments here8-- and here9-- and here as well :)1011-- how to set a value for ourput parameter12SET @.MyOutputParameter = 100-- just for example1314-- how to increment the input parameter by 1 (for example)15SET @.MyInputParameter = @.MyInputParameter + 1161718/* At the end you the @.MyOutputParameter output parameter value19 will be 100 (as per the logic in this simple example20 Did you see how easy it is to created a stored procedure? */21
Good luck.
|||
I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it
|||
Carehalf:
I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it
Create you stored procedures in your application/project database.
Example:
USE MyDatabase
GO
Then the CREATE PROCEDURE statment as in my last two posts.
Good luck.
|||
Carehalf:
Create a table in your database and copy the code sample I posted (change the connection string in the code) and check the syntax to work for you if you don't want to create the Stored Procedure.
Here is the table definition:
CREATETABLE [dbo].[Contacts](
[ContactID] [int]IDENTITY(1,1)NOTNULL,
[ContactName] [nvarchar](50)
)
