what was problem in my code?

Question

Hai Fireinds i ve the page like empdetails.apsx in these i made 2 panels I)empdeatils shows the details of (usernameagedepartmentemployeecode) those thinks automatically shoe from database depends on the user Login. travel purpose:textbox total amount:textbox if the person enter the text on purpose of travel request_id would be generated. II)travel details show the information of departuredate:textbox from_place:textbox to_place:textbox mode :textbox add{button) after given the add button entire details ll show on gridview table here the problem is depends on the request_id panel-II details ll be entered so i made the query like these............ MY procedure: =============== alter procedure Insert_Journey ( @departuredate datetime @from_location varchar(50) @to_location varchar(50) @metro nvarchar(50) @trans_all nvarchar(50) @mode_of_travel nvarchar(50) @seat_type nvarchar(50) @no_of_days int @other_details varchar(50) @status_id int ) as BEGIN DECLARE @MaxDate datetime @request int SELECT @request = MAX(request_id) from travel_request a inner join users b on a.user_id=b.user_id SELECT @MaxDate = MAX(DepartureDate) FROM onward_journey where request_id=@request IF(@MaxDate > @departuredate) begin RAISERROR('Your error message for departuredate should be greater then maxdate'161) RETURN END insert into onward_journey(departuredatefrom_locationto_locationmetrotrans_allmode_of_travelseat_typeno_of_daysother_detailsstatus_idrequest_id) values(@departuredate@from_location@to_location@metro@trans_all@mode_of_travel@seat_type@no_of_days@other_details'2'@request) end when i choose the add panel-II details not inserted ....... what was error?

Iftikar Hussain · Answer

Hi Since request_id is FK in your on so you need to pass the value in your SP. Since you know you are inserting the journey details for the same request id alter procedure Insert_Journey ( @departuredate datetime @from_location varchar(50) @to_location varchar(50) @metro nvarchar(50) @trans_all nvarchar(50) @mode_of_travel nvarchar(50) @seat_type nvarchar(50) @no_of_days int @other_details varchar(50) @status_id int @request int ) as BEGIN DECLARE @MaxDate datetime SELECT @MaxDate = MAX(DepartureDate) FROM onward_journey where request_id=@request IF(@MaxDate > @departuredate) begin RAISERROR('Your error message for departuredate should be greater then maxdate'161) RETURN END insert into onward_journey(departuredatefrom_locationto_locationmetrotrans_allmode_of_travelseat_typeno_of_daysother_detailsstatus_idrequest_id) values(@departuredate@from_location@to_location@metro@trans_all@mode_of_travel@seat_type@no_of_days@other_details'2'@request) end Regards Iftikar

Iftikar Hussain · Answer

you can refer the below link http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ Regards Iftikar

Rocky Rocky · Answer

Hai Iftikar Can You provide any link for above my requirement.

Iftikar Hussain · Answer

From panel-1 after entering the details the request id is generated. So after inserting the record read the last identity value of request_id column as like below SELECT IDENT_CURRENT(' travel_request ') Then you will have request_id in your panel-1 You can provide your panel-I code for inserting the travel request details Regards Iftikar

Rocky Rocky · Answer

hai Iftikar i ve one doubt in that here pannel-I i made the code like if i enter the travel purpose =textbox request_id ll be generated then how to get that id from it and pass through on pannel-II of onwrd_joureny.

Rocky Rocky · Answer

Hai friends My Table structure is: ====================== create table travel_request ( request_id int identity PK purpose varchar(20) total_amount varchar(20) ) create table onward_journey ( onward_journey_id int identity PK request_id int FK references travel_request(request_id) departuredate datetime from_place varchar(20) to_place varchar(10) )

Sanjeeb Lenka · Answer

try this alter procedure Insert_Journey ( @departuredate datetime @from_location varchar(50) @to_location varchar(50) @metro nvarchar(50) @trans_all nvarchar(50) @mode_of_travel nvarchar(50) @seat_type nvarchar(50) @no_of_days int @other_details varchar(50) @status_id int ) as BEGIN DECLARE @MaxDate datetime @request int SELECT @request = MAX(request_id) from travel_request a inner join users b on a.user_id=b.user_id SELECT @MaxDate = MAX(DepartureDate) FROM onward_journey where request_id=@request IF(@MaxDate > @departuredate) begin RAISERROR('Your error message for departuredate should be greater then maxdate'161) -- RETURN END ELSE BEGIN insert into onward_journey(departuredatefrom_locationto_locationmetrotrans_allmode_of_travelseat_typeno_of_daysother_detailsstatus_idrequest_id) values(@departuredate@from_location@to_location@metro@trans_all@mode_of_travel@seat_type@no_of_days@other_details'2'@request) END end 'Return' won't execute further lines of code. try to execute it in sql server its working or not. if it will not work provide your table design and front end code.

Iftikar Hussain · Answer

Hi Can you share your table design with PK and FK? Regards Iftikar