Sorting by date and time. HELP

Question

Hi everybody, I'm newbie in vb.net.. And I have a Resort Reservation project... I have done sorting the cottages by date. And now I want to check the availability of cottages by date and time ...

Thank you for your reply ... :D

Call connection()

Dim dr As OleDb.OleDbDataReader
dr = cmd.ExecuteReader()

While dr.Read
            If Date.Parse(dr("checkin_date").ToString) >= dtpCheckIn.Value.AddDays(-1) And Date.Parse(dr("checkout_date").ToString) <= dtpCheckOut.Value _
            And dr("checkin_time").ToString >= cboTimeIn.Text And dr("checkout_time").ToString <= cboTimeOut.Text Then


                Label4.ForeColor = Color.DarkRed
                Label4.Text = "Not Available"

            Else
                Label4.ForeColor = Color.GreenYellow
                Label4.Text = "Available"



            End If
End While

con.Close()

Answer 1

Hi,

some strange code you have there. Do you have only one reservation (Im asking since you have only 1 label).

If you want to sort, you have to gather all the data, and then when you have all, you can do the sorting, and check the Availability columns if is available or not.

I would suggest you to fill DataTable with all of the data, then sort it by date (and time at ones), and then check of the availability.

You can use Linq if you know how to do it.

---

Mitja

Answer 2

Hi Mitja Bonca .

that code is just an example, i'm just referring that to a single cottage ... 

For example, in the database there's a reservation from 11:00AM - 05:00PM .. Then, if someone will have to reserve from 12:00-04:00 then the label should go red because it is reserved by that time ..

And what's Linq ?

Answer 3

what about if new user wants reserve from  04PM to 6PM?

And what are your database columns?

---

and btw, if you want to check for a sinlge cottage, then you dont use while loop, but if, because you are refering to a specifc one - this means you have to use a Where clause in the select query.

---

It should go something like:

DateTime dateStartNew = new DateTime(2012, 8, 27, 12, 00, 00); //example of new reservarion (when starts)
DateTime dateEndNew = new DateTime(2012, 8, 27, 16, 00, 00);//example of new reservarion (when ends) - I took your example

string query = @"SELECT DateStart, DateEnd FROM Reservations WHERE Cottage = @cottage";
OleDbConnection conn = new OleDbConnection("connString");
OleDbComamnd cmd = new OleDbCommand(query, conn);
cmd.Parameters.AddWithValue("@cottage", "CottageName");
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
    DateTime date1 = Convert.ToDateTime(reader[0].ToString());
    DateTime date2 = Convert.ToDateTime(reader[1].ToString());
    if(dateStartNew >= date1 && dateEndNew <= date2) //this is only when new reservation falls into old reservation!!
    {
        //the serevations has already been taken!!
        //color to red!
    }
    else
    {
        //you can set other conditions if new be
    }
}

And close connection and command on the end!

I hope it helps how to do queries and how to then get the data back from database and compare it (them) with new data.

---

Mitja

Answer 4

sir Mitja Bonca,

can you please translate that into visual basic 2010 syntax... thank you so much ... :)

Answer 5

Yes:

Dim dateStartNew As New DateTime(2012, 8, 27, 12, 0, 0)'example of new reservarion (when starts)
Dim dateEndNew As New DateTime(2012, 8, 27, 16, 0, 0)'example of new reservarion (when ends) - I took your example
Dim query As String = "SELECT DateStart, DateEnd FROM Reservations WHERE Cottage = @cottage"
Dim conn As New OleDbConnection("connString")
Dim cmd As OleDbComamnd = New OleDbCommand(query, conn)
cmd.Parameters.AddWithValue("@cottage", "CottageName")
conn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader()
If reader.Read() Then
	Dim date1 As DateTime = Convert.ToDateTime(reader(0).ToString())
	Dim date2 As DateTime = Convert.ToDateTime(reader(1).ToString())
	If dateStartNew >= date1 AndAlso dateEndNew <= date2 Then
			'the serevations has already been taken!!
			'color to red!
		'this is only when new reservation falls into old reservation!!
			'you can set other conditions if new be
	Else
	End If
End If

next time use this translator.

---

Mitja

Answer 6

I've modified you code and I got an error on the first line "Conversion from string "8/29/2012 08:00 AM" to type 'Long' is not valid."

 Dim dateStartNew As New DateTime(dtpCheckIn.Text + " " + cboTimeIn.Text) 'the error "8/29/2012 08:00 AM" to type 'Long' is not valid.""
        Dim dateEndNew As New DateTime(dtpCheckOut.Text + " " + cboTimeOut.Text) 'example of new reservarion (when ends) - I took your example
        Dim query As String = "SELECT checkin_date, checkin_time, checkout_date, checkout_time FROM tbltransaction WHERE Cottage_type = '" & lblcottage.Text & "'"
        Dim conn As New OleDbConnection(dbProvider & dbSource)
        Dim cmd As OleDbCommand = New OleDbCommand(query, conn)
        cmd.Parameters.AddWithValue(lblcottage.Text, "cottage_type")
        conn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.Read() Then
            Dim date1 As DateTime = Convert.ToDateTime(reader(8).ToString) + (reader(9).ToString)
            Dim date2 As DateTime = Convert.ToDateTime(reader(10).ToString) + (reader(11).ToString)
            If dateStartNew >= date1 AndAlso dateEndNew <= date2 Then
                'the serevations has already been taken!!
                'color to red!
                'this is only when new reservation falls into old reservation!!
                'you can set other conditions if new be
                Label4.ForeColor = Color.DarkRed
                '       Label4.Text = "Not Available"

            Else
                Label4.ForeColor = Color.GreenYellow
                Label4.Text = "Available"
            End If
        End If

Answer 7

Hi amaChristLan,

Please refer to MSDN library: DateTime Constructor

There is not a constructor with string parameters.

Best regards,

---

Chester Hong
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 8

Dim query As String = "SELECT checkin_date, checkin_time, checkout_date, checkout_time FROM tbltransaction WHERE Cottage_type = '" & lblcottage.Text & "'"

1st of all, why do you have 4 columns? When you only need 2.

Date and Time can be combined into one single value, so no need to 2 per checkin (same goes for checkuout).

---

Next,

to get date and time from two textBoxes, do as followed:

Dim strDateTime As String = String.Format("{0} {1}", dtpCheckIn.Text, cboTimeIn.Text)
Dim dateStartNew As New DateTime()
If Not DateTime.TryParse(strDateTime, dateStartNew) Then
	MessageBox.Show("The provided date and time are not in correct format!")
	Return
End If

Hope it helps,

bye

---

Mitja