Click here to Skip to main content
11,644,286 members (67,623 online)
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 OriginalGriff 610
1 jyo.net 484
2 Afzaal Ahmad Zeeshan 453
3 Sergey Alexandrovich Kryukov 369
4 CPallini 352
0 OriginalGriff 925
1 DamithSL 636
2 Afzaal Ahmad Zeeshan 622
3 Sergey Alexandrovich Kryukov 548
4 CPallini 521


Advertise | Privacy | Mobile
Web03 | 2.8.150731.1 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2015
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