none
How do I need to stop data reading in Visual Basic.NET when one of the condition of data is met in Microsoft Database? RRS feed

  • Question

  • Hi, how do I need to solve when my check-in date is 9/5/2018 and check-out date is 15/5/2018 and this condition have met my second row data from Microsoft Database. The Visual Basic show 3 Message Box which is Available, Occupied, Available but how do I make it if one condition met and will show 1 messagebox:  "Occupied" and button backcolor = red without showing 3 messagebox? Thank you.

    Below are my code: 

            Search2.CommandText = "Select * from Table1 ;"
            Search2.Connection = cnnOLEDB
            Dim dr As OleDbDataReader = Search2.ExecuteReader
            While dr.Read = True
                If dtpIn.Value < dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value < dr(2) Then
                    MessageBox.Show("Occupied")
                    Button1.BackColor = Color.Red
                ElseIf dtpIn.Value > dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value > dr(2) Then
                    MessageBox.Show("Occupied")
                    Button1.BackColor = Color.Red
                ElseIf dtpIn.Value < dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value > dr(2) Then
                    MessageBox.Show("Occupied")
                    Button1.BackColor = Color.Red
                ElseIf dtpIn.Value > dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value < dr(2) Then
                    MessageBox.Show("Occupied")
                    Button1.BackColor = Color.Red
                Else
                    Button1.BackColor = Color.Green
                    MessageBox.Show("Available")
                    Button1.BackColor = Color.Green
                End If
            End While
            dr.Close()

    Below are my database: 

    Chalet_number

    Check_In_Status

    Check_Out_Status

    1

    13/4/2018

    20/4/2018

    2

    10/5/2018

    17/5/2018

    3

    8/6/2018

    13/6/2018

    Thursday, April 5, 2018 11:34 AM

All replies

  • Hi ben,

    >>but how do I make it if one condition met and will show 1 messagebox:  "Occupied" and button backcolor = red without showing 3 messagebox?

    That is because the 'While dr.Read = True' will loop through all the data in the database. to solve this problem, you just need to use 'Return' to terminate your code:

    If dtpIn.Value < dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value < dr(2) Then MessageBox.Show("Occupied") Button1.BackColor = Color.Red Return ElseIf dtpIn.Value > dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value > dr(2) Then MessageBox.Show("Occupied") Button1.BackColor = Color.Red Return ElseIf dtpIn.Value < dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value > dr(2) Then MessageBox.Show("Occupied") Button1.BackColor = Color.Red Return ElseIf dtpIn.Value > dr(1) And dtpIn.Value < dr(2) And dtpOut.Value > dr(1) And dtpOut.Value < dr(2) Then MessageBox.Show("Occupied") Button1.BackColor = Color.Red Return Else Button1.BackColor = Color.Green MessageBox.Show("Available") Button1.BackColor = Color.Green

    Return End If

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Stanly Fan Friday, April 6, 2018 5:40 AM
    Friday, April 6, 2018 5:38 AM
  • But if using this function when I select a date between my second data.It should be show "Occupied" but the result show "Available". 
    Friday, April 6, 2018 6:21 AM
  • But if using this function when I select a date between my second data.It should be show "Occupied" but the result show "Available". 

    This is a problem with your logic, not with trying to exit the test when you get a match.  If you have a new question you should start a new thread.

    You have listed the dates against chalets, but I believe it should actually be a list of bookings, because one chalet can have several bookings (for different date ranges).

    If you write out the test in plain language you can usually translate it into correct code. For instance, the chalet is free if the booked guests have left before these new guests arrive, or if the booked guests don't arrive until after these new guests have left. In other words, 

    This booking (in the database) does NOT conflict with the new booking (that I want to add) if:
      this booking end date is earlier than the new booking start date, OR
      this booking start date is later than the new booking end date.

    That assumes that at booking in the data base will have a end date later than its start date, which ought to be a reasonable assumption.

    Friday, April 6, 2018 7:00 AM