Imports System.Windows.Forms
Imports System.Data.SqlClient

' simple tutorial on how to interact to SQL SERVER DATABASE 2008 using Visual Basic.Net 2010
' general features:
'   pure SQL Statements (select, insert, delete, update)
'   how to use sub procedures with parameters and how to call procedures directly

'tips from me
'if using/creating a procedures always declare as private/public not only as SUB
'in calling sub procedures and functions, always use CALL to make d program readable!

'programmed by:
'cedrick blas
'junior analyst/programmer
'cedrickblas@yahoo.com (+639081805286)

Public Class Form1
    Dim cn As SqlConnection
    Dim cmd As SqlCommand

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call connectToDatabase("Data Source=.\SQLEXPRESS;Initial Catalog=NASL;Integrated Security=True")
        Call centerForm(Me)
        Call initList(Me.ListView1)
        Call ShowRecords()
        Call defaultLocks(True)
        Call mrClean()
    End Sub

    'connection to db
    Private Sub connectToDatabase(ByVal cnString As String)
        Try
            cn = New SqlConnection
            With cn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = cnString
                .Open()
            End With
        Catch ex As Exception
            MessageBox.Show("Error Connection to Database", "Err", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End
        End Try
    End Sub

    Private Sub centerForm(ByRef frm As Form)
        With frm
            .StartPosition = FormStartPosition.CenterScreen
        End With
    End Sub

    Private Sub InsertRecords(ByVal n As String, ByVal age As String, ByVal sex As String, ByVal loc As String)
        cmd = New SqlCommand
        Try
            With cmd
                .Connection = cn
                .CommandText = "Insert into tblinfo ([myname],[age],[sex],[location]) Values('" & n & "', '" & age & "', '" & sex & "', '" & loc & "')"
                .ExecuteNonQuery()
            End With
            MsgBox("Sucessfully Save!", MsgBoxStyle.Information, "")
        Catch ex As Exception
            MsgBox("Error Saving Records", vbExclamation, "")
        End Try
    End Sub

    Private Sub UpdateRecords(ByVal n As String, ByVal age As String, ByVal sex As String, ByVal loc As String, ByVal id As Integer)
        cmd = New SqlCommand
        Try
            With cmd
                .Connection = cn
                .CommandText = "Update tblinfo Set myname = '" & n & "', age = '" & age & "', sex = '" & sex & "', location = '" & loc & "' WHERE ID = " & id & ""
                .ExecuteNonQuery()
            End With
            MsgBox("Sucessfully Updated!", MsgBoxStyle.Information, "")
        Catch ex As Exception
            MsgBox("Error Updating Records", vbExclamation, "")
        End Try
    End Sub

    Private Sub DeleteRecords(ByVal id As Integer)
        cmd = New SqlCommand
        Try
            With cmd
                .Connection = cn
                .CommandText = "Delete From tblInfo WHERE ID = " & id & ""
                .ExecuteNonQuery()
            End With
            MsgBox("Successfully Deleted!", MsgBoxStyle.Information, "")
        Catch ex As Exception
            MsgBox("Error Deleting Records!", vbExclamation, "")
        End Try
    End Sub

    Private Sub initList(ByRef lvw As ListView)
        With lvw.Columns
            .Add("", 0)
            .Add("Name", 160)
            .Add("Age", 50)
            .Add("Sex", 80)
            .Add("Location", 160)
        End With
    End Sub

    Private Sub ShowRecords()
        Dim dr As SqlDataReader
        cmd = New SqlCommand
        With cmd
            .Connection = cn
            .CommandText = "Select * from tblinfo"
        End With
        dr = cmd.ExecuteReader
        Me.ListView1.Items.Clear()
        Do While dr.Read
            With Me.ListView1
                .Items.Add(dr(0))
                With .Items(.Items.Count - 1).SubItems
                    .Add(dr(1).ToString)
                    .Add(dr(2).ToString)
                    .Add(dr(3).ToString)
                    .Add(dr(4).ToString)
                End With
            End With
        Loop
        dr.Close()
    End Sub

    Private Sub displayRecord(ByVal id As Integer)
        Dim dr As SqlDataReader
        cmd = New SqlCommand
        With cmd
            .Connection = cn
            .CommandText = "Select * from tblInfo WHERE ID = " & id & ""
        End With
        dr = cmd.ExecuteReader
        Do While dr.Read
            Me.txtName.Text = dr(1).ToString
            Me.txtAge.Text = dr(2).ToString
            Me.txtSex.Text = dr(3).ToString
            Me.txtLocation.Text = dr(4).ToString
        Loop
        dr.Close()
    End Sub

    Private Sub ListView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.DoubleClick
        If MsgBox("Would You Like To Edit This Record?", vbYesNo + vbQuestion, "") = MsgBoxResult.Yes Then
            Call displayRecord(Me.ListView1.SelectedItems.Item(0).Text)
            Call LockEntry(False)
            Call defaultLocks(False)
            Me.cmdSave.Tag = "update"
            Me.cmdSave.Text = "Save"
        End If
    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim t
        For Each t In Me.GroupBox1.Controls
            If TypeOf t Is TextBox Then
                If t.text = "" Then
                    MessageBox.Show("Pls. Complete Entries!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    Exit Sub
                End If
            End If
        Next

        Select Case Me.cmdSave.Tag
            Case "save"
                Call InsertRecords(Me.txtName.Text, Me.txtAge.Text, Me.txtSex.Text, Me.txtLocation.Text)
            Case Else
                Call UpdateRecords(Me.txtName.Text, Me.txtAge.Text, Me.txtSex.Text, Me.txtLocation.Text, Me.ListView1.SelectedItems.Item(0).Text)
        End Select
        Call cmdCancel_Click(sender, e)
    End Sub

    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        If MsgBox("Would You Like To Delete This Record?", vbYesNo + vbQuestion, "") = MsgBoxResult.Yes Then
            Call DeleteRecords(Me.ListView1.SelectedItems.Item(0).Text)
            Call cmdCancel_Click(sender, e)
        End If
    End Sub

    Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click
        Me.cmdSave.Tag = "save"
        Call defaultLocks(False)
        Call LockEntry(False)
        Me.txtName.Focus()
        Me.cmdDelete.Enabled = False
    End Sub

    Private Sub defaultLocks(ByVal x As Boolean)
        With Me
            .cmdNew.Enabled = x
            .cmdSave.Enabled = Not x
            .cmdDelete.Enabled = Not x
            .cmdCancel.Enabled = Not x
        End With
    End Sub

    Private Sub LockEntry(ByVal x As Boolean)
        Me.ListView1.Enabled = x
        Me.GroupBox1.Enabled = Not x
    End Sub

    Private Sub mrClean()
        Dim t
        For Each t In Me.GroupBox1.Controls
            If TypeOf t Is TextBox Then
                If t.Text <> "" Then t.Text = ""
            End If
        Next
    End Sub

    Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
        Call defaultLocks(True)
        Call LockEntry(True)
        Call mrClean()
        Call ShowRecords()
    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub
End Class
