Help getting an ID, back from a record, that has just been inserted with a stored procedure

Hi,

I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID

This is my stored procedure:

ALTER PROCEDURE dbo.quote

 

(

@Business_Name
nvarchar(50),

@Business_Type nvarchar(50),

@Contact_Title nchar(10),

@Contact_First_Name nvarchar(50),

@Contact_Last_Name nvarchar(50),

@Address_Line_1 nvarchar(MAX),

@Address_Line_2 nvarchar(MAX),

@City nvarchar(MAX),

@Postcode nchar(7),

@Tel_No nchar(11),

@E_mail_Address nvarchar(50),

@Distributor_ID int,

@Profile_Class int,

@Meter_Time_Code int,

@Line_Loss_Factor int,

@Unique_Identifier1 int,

@Unique_Identifier2 int,

@Check_Digit int,

@Tariff nchar(20),

@UnitRate1AnnualUsage nchar(10),

@UnitRate2AnnualUsage nchar(10),

@UnitRate1SubTotal money,

@UnitRate2SubTotal money,

@QuoteTotal money

)

AS

INSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address)

 

DECLARE @Client_ID INT

SET @Client_ID = scope_identity()

 

INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal)

 

RETURN

And this is the code I have in my asp page:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

InsertCommand="quote" InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue=""

Name="Business_Name" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type"

PropertyName="SelectedValue" Type="String" />

<asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title"

PropertyName="SelectedValue" Type="String" />

<asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="LabelTariff2" Name="Tariff"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage"

PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal"

PropertyName="Text" Type="Decimal" />

<asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal"

PropertyName="Text" Type="Decimal" />

<asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal"

PropertyName="Text" Type="Decimal" />

</InsertParameters>

</asp:SqlDataSource>

And the following in the C# code:

try

{

SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure

}

catch (Exception ex)

{

LabelInsertException.Text =
"Failed" + ex.Message;

}

Any help would be much appreciated

Thanks, Hayley

 

 

0
Hayley21
3/21/2008 11:20:03 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
669 Views

Similar Articles

[PageSpeed] 37
Get it on Google Play
Get it on Apple App Store

Hi, You need to use @@IDENTITY

Declare @New_ID int

INSERT Code here...

Select @New_ID = @@IDENTITY


Regards
Smcoxon

No Gem is ever polished without some friction.
0
smcoxon
3/22/2008 12:01:06 AM

Thanks, So how would I use this with the code I have??

0
Hayley21
3/22/2008 12:17:35 AM

I'm guessing you are looking for meter_id from meter_quote

 Declare a variable @meter_id.

Like smcoxon suggested, after the insert in to meter_quote table add a statement

set @meter_id=@@identity
 

 

 


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
3/22/2008 12:27:03 AM

Sorry this is taking me a while to grasp, so I set the meter_id to @@identity.

I want to display this id in a label somewhere on the page. do I just need to set a control paramter in my asp page, or do I need to add some code in my C# code??

Thanks, Hayley

0
Hayley21
3/22/2008 12:38:47 AM

 Check this post, its very much similar to your situation. Pay attention to @@Identity and the code marked in red. Its 13th post in that thread. Hope it helps.

http://p2p.wrox.com/topic.asp?TOPIC_ID=41578 


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
3/22/2008 12:52:34 AM
Thanks, that solved by problem Smile
0
Hayley21
3/22/2008 1:13:40 AM
Reply:

Similar Artilces:

Help with getting the ID of the just inserted record
I am very much a newby and I have seen some posts on this subject, but they seem to be a little over my head, or they are using it in contexts that don't apply to my situation.  In most posts I see that adding "SELECT SCOPE_IDENTITY() to the end of your INSERT statement is the way to go. It works perfect in Query Builder. I just don't know how to get that returned value and assign it to a variable. Here is the code I am dealing with. Can someone please let me know how to make this work in my code and not just send me to more posts that I don't really understand. Als, i...

SqlDataSource with Access database: how to get back the latest inserted record Id after Inserting a new record
Hi all, Y use an Access database with a password. That´s the reason why I use the SqlDataSource. Works very good!But, I have bad luck in getting back the latest inserted record Id after Inserting a new record. I use the DetailsView. An SqlDataSource is bound to a TextBox that should represent the IdCliente.So, after inserting the Client details I want to have back the IdCliente to show the Client details inserted.I have read many of your greatfull help about getting back the latest inserted record Id after Inserting a new record.I used your good ideas: INSERT INTO [T_Clientes...

In SQL SERVER 2005, how can I get the ID of the record I just insert to table?
In SQL SERVER 2005, how can I get the ID of the record I just insert to table? I defined a table MyTable, and I insert a record into the table using the SQL below Insert into MyTable (Name) values ("User Name") You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table?   CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON...

Get ID of record you just inserted
Please advice!For example I have Datalist and  using its insertitemtemplate I insert row in a table, through insert query, through objectdatasource. How could I get (into variable for example) ID of record, which I just inserted?   i do use this methoud in sql sp, but not sure if it works in your insert template, INSERT INTO tblName(TextField, NumberField) VALUES(@Text, @Number) SELECT @@IDENTITYRETURN @@IDENTITY worth to try Muhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn Well, this work for me: I had a Stored Procedure, where I define an output var for the ID ...

SQL Help: getting the ID from an insert
I am trying to get the ID field (which is a primary key) of record just after it is inserted. In other words, you insert a record into to the table. What is the identity of the new record. In visual basic, you can do this: recset.addnew recset("Info") = "Info column" recset.update The id of the new record is: recset("id") any ideas ? hi, You can write a insert trigger for the table.In that case when you insert a record, the trigger gets fired.In the trigger you can use the logical table INSERTED to find out the newly inserted value ...

get autonumber ID of record I just inserted ???
- i have sqldatasource1 and sqldatasource2- then i do sqldatasource1.insert() - now, i want to insert a record into sqldatasource2 with the autonumber ID just inserted into sqldatasource1 is this possible?  thanks How about have Field in the table (maybe call ID) in the sqlDatasource2 database this ID have Identity Specification enable (= yes) then whenever you insert data into sqlDatasource2 , the MS SQL 2005 will insert number for you automatically  James Wu (MIB426).NET is only way to goMCP, MCSE, MCDBA, MCSD, MCAD I'm not quite sure this is what the OP ...

SQL help: Get the record I just updated
Hi -  apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried:  1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (Provid...

Stored Procedure - Getting PK from Inserted Record
I have an ASP.NET page where administrators can enter questions/answers into a SQL Server database. Every question has 4 answers and 1 category assigned to each question. Thus, there is a 1 to MANY relationship between tblQuestions and tblAnswers. I am having trouble in the SQL logic for the following stored procedure on INSERTING a question and its 4 answers. My logic jus seems very inefficient. I am also having trouble on figuring out the PK for the Question that was just submitted into tblQuestions so I can use it as a FK for tblAnswers for each Answer submitted. CREATE PROCEDURE ...

help getting parameters back from a stored procedure
Hello everyone, I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server. --I've been getting the following error in visual basic 6 when i try to call this: run-time error '-2147217900 (80040e14)': syntax access violation --This is the error you get when you try to just run the code in query analyzer: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation --what code i was trying to use (in query analyzer): {call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a'...

how to get the ID of an inserted data in a stored procedure
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure. for example: alter procedure [dbo].[AddDetails] ( @IndustryName nvarchar(50), @CompanyName nvarchar(50), @PlantName nvarchar(50), @Address nvarchar(100), @Createdby int, @CreatedOn datetime ) as begin insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn) insert into Comp...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Getting back the ID of the just inserted datarow (MSSQL)
Hi,I have an application that I need to modify. It uses datasets to store data from the database.I now need to commit a new line to the database and get back exactly that line's id. What is thebest way to guarantee that? Even if 2 users are working on the database at the same time I need to makesure that the returned ID really matches the inserted datarow.Any suggestions?RegardsSven   Select scope_identity()Community Coder Thanks perfect! :) I have a stored procedure that add to more than one table. I'd like to grad the ID of the first table, but SCOPE_IDENTITY() appears ...

Please help want to use the stored procedure to insert record to table
Please help, first time trying to use the stored procedure. Can you please modify my code below which is in asp.net to insert the record's in table(tbl_labels) using my stored procedure, i don't have problem inserting records, but i want to use my stored procedure to insert the record. Please help I never used a stored procedure. ********** CREATE PROCEDURE sp_insert_label ( @engl nvarchar, @espl nvarchar, @frlbl nvarchar, @gerlbl nvarchar ) AS INSERT INTO tbl_labels ( eng_lbl, esp_lbl, fr_lbl, ger_lbl ) VALUES ( @engl, @espl...

sql stored procedure
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )DECLARE @soNo1 INT  DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD...

Web resources about - Help getting an ID, back from a record, that has just been inserted with a stored procedure - asp.net.sql-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Why Are Web Login Security Procedures So #$*&!% Dumb And Obnoxious?
The geeks who design our websites talk a lot about user friendliness, but they often let an exaggerated security concerns get in the way of commonsense. ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Resources last updated: 12/18/2015 2:11:28 AM