Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim myconn As SqlConnection
Dim mycmd As SqlCommand
Dim mydr As SqlDataReader
Dim dr As SqlDataReader
Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dview2()
lbldate.Text = Date.Today
dview3()
schedupdate()
lbldatestatus.Text = lbldate.Text
lbldatestatuss.Text = "UPDATED"
lbldatestatusss.Text = ""
Dim temptext As String = ""
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [user]", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1user.Items.Add(dr("username"))
txtuser.Items.Add(dr("username"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
'------------------------------------------------------------------------------------------
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [role]", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1role.Items.Add(dr("rolename"))
txtroles.Items.Add(dr("rolename"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
'------------------------------------------------------------------------------------------
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [booth]", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1booth.Items.Add(dr("boothname"))
txtbooth.Items.Add(dr("boothname"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub TabPage2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage2.Click
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If txtdate.Text <> "" And txtuser.Text <> "" And txtshift.Text <> "" And txtbooth.Text <> "" And txtroles.Text <> "" Then
Dim xcounter As Integer = 0
Try
myconn = New SqlConnection(My.Settings.dsettings)
myconn.Open()
mycmd = New SqlCommand("insert into usersched (sdate,suser,stime,srole,sbooth,stimeid,sroleid,sboothid,remarks) values ('" & txtdate.Text & "' , '" & txtuser.Text & "' , '" & txtshift.Text & "' , '" & txtroles.Text & "', '" & txtbooth.Text & "' , '" & txtshiftid.Text & "' , '" & txtrolesid.Text & "' , '" & txtboothid.Text & "' , '" & txtremarks.Text & "')", myconn)
xcounter = mycmd.ExecuteNonQuery
MessageBox.Show("(" & xcounter & ") affected")
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
myconn.Close()
mycmd.Dispose()
End Try
txtdate.Text = ""
txtuser.Text = ""
txtshift.Text = ""
txtbooth.Text = ""
txtroles.Text = ""
txtshiftid.Text = ""
txtroles.Text = ""
txtshiftid.Text = ""
txtremarks.Text = ""
Else
MessageBox.Show("Please complete Field", "Add")
End If
dview2()
dview3()
End Sub
Private Sub MonthCalendar1_DateChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateChanged
txtdate.Text = MonthCalendar1.SelectionRange.Start
End Sub
Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage1.Click
End Sub
Public Sub dview()
Try
myconn = New SqlConnection(My.Settings.dsettings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
mycmd = New SqlCommand("select sdate,suser,stime,srole,sbooth, idd from usersched where sdate like '" & MonthCalendar2.SelectionRange.Start & "' ", myconn)
mydr = mycmd.ExecuteReader()
'MessageBox.Show("(" & xcounter & ") affected")
Dim dtable As New DataTable()
dtable.Load(mydr)
DataGridView1.DataSource = dtable
DataGridView1.Refresh()
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message, "view")
myconn.Close()
mycmd.Dispose()
End Try
End Sub
Public Sub dview2()
Try
myconn = New SqlConnection(My.Settings.dsettings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
mycmd = New SqlCommand("select * from usersched order by idd desc ", myconn)
mydr = mycmd.ExecuteReader()
'MessageBox.Show("(" & xcounter & ") affected")
Dim dtable As New DataTable()
dtable.Load(mydr)
DataGridView2.DataSource = dtable
DataGridView2.Refresh()
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message, "view")
myconn.Close()
mycmd.Dispose()
End Try
End Sub
Public Sub dview3()
Try
myconn = New SqlConnection(My.Settings.dsettings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
mycmd = New SqlCommand("select sdate,suser,stime,srole,sbooth, idd,stimeid,sroleid,sboothid from usersched where sdate like '" & lbldate.Text & "' order by idd desc ", myconn)
mydr = mycmd.ExecuteReader()
'MessageBox.Show("(" & xcounter & ") affected")
Dim dtable As New DataTable()
dtable.Load(mydr)
DataGridView3.DataSource = dtable
DataGridView3.Refresh()
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message, "view")
myconn.Close()
mycmd.Dispose()
End Try
End Sub
Private Sub MonthCalendar2_DateChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar2.DateChanged
dview()
End Sub
Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click
If DataGridView1.SelectedCells.Count > 0 Then
txtt1id.Text = DataGridView1.SelectedRows(0).Cells(5).Value
txtt1date.Text = DataGridView1.SelectedRows(0).Cells(0).Value
txtt1user.Text = DataGridView1.SelectedRows(0).Cells(1).Value
txtt1time.Text = DataGridView1.SelectedRows(0).Cells(2).Value
txtt1role.Text = DataGridView1.SelectedRows(0).Cells(3).Value
txtt1booth.Text = DataGridView1.SelectedRows(0).Cells(4).Value
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim xcounter As Integer = 0
Try
myconn = New SqlConnection(My.Settings.dsettings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
mycmd = New SqlCommand("update usersched set sdate='" & txtt1date.Text & "' , suser='" & txtt1user.Text & "' , stime='" & txtt1time.Text & "' , srole='" & txtt1role.Text & "' , stimeid='" & txtt1timeid.Text & "' , sroleid='" & txtt1roleid.Text & "' , sboothid='" & txtt1boothid.Text & "' , sbooth='" & txtt1booth.Text & "' where idd ='" & txtt1id.Text & "'", myconn)
xcounter = mycmd.ExecuteNonQuery
MessageBox.Show("(" & xcounter & ") affected")
myconn.Close()
mycmd.Dispose()
dview()
dview2()
Catch ex As Exception
MessageBox.Show(ex.Message, "asdf")
myconn.Close()
mycmd.Dispose()
txtt1id.Text = ""
txtt1date.Text = ""
txtt1user.Text = ""
txtt1time.Text = ""
txtt1booth.Text = ""
End Try
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtt1id.TextChanged
End Sub
Private Sub Label9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label9.Click
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If MessageBox.Show("Are your sure to delete '" & DataGridView1.SelectedRows(0).Cells(0).Value & "'", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
Dim xcounter As Integer = 0
Try
myconn = New SqlConnection(My.Settings.dsettings)
myconn.Open()
mycmd = New SqlCommand("delete from usersched where idd='" & txtt1id.Text & "'", myconn)
xcounter = mycmd.ExecuteNonQuery
MessageBox.Show("(" & xcounter & ") affected")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
myconn.Close()
mycmd.Dispose()
dview()
dview2()
txtt1id.Text = ""
txtt1date.Text = ""
txtt1user.Text = ""
txtt1time.Text = ""
txtt1booth.Text = ""
End If
End Sub
Private Sub txtt1time_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtt1time.SelectedIndexChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [shift] where shiftstart like '" & Mid(txtt1time.Text, 1, 5) & "%" & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1timeid.Text = (dr("shiftid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub txtt1role_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtt1role.SelectedIndexChanged
End Sub
Private Sub txtt1role_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtt1role.TextChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [role] where rolename= '" & txtt1role.Text & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1roleid.Text = (dr("roleid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub txtt1booth_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtt1booth.SelectedIndexChanged
End Sub
Private Sub txtt1booth_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtt1booth.TextChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [booth] where boothname= '" & txtt1booth.Text & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtt1boothid.Text = (dr("boothid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub txtshift_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtshift.SelectedIndexChanged
End Sub
Private Sub txtroles_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtroles.SelectedIndexChanged
End Sub
Private Sub txtbooth_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtbooth.SelectedIndexChanged
End Sub
Private Sub txtbooth_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtbooth.TextChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [booth] where boothname= '" & txtbooth.Text & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtboothid.Text = (dr("boothid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub txtroles_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtroles.TextChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [role] where rolename= '" & txtroles.Text & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtrolesid.Text = (dr("roleid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub txtshift_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtshift.TextChanged
Try
myconn = New SqlConnection(My.Settings.d2Settings)
myconn.Open()
mycmd = New SqlCommand("select * from [shift] where shiftstart like '" & Mid(txtshift.Text, 1, 5) & "%" & "'", myconn)
dr = mycmd.ExecuteReader
While dr.Read
txtshiftid.Text = (dr("shiftid"))
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "cbox")
End Try
myconn.Close()
mycmd.Dispose()
dr.Close()
End Sub
Private Sub Label14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbldate.Click
End Sub
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If ProgressBar1.Value = 100 Then
If lbldatestatus.Text <> lbldate.Text Then
schedupdate()
lbldatestatus.Text = lbldate.Text
lbldatestatuss.Text = "UPDATED"
lbldatestatusss.Text = ""
End If
If lbldatestatusss.Text = "" And lbltime.Text = 5 And lbldatestatuss.Text = "UPDATED" Then
schedupdate2()
lbldatestatusss.Text = "UPDATED"
End If
lbltime.Text = TimeOfDay
dview3()
ProgressBar1.Value = 1
Else
ProgressBar1.Value = ProgressBar1.Value + 1
End If
lbltime.Text = Hour(Now)
lbldate.Text = Date.Today
End Sub
Private Sub DataGridView2_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellContentClick
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
schedupdate()
lbldatestatus.Text = lbldate.Text
lbldatestatuss.Text = "UPDATED"
End Sub
Private Sub TabPage4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage4.Click
End Sub
Public Sub schedupdate()
'-------------------------------------------
Dim i As Integer = 0
Dim rcount As Integer = 0
Try
rcount = DataGridView3.RowCount
DataGridView3.MultiSelect = False
While i < rcount
DataGridView3.Rows(i).Selected = True
DataGridView3.CurrentCell = DataGridView3(0, i)
'update process -------------------------------------------------
Dim xcounter As Integer = 0
Dim iuser = "", isched = "", irole = "", ibooth = ""
Try
myconn = New SqlConnection(My.Settings.d2Settings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
iuser = DataGridView3.SelectedRows(0).Cells(1).Value
isched = DataGridView3.SelectedRows(0).Cells(6).Value
irole = DataGridView3.SelectedRows(0).Cells(7).Value
ibooth = DataGridView3.SelectedRows(0).Cells(8).Value
mycmd = New SqlCommand("update [user] set roleid='" & irole & "' , shiftid='" & isched & "' , boothid='" & ibooth & "' where username ='" & iuser & "'", myconn)
If isched <> 7 Then
xcounter = mycmd.ExecuteNonQuery
End If
'MessageBox.Show("(" & xcounter & ") affected")
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message, "look update")
myconn.Close()
mycmd.Dispose()
End Try
'/update process -------------------------------------------------
i = i + 1
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "updating")
End Try
'--------------------------------------------
End Sub
Public Sub schedupdate2()
'-------------------------------------------
Dim i As Integer = 0
Dim rcount As Integer = 0
Try
rcount = DataGridView3.RowCount
DataGridView3.MultiSelect = False
While i < rcount
DataGridView3.Rows(i).Selected = True
DataGridView3.CurrentCell = DataGridView3(0, i)
'update process -------------------------------------------------
Dim xcounter As Integer = 0
Dim iuser = "", isched = "", irole = "", ibooth = ""
Try
myconn = New SqlConnection(My.Settings.d2Settings)
If myconn.State = System.Data.ConnectionState.Closed Then
myconn.Open()
End If
iuser = DataGridView3.SelectedRows(0).Cells(1).Value
isched = DataGridView3.SelectedRows(0).Cells(6).Value
irole = DataGridView3.SelectedRows(0).Cells(7).Value
ibooth = DataGridView3.SelectedRows(0).Cells(8).Value
mycmd = New SqlCommand("update [user] set roleid='" & irole & "' , shiftid='" & isched & "' , boothid='" & ibooth & "' where username ='" & iuser & "'", myconn)
If isched = 7 Then
xcounter = mycmd.ExecuteNonQuery
End If
'MessageBox.Show("(" & xcounter & ") affected")
myconn.Close()
mycmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message, "look update")
myconn.Close()
mycmd.Dispose()
End Try
'/update process -------------------------------------------------
i = i + 1
End While
Catch ex As Exception
MessageBox.Show(ex.Message, "updating")
End Try
'--------------------------------------------
End Sub
Private Sub Label16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label16.Click
End Sub
Private Sub lbldatestatus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbldatestatus.Click
End Sub
End Class
No comments:
Post a Comment