Hai Fireinds,
i ve the page like empdetails.apsx
in these i made 2 panels
I)empdeatils shows the details of
(username,age,department,employeecode) 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',16,1)
RETURN
END
insert
into
onward_journey(departuredate,from_location,to_location,metro,trans_all,mode_of_travel,seat_type,no_of_days,other_details,status_id,request_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?
Loading
Iftikar HussainPosted Jul 11, 2013, 3:00 AM
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',16,1)
RETURN
END
insert into onward_journey(departuredate,from_location,to_location,metro,trans_all,mode_of_travel,seat_type,no_of_days,other_details,status_id,request_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 HussainPosted Jul 11, 2013, 3:29 AM
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 RockyPosted Jul 11, 2013, 3:20 AM
Can You provide any link for above my requirement.
Iftikar HussainPosted Jul 11, 2013, 3:16 AM
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 RockyPosted Jul 11, 2013, 3:10 AM
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 RockyPosted Jul 11, 2013, 2:46 AM
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 LenkaPosted Jul 11, 2013, 2:24 AM
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',16,1)
-- RETURN
END
ELSE
BEGIN
insert into onward_journey(departuredate,from_location,to_location,metro,trans_all,mode_of_travel,seat_type,no_of_days,other_details,status_id,request_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 HussainPosted Jul 11, 2013, 2:15 AM
Can you share your table design with PK and FK?
Regards,
Iftikar