Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL
Hi,
In my table i have four fields in a table namely Item,booking description, bookeStartDate,bookedEndDate
 
now if the item field is lorry one and bookedStartDate is say 23/12/2012 4:00pm and bookedEndDate is say 23/12/2012 6:00
 
now lorry one has booked this time. so again I cant book the same lorry(ie lorry one) at the same date time. but if the item field is lorry two wants to book at the same date and time it can do.
 
how can i give sql query and how to inplement it in asp.net. Im not that great in querying.
 
Thanks
Posted 7-Dec-12 1:01am
Comments
Kschuler at 7-Dec-12 10:04am
   
Your question is very confusing. Are you asking for help with a SELECT query? Or are you asking about actually manipulating the data by adding/editing/deleting records? You gave two scenarios...using the same date and time or using a different item field....but which one are you asking us to help you use? Please explain further what you are doing and what you are asking for.
Nihal Hussain at 7-Dec-12 23:46pm
   
@Kshuler.. Im asking help for the select query.. not for particular value in item field.
ALTER PROCEDURE [dbo].[usp_valudateDate]
@fromDate datetime,
@todate datetime,
@msg varchar(50) output,
@success bit output
 

AS
BEGIN
 
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
-- Insert statements for procedure here
SELECT item,count(*)
FROM tblCalendarDetails
WHERE bookeStartDate <= @todate
AND bookeEndDate>= @fromDate
if (count(*) > 0)
begin
set @success=0
set @msg='datetime already scheduled. enter new dates'
end
else
begin
set @success=1
set @msg='datetime entered successfully'
end
 
Tried this query.. its not working

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You need to revisit your checking condition or the query you are running before the insert statement If you are using any validation prior to the insert statement.
 
were you forget to add the dates to your select query?
 
I believe your verification query should be something like below
 
select count(*) from your_table where item='lorry' and bookedEndDate ='enddate' and bookedStartDate = 'startDate'
 
or
 
select count(*) from your_table where item='lorry' and bookedStartDate >= 'startDate' and bookedEndDate <='enddate'
 
try to include the time also.
You may also refer the following link.
http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm[^]
  Permalink  
Comments
Nihal Hussain at 7-Dec-12 23:56pm
   
@jibesh. I dont want to mention the value of the item. it should be for any duplicate item. eg lorry one with the same booking start date time and booking enddate time should not be there twice.. but lorry two can book at the same date and time. Hope you got my point. similarly for other cases also. I have given my query in the previous comments. please go through that.
jibesh at 11-Dec-12 20:29pm
   
I checked your query. what do you mean it's not working. it would be better if you specify the real nature of the problem rather others guessing what went wrong. having a detailed problem scenario will save all of our time.
 
I don't see any error with your query but count(*) will return a value if you try to insert for another item i.e lorry2 is that correct?
 
this query will restrict the second insertion of data which matches the bookstart and bookend dates. If you don't want any restrictions just skip the checking it will insert all the dates and lorries you entered.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 746
1 OriginalGriff 345
2 Magic Wonder 131
3 Kishore Pogaru 130
4 Fredrik Bornander 130
0 OriginalGriff 5,705
1 Sergey Alexandrovich Kryukov 4,763
2 CPallini 4,570
3 George Jonsson 3,142
4 Gihan Liyanage 2,450


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100