emp.zip_Table

'The name spaces used Imports System.Data.Odbc 'odbc connectivity is used for database manipulation' Public Class EmployeeNew Dim EId As String Dim editFlg As Integer Friend WriteOnly Property GetEId() As String Set(ByVal value As String) EId = value End Set End Property 'First a dsn create using mysql odbc 3.5 driver with user name and password and database name Private Function GetConnection() As OdbcConnection Dim Conn As New OdbcConnection("DSN=Attendance") Conn.Open() Return Conn End Function 'in the form load event it is checked for adding new employee or to edit the employee details Private Sub EmployeeNew_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If EId = Nothing Then editFlg = 0 If EId <> Nothing Then editFlg = 1 rdoMale.Checked = True 'if to edit the details then fill the details of particular employee If editFlg = 1 Then FillEmployee() End If End Sub Private Sub FillEmployee() 'aconnection object of the odbc connection created Dim con As OdbcConnection = GetConnection() 'the command object created and set the properties of it Dim cmd As New OdbcCommand cmd.CommandType = CommandType.Text cmd.Connection = con cmd.Parameters.Clear() cmd.CommandText = "" If con.State = ConnectionState.Open Then con.Close() con.Open() Dim SelCompany As String = "select * from employee where EId=?" cmd.CommandText = SelCompany cmd.Parameters.Add(New OdbcParameter("?EId", EId)) Dim da As New OdbcDataAdapter(cmd) Dim ds As New DataSet da.Fill(ds) cmd.Parameters.Clear() cmd.CommandText = "" con.Close() 'the data is now present in the data set which is then displayed according to the fields name If ds.Tables(0).Rows.Count >= 1 Then txtEId.Text = ds.Tables(0).Rows(0).Item("EId").ToString txtEName.Text = ds.Tables(0).Rows(0).Item("EmpName").ToString If (ds.Tables(0).Rows(0).Item("Gender").ToString) = "M" Then rdoMale.Checked = True Else : rdoFemale.Checked = True End If txtAddr1.Text = ds.Tables(0).Rows(0).Item("EAdd1").ToString txtAddr2.Text = ds.Tables(0).Rows(0).Item("EAdd2").ToString txtCountry.Text = ds.Tables(0).Rows(0).Item("ECOuntry").ToString txtState.Text = ds.Tables(0).Rows(0).Item("EState").ToString txtTown.Text = ds.Tables(0).Rows(0).Item("ECity").ToString txtZip.Text = ds.Tables(0).Rows(0).Item("EPostcode").ToString txtTele.Text = ds.Tables(0).Rows(0).Item("EPhone").ToString txtMobile.Text = ds.Tables(0).Rows(0).Item("EMobile").ToString txtEmail.Text = ds.Tables(0).Rows(0).Item("EEmail").ToString End If End Sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click Try 'the required fields validation are done If CtrlValidation() Then Dim con As OdbcConnection = GetConnection() Dim cmd As New OdbcCommand cmd.CommandType = CommandType.Text cmd.Connection = con cmd.Parameters.Clear() cmd.CommandText = "" If con.State = ConnectionState.Open Then con.Close() con.Open() Dim strEId As String = "Select EId from employee order by EId desc limit 1" cmd.CommandText = strEId Dim EmId As String = cmd.ExecuteScalar() Dim value As Integer = 0 If EmId = Nothing Then EmId = "EPC" value += 1 Else value = Convert.ToInt64(Val(EmId.Substring(3))) value += 1 EmId = "EPC" End If If value.ToString().Length = 1 Then EmId = EmId + "0000" + value.ToString() ElseIf value.ToString().Length = 2 Then EmId = EmId + "000" + value.ToString() ElseIf value.ToString().Length = 3 Then EmId = EmId + "00" + value.ToString() ElseIf value.ToString().Length = 4 Then EmId = EmId + "0" + value.ToString() ElseIf value.ToString().Length >= 5 Then EmId = EmId + value.ToString() End If If editFlg = 0 Then Dim strInsertEntity As String = "insert into employee values(?,?,?,?,?,?,?,?,?,?,?,?,?)" cmd.CommandText = strInsertEntity cmd.Parameters.Add(New OdbcParameter("?EId", EmId)) ElseIf editFlg = 1 Then Dim strInsertEntity As String = "update employee set `EmpName`=? ,`Gender`=?,SalPerHour=?,`EAdd1`=?,`EAdd2`=?, `ECity`=?, " & _ "`EState`=?,`ECOuntry`=?,`EPostEmId`=?,`EEmail`=? ,`EPhone`=? ,`EMobile`=? where `EId`=?" cmd.CommandText = strInsertEntity End If 'the values to the fields are passed as parameters cmd.Parameters.Add(New OdbcParameter("?EmpName", txtEName.Text)) cmd.Parameters.Add(New OdbcParameter("?Gender", IIf(rdoMale.Checked = True, "M", "F"))) cmd.Parameters.Add(New OdbcParameter("?SalPerHour", Val(txtEId.Text))) cmd.Parameters.Add(New OdbcParameter("?EAdd1", txtAddr1.Text)) cmd.Parameters.Add(New OdbcParameter("?EAdd2", txtAddr2.Text)) cmd.Parameters.Add(New OdbcParameter("?ECity", txtTown.Text)) cmd.Parameters.Add(New OdbcParameter("?EState", txtState.Text)) cmd.Parameters.Add(New OdbcParameter("?ECOuntry", txtCountry.Text)) cmd.Parameters.Add(New OdbcParameter("?EPostEmId", txtZip.Text)) cmd.Parameters.Add(New OdbcParameter("?EEmail", txtEmail.Text)) cmd.Parameters.Add(New OdbcParameter("?EPhone", txtTele.Text)) cmd.Parameters.Add(New OdbcParameter("?EMobile", txtMobile.Text)) If editFlg = 1 Then cmd.Parameters.Add(New OdbcParameter("?EId", EId)) End If cmd.ExecuteScalar() cmd.Parameters.Clear() cmd.CommandText = "" con.Close() EmployeeHome.GridFillQuery() If editFlg = 1 Then Me.Close() ClearCtrls() End If Catch ex As Exception MsgBox(ex.ToString, MsgBoxStyle.Information, Application.ProductName) End Try End Sub Private Function CtrlValidation() As Boolean If txtEName.Text = "" Then MsgBox("Enter employee name", MsgBoxStyle.Information, Application.ProductName) txtEName.Focus() Return False ElseIf txtEId.Text = "" Then MsgBox("Enter salary per hour", MsgBoxStyle.Information, Application.ProductName) txtEId.Focus() Return False ElseIf txtAddr1.Text = "" Then MsgBox("Enter address 1", MsgBoxStyle.Information, Application.ProductName) txtAddr1.Focus() Return False ElseIf txtTown.Text = "" Then MsgBox("Enter town/city", MsgBoxStyle.Information, Application.ProductName) txtTown.Focus() Return False ElseIf txtZip.Text = "" Then MsgBox("Enter zip/postal code", MsgBoxStyle.Information, Applicati