May 23, 2013 at 12:15 am
Hai Friends ,
I m creating one web application in that input fileds are
departuredate,from_place,To_place,travel mode.
My condition for insertion
when i choose to enter departuredate(input date) is always greater than already appeared date on database date.
create table journey
(
departuredate datetime,
from_place varchar(50),
to_place varchar(50),
travel mode nvarchar(50)
)
insert into journey values ('20-05-2013','cdsfs','dhf','Train')
insert into journey values ('21-05-2013','cds','dh','Car')
insert into journey values ('22-05-2013','cfs','df','Bus')
My procedurecode:
create procedure jack
(
@departuredate datetime,
@from_place varchar(50),
@to_place varchar(50),
@travelmode nvarchar(10)
)
as
begin
if exists('select departuredate from journey where departuredate<@departuredate')
print 'Must choose greater date of departuredate'
end
else
begin
insert into journey (departuredate,from_place,to_place,travel mode) values(@depaturedate,@from_place,
@to_place ,
@travelmode,
)
end
end
/
these query shows exceuted successfully,but it was not woking any body suggest me
May 23, 2013 at 12:21 am
What is the problem I see here is your statement to check the Departuredate is wrong, from the below query it will return the rows where departuredate is less the supplied departuredate.
select departuredate from journey where departuredate < @departuredate
try this replacing your above line with the below code
declare @MaxDate datetime
SELECT @MaxDate = MAX(DepartureDate) FROM journey
IF(@MaxDate > @Departuredate)
Your Message for departuredate less then
May 23, 2013 at 12:32 am
Hai friend,
I need the output of
insert into journey values('20-05-2013','bankok','London','Air')
insert into journey values('21-05-2013','London','USA','Air')
insert into journey values('22-05-2013','USA','London','Air')
these insertion ll execeuted successfully.
now i m trying insert
insert into journey values('10-05-2013','bankok','London','Air')
these date ll show error message.
least date should not be taken for insertion.
May 23, 2013 at 12:35 am
Change the below condition
if exists(select departuredate from journey where departuredate<@departuredate)
to
if exists(select departuredate from journey where departuredate>@departuredate)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 23, 2013 at 12:42 am
Bhaskar.Shetty (5/23/2013)
What is the problem I see here is your statement to check the Departuredate is wrong, from the below query it will return the rows where departuredate is less the supplied departuredate.
select departuredate from journey where departuredate < @departuredatetry this replacing your above line with the below code
declare @MaxDate datetime
SELECT @MaxDate = MAX(DepartureDate) FROM journey
IF(@MaxDate > @Departuredate)
Your Message for departuredate less then
Check this...
CREATE TABLE journey (DepartDate datetime, FromCountry varchar(50), ToCountry varchar(50), mode varchar(50))
GO
CREATE PROCEDURE Insert_Journey(@DeptDate datetime, @FromCountry varchar(50), @ToCountry varchar(50), @Mode varchar(50))
AS
BEGIN
DECLARE @MaxDate datetime
SELECT @MaxDate = MAX(DepartDate) FROM journey
IF(@MaxDate > @DeptDate)
BEGIN
RAISERROR('Your error message for departuredate should be greater then maxdate',16,1)
RETURN
END
INSERT INTO journey VALUES (@DeptDate, @FromCountry, @ToCountry, @Mode)
END
GO
EXEC Insert_Journey '05/20/2013','bankok','London','Air'
EXEC Insert_Journey '05/21/2013','London','USA','Air'
EXEC Insert_Journey '05/22/2013','bankok','London','Air'
--- Below Insertion line will fire error as the departuredate is less then already departureDate From Table
EXEC Insert_Journey '05/10/2013','bankok','London','Air'
May 23, 2013 at 4:23 am
Hai BhaskarShetty,
Your code Was working When i was making new entry it wont accepted on asp.background.
the procedure wont allow first entry?
May 23, 2013 at 4:44 am
raghuldrag (5/23/2013)
Hai BhaskarShetty,the procedure wont allow first entry?
It wont allow entry with the date less than 23/05/2013 as we are checking max departure date in the procedure
May 23, 2013 at 4:49 am
hai ,
I m deleted entire entries and make the new entry via asp.net it wont be work
May 23, 2013 at 4:56 am
raghuldrag (5/23/2013)
hai ,I m deleted entire entries and make the new entry via asp.net it wont be work
What do you mean by "it wont be work"?
Does it give any error message?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 23, 2013 at 4:58 am
its simple do nothing while i making new entry.
May 23, 2013 at 5:04 am
raghuldrag (5/23/2013)
its simple do nothing while i making new entry.
Try calling the procedure by passing parameters as Bhaskar had suggested earlier and let us know if the row gets inserted
EXEC Insert_Journey '05/20/2013','bankok','London','Air'
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 1:15 pm
try this - there is a syntax error
create procedure jack
(
@departuredate datetime,
@from_place varchar(50),
@to_place varchar(50),
@travelmode nvarchar(10)
)
as
if exists('select departuredate from journey where departuredate<@departuredate')
begin
print 'Must choose greater date of departuredate'
end
else
begin
insert into journey (departuredate,from_place,to_place,travel mode) values(@depaturedate,@from_place,
@to_place ,
@travelmode,
)
end
go
July 25, 2013 at 11:56 pm
Hai Bhasker,
if suppose i ve the request_id FK column depends travel_request table.
create table travel_request
(
request_id int identity PK
)
create table onward_journey
(
onward_journey_id int identity,
request_id int FK references travel_request(request_id),
departuredate datetime,
from_place varchar(10),
to_place varchar(10),
travel_mode varchar(10)
)
depends on above critirea i made procedure like these
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=IDENT_CURRENT ('travel_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',IDENT_CURRENT ('travel_request'))
--set @request= IDENT_CURRENT ('travel_request')
--return @request
end
is these correct ah?
how to that?
July 26, 2013 at 7:54 am
Your code seems to have some concurrency issues. You are using IDENT_CURRENT to determine which value to get for a DepartureDate. How can you know that nobody else has inserted into travel_request before this proc runs? I think you need to revisit your procedure. I would be willing to help but you need to try to explain more clearly what it is you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply