How do I search booking existing in date range?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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

Posted 20-May-16 0:33am

Sumitgiri M. Goswami

Updated 20-May-16 8:26am

Add a Solution

Comments

ZurdoDev 20-May-16 7:49am

Write the query in sql first and then convert it into VB.

Richard Deeming 20-May-16 9:25am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

2 solutions

Solution 1

try this

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

Posted 20-May-16 8:07am

Rohit Topiya

Comments

Richard Deeming 20-May-16 14:19pm

Assuming datetimePicker1 is the start date of the new booking, and DatetimePicker2 is the end date, that's only going to pick up bookings that completely encompass the new booking. It won't pick up bookings that overlap the start or end of the new booking.

Also, you have copied the SQL Injection[^] vulnerability from the question.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2016-05-20T08:25:00

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.

VB.NET

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. :)