Programming for Everybody: Visual Basic.net: insert date and check a date between two dates in access database(system book room in a hotel)

Visual Basic.net: insert date and check a date between two dates in access database(system book room in a hotel)



 Imports System.Data.OleDb

Public Class Form6

    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\dd.mdb")


    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strsql1 As String

        strsql1 = "insert into table1(room1,startdate1,enddate1)Values(@room,@start1,@end1)"

        Dim cmd1 As New OleDbCommand(strsql1, conn)

        cmd1.Parameters.AddWithValue("@room", OleDbType.VarChar).Value = ComboBox1.Text

        cmd1.Parameters.AddWithValue("@start1", OleDbType.Date).Value = DateTimePicker1.Value.ToString

        cmd1.Parameters.AddWithValue("@end1", OleDbType.Date).Value = DateTimePicker2.Value.ToString


        conn.Open()

        cmd1.ExecuteNonQuery()

        conn.Close()

        MessageBox.Show("The Room reserved successfully")

    End Sub

  

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        conn.Open()

        Dim strsql2 As String

        strsql2 = " Select * from table1 where room1=@room " & _

            " And ((@start1>=startdate1 and @end1<= enddate1)" & _

            " or (@start1<=startdate1 and @end1>= enddate1)" & _

            " or (@start1>startdate1 and @end1> enddate1 and @start1 <enddate1 )" & _

            " or (@start1<startdate1 and @end1< enddate1 and @end1 >startdate1 ))"

        Dim cmd2 As New OleDbCommand(strsql2, conn)

        cmd2.Parameters.AddWithValue("@room", OleDbType.VarChar).Value = ComboBox1.Text

        cmd2.Parameters.AddWithValue("@start1", OleDbType.Date).Value = DateTimePicker1.Value.ToString

        cmd2.Parameters.AddWithValue("@end1", OleDbType.Date).Value = DateTimePicker2.Value.ToString


        Dim myreader As OleDbDataReader = cmd2.ExecuteReader

        If (myreader.Read()) Then

            MessageBox.Show(" The room reserved from: " & (myreader.GetValue("2").ToString & " to: " & (myreader.GetValue("3").ToString)))

        Else

            MessageBox.Show(" The Room not reserved before")

        End If

        conn.Close()


    End Sub

End Class

No comments:

Post a Comment