Imports System.Data.SqlClient Public Class Form1 Dim con As New SqlConnection("server=.\SQLEXPRESS;database=domatia;Integrated Security=True") Dim imaf, iman As Date Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click Dim room As Integer Dim timi As Double room = ListBox1.SelectedItem imaf = MonthCalendar1.SelectionStart.ToString("dd/MM/yyyy") iman = MonthCalendar2.SelectionStart.ToString("dd/MM/yyyy") If Month(imaf) = Month(iman) Then MessageBox.Show(Month(imaf) & " " & Month(iman)) timi += ((Format(iman, "dd") - Format(imaf, "dd")) * 30) MessageBox.Show(timi) ElseIf Month(iman) > Month(imaf) Then timi += (((31 - Format(imaf, "dd")) + Format(iman, "dd")) * 30) + (Month(iman) - Month(imaf) - 1) * 30 End If If room > 0 Then Dim query As String = "INSERT INTO kratisi VALUES (@onoma,@imaf,@iman,@room,@timi,@eponimo,@id)" MessageBox.Show(vbEmpty & " " & imaf & " " & iman) Using cmd As New SqlCommand() With cmd .Connection = con .CommandType = CommandType.Text .CommandText = query .Parameters.AddWithValue("@onoma", TextBox1.Text) .Parameters.AddWithValue("@imaf", imaf) .Parameters.AddWithValue("@iman", iman) .Parameters.AddWithValue("@room", room) .Parameters.AddWithValue("@timi", timi) .Parameters.AddWithValue("@eponimo", TextBox2.Text) .Parameters.AddWithValue("@id", "") End With MessageBox.Show(cmd.ToString()) Try con.Open() cmd.ExecuteNonQuery() MessageBox.Show("o πελάτης " & TextBox1.Text & " " & TextBox2.Text & " καταχωρήθηκε στον πίνακα kratisi.") Catch MessageBox.Show("Error Message") End Try End Using con.Close() query = "update rooms set isfree=@id,imaf=@imaf,iman=@iman where roomnumber=@room" Using cmd As New SqlCommand() With cmd .Connection = con .CommandType = CommandType.Text .CommandText = query .Parameters.AddWithValue("@id", 0) .Parameters.AddWithValue("@imaf", imaf) .Parameters.AddWithValue("@iman", iman) .Parameters.AddWithValue("@room", room) End With MessageBox.Show(cmd.ToString()) Try con.Open() cmd.ExecuteNonQuery() Catch MessageBox.Show("Error Message") End Try End Using con.Close() End If End Sub Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click Dim d As Date d = Date.Now MessageBox.Show(d) Dim query As String = "delete from kratisi where iman<=@date" con.Open() Using comm As New SqlCommand() With comm .Connection = con .CommandType = CommandType.Text .CommandText = query .Parameters.AddWithValue("@date", d) End With Try comm.ExecuteNonQuery() MessageBox.Show("όλες οι παλιές κρατήσεις διαγράφηκσν.πατήστε show available rooms") Catch MessageBox.Show("Error Message") End Try con.Close() End Using query = "update rooms set imaf=@date1,iman=@date2,isfree=@value where iman<=@date" con.Open() Using comm As New SqlCommand() With comm .Connection = con .CommandType = CommandType.Text .CommandText = query .Parameters.AddWithValue("@date1", "1000-01-01") .Parameters.AddWithValue("@date2", "1000-01-01") .Parameters.AddWithValue("@value", 1) .Parameters.AddWithValue("@date", d) End With Try comm.ExecuteNonQuery() MessageBox.Show("όλες οι τιμές στον πίνακα rooms ενημερώθηκαν!!!") Catch MessageBox.Show("Error Message") End Try con.Close() End Using End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'imaf = MonthCalendar1.SelectionStart.ToString("dd/MM/yyyy") 'iman = MonthCalendar2.SelectionStart.ToString("dd/MM/yyyy") Dim d, m As String Dim f, l As Date 'd = imaf.Day.ToString() ' m = imaf.ToString("MMMM") Dim dd, mm As Date Dim start, en As Date Dim dt As DateTime Dim format As String = "yyyy-MM-dd" Dim str1, str2 As String start = MonthCalendar1.SelectionStart 'MessageBox.Show(start) dt = Convert.ToDateTime(start) 'MessageBox.Show(dt) str1 = dt.ToString(format) f = CDate(str1) MessageBox.Show(f & " " & f.Day) en = MonthCalendar2.SelectionStart 'MessageBox.Show(en) dt = Convert.ToDateTime(en) 'MessageBox.Show(dt) str2 = dt.ToString(format) l = CDate(str2) MessageBox.Show(l) ListBox1.Items.Clear() Dim cmd As New SqlCommand("SELECT * from rooms", con) Dim room, free As Integer Try con.Open() Dim reader As SqlDataReader reader = cmd.ExecuteReader() If (reader.HasRows) Then While (reader.Read()) room = reader("roomnumber") free = reader("isfree") 'If reader("imaf") <> "" And reader("iman") <> "" Then dd = reader("imaf") mm = reader("iman") ' MessageBox.Show(mm.Day) 'End If If free = 0 Then 'If (f.Day >= mm.Day And f.Month = mm.Month And f.Year >= mm.Year And free = 0) Or (f.Day < mm.Day And f.Month <> mm.Month And f.Year >= mm.Year And free = 0) Or free = 1 Then If f >= mm Then ListBox1.Items.Add(room) ElseIf f < mm Or free = 0 Then MessageBox.Show("room " & room & " is not available") End If Else ListBox1.Items.Add(room) End If End While End If con.Close() Catch ex As SqlException MessageBox.Show("something went wrong " + ex.ToString) End Try End Sub End Class