mainform database (infoSystem - jordan)

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class frmMain
    Dim mToggle, mModifyData, mSelectRecord As Integer
    Dim txt, cbo As Control

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mToggle = 0
        ModifyControls()
        'bind the data to the controls
        BindSex()
        BindCivilstatus()
        BindCitizenship()
        BindGridview()
    End Sub

    Private Sub ModifyControls()
        Select Case mToggle
            Case 0
                For Each txt In Me.Controls
                    If TypeOf txt Is TextBox Then
                        txt.Enabled = False
                    End If
                Next
                For Each cbo In Me.Controls
                    If TypeOf cbo Is ComboBox Then
                        cbo.Enabled = False
                    End If
                Next
                dtpBirthdate.Enabled = False
                mnuSave.Enabled = False
                mnuCancel.Enabled = False

                grdInformation.Enabled = True
                mnuAdd.Enabled = True
            Case 1
                For Each txt In Me.Controls
                    If TypeOf txt Is TextBox Then
                        txt.Enabled = True
                    End If
                Next
                For Each cbo In Me.Controls
                    If TypeOf cbo Is ComboBox Then
                        cbo.Enabled = True
                    End If
                Next
                dtpBirthdate.Enabled = True
                mnuSave.Enabled = True
                mnuCancel.Enabled = True

                grdInformation.Enabled = False
                mnuAdd.Enabled = False
                mnuEdit.Enabled = False
                mnuDelete.Enabled = False
        End Select
    End Sub

    Private Sub BindSex()
        BindCombobox("select * from dbo.gender", "sex", "id", cboSex)
    End Sub

    Private Sub BindCivilstatus()
        BindCombobox("select * from dbo.civilstatus", "civilstatus", "id", cboCivilstatus)
    End Sub

    Private Sub BindCitizenship()
        BindCombobox("select * from dbo.citizenship", "citizenship", "id", cboCitizenship)
    End Sub

    Private Sub ResetControls()
        ClearTextbox(Me)
        dtpBirthdate.Value = Now
        cboSex.SelectedIndex = 0
        cboCivilstatus.SelectedIndex = 0
        cboCitizenship.SelectedIndex = 0
    End Sub

    Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click
        'required validation
        If txtSurname.Text = "" Then MsgBox("Please fillup the field surname!", MsgBoxStyle.Critical, "Save error") _
            : txtSurname.Focus() : Exit Sub
        If txtFirstname.Text = "" Then MsgBox("Please fillup the field first name!", MsgBoxStyle.Critical, "Save error") _
            : txtFirstname.Focus() : Exit Sub
        If txtMiddlename.Text = "" Then MsgBox("Please fillup the field middle name!", MsgBoxStyle.Critical, "Save error") _
            : txtMiddlename.Focus() : Exit Sub
        If txtBirthplace.Text = "" Then MsgBox("Please fillup the field place of birth!", MsgBoxStyle.Critical, "Save error") _
            : txtBirthplace.Focus() : Exit Sub
        If txtAddress.Text = "" Then MsgBox("Please fillup the field address!", MsgBoxStyle.Critical, "Save error") _
            : txtAddress.Focus() : Exit Sub
        'saving to the database
        Select Case mModifyData
            Case 0
                Try
                    OpenConnection()
                    objCmd = New SqlCommand("insert into dbo.personalinfo(fname,lname,mname,suffix,birthdate," & _
                        "birthplace,sex,civilstatus,citizenship,address,zipcode,telephone) " & _
                        "values(@fname,@lname,@mname,@suffix,@birthdate,@birthplace,@sex," & _
                        "@civilstatus,@citizenship,@address,@zipcode,@telephone)", objCon)
                    objCmd.Parameters.Clear()
                    With objCmd.Parameters
                        .AddWithValue("@fname", txtFirstname.Text.ToUpper)
                        .AddWithValue("@lname", txtSurname.Text.ToUpper)
                        .AddWithValue("@mname", txtMiddlename.Text.ToUpper)
                        .AddWithValue("@suffix", txtSuffix.Text.ToUpper)
                        .AddWithValue("@birthdate", dtpBirthdate.Text)
                        .AddWithValue("@birthplace", txtBirthplace.Text.ToUpper)
                        .AddWithValue("@sex", cboSex.SelectedValue)
                        .AddWithValue("@civilstatus", cboCivilstatus.SelectedValue)
                        .AddWithValue("@citizenship", cboCitizenship.SelectedValue)
                        .AddWithValue("@address", txtAddress.Text.ToUpper)
                        .AddWithValue("@zipcode", txtZipcode.Text.ToUpper)
                        .AddWithValue("@telephone", txtTelephone.Text.ToUpper)
                    End With
                    objCmd.ExecuteNonQuery()
                    BindGridview()
                    MsgBox("Information successfully saved!", MsgBoxStyle.Information, "Save complete")
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Save error")
                Finally
                    CloseConnection()
                    ResetControls()
                    mToggle = 0
                    ModifyControls()
                End Try
            Case 1
                Try
                    OpenConnection()
                    objCmd = New SqlCommand("update dbo.personalinfo set fname=@fn,lname=@ln,mname=@mn,suffix=@sf,birthdate=@bd," & _
                        "birthplace=@bp,sex=@sx,civilstatus=@cs,citizenship=@cz,address=@ad,zipcode=@zc,telephone=@tp " & _
                        "where id='" & grdInformation.Item(0, grdInformation.CurrentRow.Index).Value & "'", objCon)
                    objCmd.Parameters.Clear()
                    With objCmd.Parameters
                        .AddWithValue("@fn", txtFirstname.Text.ToUpper)
                        .AddWithValue("@ln", txtSurname.Text.ToUpper)
                        .AddWithValue("@mn", txtMiddlename.Text.ToUpper)
                        .AddWithValue("@sf", txtSuffix.Text.ToUpper)
                        .AddWithValue("@bd", dtpBirthdate.Text)
                        .AddWithValue("@bp", txtBirthplace.Text.ToUpper)
                        .AddWithValue("@sx", cboSex.SelectedValue)
                        .AddWithValue("@cs", cboCivilstatus.SelectedValue)
                        .AddWithValue("@cz", cboCitizenship.SelectedValue)
                        .AddWithValue("@ad", txtAddress.Text.ToUpper)
                        .AddWithValue("@zc", txtZipcode.Text.ToUpper)
                        .AddWithValue("@tp", txtTelephone.Text.ToUpper)
                    End With
                    objCmd.ExecuteNonQuery()
                    BindGridview()
                    MsgBox("Information successfully updated!", MsgBoxStyle.Information, "Update complete")
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Update error")
                Finally
                    CloseConnection()
                    ResetControls()
                    mToggle = 0
                    ModifyControls()
                End Try
        End Select
    End Sub

    Private Sub BindGridview()
        'populating the gridview from the database
        Try
            OpenConnection()
            objDa = New SqlDataAdapter("select dbo.personalinfo.id as ID, lname + ', ' + fname + ' ' + left(mname, 1) + '.' as Name, " & _
                "birthdate as 'Birth Date', birthplace as 'Birth Place', dbo.gender.sex as Sex, " & _
                "dbo.civilstatus.civilstatus as 'Civil Status', dbo.citizenship.citizenship as Citizenship, " & _
                "address as Address, zipcode as 'Zip Code', telephone as Telephone from dbo.personalinfo " & _
                "inner join dbo.gender on dbo.personalinfo.sex=dbo.gender.id inner join " & _
                "dbo.civilstatus on dbo.personalinfo.civilstatus=dbo.civilstatus.id inner join " & _
                "dbo.citizenship on dbo.personalinfo.citizenship=dbo.citizenship.id order by dbo.personalinfo.id", objCon)
            objDt = New DataTable
            objDa.Fill(objDt)
            grdInformation.DataSource = objDt
            lblCount.Text = grdInformation.Rows.Count
            If objDt.Rows.Count > 0 Then
                mnuEdit.Enabled = True
                mnuDelete.Enabled = True
            Else
                mnuEdit.Enabled = False
                mnuDelete.Enabled = False
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            CloseConnection()
        End Try
    End Sub

    Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
        Application.Exit()
    End Sub

    Private Sub mnuAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAdd.Click
        mToggle = 1
        ModifyControls()
        mModifyData = 0
        txtSurname.Focus()
    End Sub

    Private Sub mnuCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCancel.Click
        mToggle = 0
        ModifyControls()
        ResetControls()
        BindGridview()
    End Sub

    Private Sub mnuDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDelete.Click
        mSelectRecord = grdInformation.Item(0, grdInformation.CurrentRow.Index).Value
        If MsgBox("Are you sure you want to delete the selected record?", _
            MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2 + MsgBoxStyle.Question, "Delete") = MsgBoxResult.Yes Then
            Try
                OpenConnection()
                objCmd = New SqlCommand("delete from dbo.personalinfo where id='" & mSelectRecord & "'", objCon)
                objCmd.ExecuteNonQuery()
                BindGridview()
                MsgBox("The record has been deleted!", MsgBoxStyle.Information, "Delete")
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Delete error")
            Finally
                CloseConnection()
            End Try
        End If
    End Sub

    Private Sub mnuEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuEdit.Click
        mModifyData = 1
        mSelectRecord = grdInformation.Item(0, grdInformation.CurrentRow.Index).Value
        Try
            OpenConnection()
            objCmd = New SqlCommand("select * from dbo.personalinfo where id='" & mSelectRecord & "'", objCon)
            objDr = objCmd.ExecuteReader()
            If objDr.HasRows = True Then
                objDr.Read()
                txtSurname.Text = objDr(2).ToString()
                txtFirstname.Text = objDr(1).ToString()
                txtMiddlename.Text = objDr(3).ToString()
                txtSuffix.Text = objDr(4).ToString()
                dtpBirthdate.Value = objDr(5).ToString()
                txtBirthplace.Text = objDr(6).ToString()
                cboSex.SelectedValue = objDr(7).ToString()
                cboCivilstatus.SelectedValue = objDr(8).ToString()
                cboCitizenship.SelectedValue = objDr(9).ToString()
                txtAddress.Text = objDr(10).ToString()
                txtZipcode.Text = objDr(11).ToString()
                txtTelephone.Text = objDr(12).ToString()
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Edit error")
        Finally
            CloseConnection()
            mToggle = 1
            ModifyControls()
        End Try
    End Sub

    Private Sub mnuMain_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles mnuMain.ItemClicked

    End Sub
End Class