如何搜索日期范围内的预订？ [英] How do I search booking existing in date range?

问题描述

我有表名BookingRecordTransaction字段是（BookingId，PropertyCode，FacilityCode，BookedFrom，BookedTo，bookingPeriod）



新预订时我想搜索所选择的设施是已经在预定日期范围内预订，如果预订比它提示我已预订，否则需要在预定范围内预订。



例如，

Table BookingRecordTransaction有以下数据：



BookingId PropertyCode FacilityCode BookedFrom BookedTo BookingPeriod

5201610 KSC ACHAll-01 20-05-2016 25- 05-2016 5

5201612 KSC DH-01 23-05-2016 24-05-2016 2

5201612 KSC ACHAll-01 27-05-2016 28-05- 2016 2



现在用户在2016年5月23日至2016年5月27日期间新预订ACHAll-01



如何在桌上预约新的预订日期？



你能帮助我吗？



我尝试过：



我尝试过t $ / b $ b选择查询跟随

从BookingRecordTransaction中选择*，其中FacilityCode =+ ComboBox1.Text +'和BookedFrom ='+ datetimePicker1.text +'和BookedTo = '+ DatetimePicker2.Text +'



查询执行时返回没有行

如果行> 0

比它显示预订存在

其他

新记录已插入

解决方案

试试这个



从BookingRecordTransaction中选择*，其中FacilityCode =+ ComboBox1.Text +'和（BookedFrom< ='+ datetimePicker1.text +'或BookedTo> ='+ DatetimePicker2.Text +'）

首先修复 SQL注入 [ ^ ]漏洞。



然后，找到任何预订重叠新预订，检查他们是否在新预订结束前开始，并在新预订开始后结束。



您只需要选择第一个匹配的记录，并且你不需要所有的字段。

 使用 con  As  新 SqlConnection（  ...）
 使用 cmd 作为 新 SqlCommand（  SELECT TOP 1 BookingId FROM BookingRecordTransaction WHERE FacilityCode = @FacilityCode AND BookedFrom < = @EndDate And BookedTo> = @StartDate，con）
 cmd.Parameters.AddWithValue（  @ FacilityCode，ComboBox1.Text）
 cmd.Parameters.AddW ithValue（  @ StartDate，DatetimePicker1.Text）
 cmd.Parameters.AddWithValue（< span class =code-string>  @ EndDate，DatetimePicker2.Text）
 
 con.Open（）
 使用 dr 作为 SqlDataReader = cmd.ExecuteReader（CommandBehavior.SingleResult 或 CommandBehavior.SingleRow 或 CommandBehavior.CloseConnection）
 如果 dr .HasRows 然后 
 ' 存在冲突预订。 
 结束 如果 
 结束 使用 
 结束 使用 
 结束 使用 

现在，帮自己一个忙，给你的控件有意义的名字。您可能还记得 ComboBox1 意味着现在，但是当您在几个月内回到代码中时，您将会忘记。如果你给它命名为 FacilityCodeList ，它会更加明显。 ：）

I have table name BookingRecordTransaction Fields are (BookingId,PropertyCode,FacilityCode,BookedFrom,BookedTo,bookingPeriod)

while new booking i want to search that selected facility is already booked in given date range, if booked than it's prompt me "already booked" else it take booking in given range.

For Example,
Table BookingRecordTransaction has following Data:

BookingId PropertyCode FacilityCode BookedFrom BookedTo BookingPeriod
5201610 KSC ACHAll-01 20-05-2016 25-05-2016 5
5201612 KSC DH-01 23-05-2016 24-05-2016 2
5201612 KSC ACHAll-01 27-05-2016 28-05-2016 2

Now when user make new booking ACHAll-01 for the period of 23-05-2016 to 27-05-2016

How to campare the new booking date in table?

Can you help me?

What I have tried:

I tried that
Select Query as Follows
"Select * from BookingRecordTransaction where FacilityCode="+ComboBox1.Text+"' and BookedFrom='"+datetimePicker1.text+"' and BookedTo='"+DatetimePicker2.Text+"'"

when query executed it return no of rows
if Rows>0
Than it shows Booking Exist
else
New Record Inserted

解决方案

try this

"Select * from BookingRecordTransaction where FacilityCode="+ComboBox1.Text+"' and (BookedFrom <= '"+datetimePicker1.text+"' OR BookedTo >= '"+DatetimePicker2.Text+"')"

Start by fixing the SQL Injection[^] vulnerability.

Then, to find any bookings that overlap the new booking, check whether they start before the new booking ends, and end after the new booking starts.

You only need to select the first matching record, and you don't need all of the fields.

Using con As New SqlConnection("...")
    Using cmd As New SqlCommand("SELECT TOP 1 BookingId FROM BookingRecordTransaction WHERE FacilityCode = @FacilityCode AND BookedFrom <= @EndDate And BookedTo >= @StartDate", con)
        cmd.Parameters.AddWithValue("@FacilityCode", ComboBox1.Text)
        cmd.Parameters.AddWithValue("@StartDate", DatetimePicker1.Text)
        cmd.Parameters.AddWithValue("@EndDate", DatetimePicker2.Text)
        
        con.Open()
        Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)
            If dr.HasRows Then
                ' There is a conflicting booking.
            End If
        End Using
    End Using
End Using

Now, do yourself a favour and give your controls meaningful names. You might remember what ComboBox1 means now, but when you come back to your code in a couple of months, you'll have forgotten. If you give it a name like FacilityCodeList, it will be much more obvious. :)

这篇关于如何搜索日期范围内的预订？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！