MyFirstSQL_vb.net__sourceCode

Imports System.Data.Sql
Imports System.Data.SqlClient


'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

'Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;



'================================================================================
'database name = FirstSQL                                                       ||
'table name    = MyTbl                                                          ||
'                                                                               ||
'column names:                                                                  ||
'                                                                               ||
'MyID     NVARCHAR(50)                                                          ||
'MyFNAME  NCHAR(10)                                                             ||
'MyLNAME     NCHAR(10)                                                          ||
'MyMNAME     NCHAR(10)                                                          ||
'MyNUMBER NVARCHAR(50)                                                          ||
'=================================================================================


Public Class MainForm

    Dim SQL As New sqlControlClass
    Public Sub CheckConn()
        If SQL.HasConn = True Then
            Status.Text = "Connection has been Established"
        Else
            Status.BackColor = Color.Red
            Status.Text = "Connection has   NOT     Established"
        End If

    End Sub


    Public Sub Populate()
        Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}
        Dim cmd As New SqlCommand
        Dim Q As String = "SELECT * from MyTbl"

        Dim DA As New SqlDataAdapter
        Dim DT As New DataTable




        CONN.Open()
        cmd.Connection = CONN


        LVnames.Columns.Clear()
        LVnames.Items.Clear()

        With LVnames
            .Columns.Add("ID", 30, HorizontalAlignment.Left)
            .Columns.Add("First Name", 1000, HorizontalAlignment.Left)
            .Columns.Add("Last Name", 1000, HorizontalAlignment.Left)
            .Columns.Add("Middle Name", 1000, HorizontalAlignment.Left)
            .Columns.Add("Number", 1000, HorizontalAlignment.Left)
        End With



        'cmd
        With cmd
            .CommandText = Q
            .Connection = CONN
        End With


        'adapter
        With DA
            .SelectCommand = cmd
            .Fill(DT)
        End With


        For i = 0 To DT.Rows.Count - 1

            With LVnames
                .Items.Add(DT.Rows(i)("MyID"))
                .Items.Add(DT.Rows(i)("MyFNAME"))
                .Items.Add(DT.Rows(i)("MyLNAME"))
                .Items.Add(DT.Rows(i)("MyMNAME"))
                .Items.Add(DT.Rows(i)("MyNUMBER"))
            End With


        Next





    End Sub

    Public Sub rECords()
        Dim Q As String = "INSERT INTO MyTbl (MyID,MyFNAME,MyLNAME,MyMNAME,MyNUMBER) VALUES ('" & txtID.Text & "','" & txtFNAME.Text & "','" & txtLNAME.Text & "','" & txtMNAME.Text & "','" & txtNumber.Text & "' )"
        Dim CMD As New SqlCommand
        Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}

        With CMD
            .Connection = CONN
            .CommandText = Q
            .ExecuteNonQuery()

        End With



    End Sub

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CheckConn()
        Populate()



    End Sub


    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

        Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}
        Dim cmd As New SqlCommand

        CONN.Open()
        cmd.Connection = CONN


        cmd.CommandText = "INSERT INTO MyTbl (MyID, MyFNAME, MyLNAME, MyMNAME, MyNUMBER) VALUES (@MyID, @MyFNAME, @MyLNAME, @MyMNAME, @MyNUMBER)"

        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyID", txtID.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyFNAME", txtFNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyLNAME", txtLNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyMNAME", txtMNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyNUMBER", txtNumber.Text))


        cmd.ExecuteNonQuery()
        CONN.Close()

        MsgBox("Successfull")

    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Try
            Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}
            Dim cmd As New SqlCommand

            Dim DA As New SqlDataAdapter
            Dim DT As New DataTable



            CONN.Open()
            cmd.Connection = CONN

            cmd.CommandText = "SELECT * FROM MyTbl where MyID = @MyID"
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyID", txtSearch.Text))

            DA.SelectCommand = cmd
            DA.Fill(DT)


            txtID.Text = DT.Rows(0)(0).ToString()
            txtFNAME.Text = DT.Rows(0)(1).ToString()
            txtLNAME.Text = DT.Rows(0)(2).ToString()
            txtMNAME.Text = DT.Rows(0)(3).ToString()
            txtNumber.Text = DT.Rows(0)(4).ToString()



            cmd.ExecuteNonQuery()
            CONN.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try


    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}
        Dim cmd As New SqlCommand

        CONN.Open()
        cmd.Connection = CONN

        cmd.CommandText = "UPDATE MyTbl set MyID=@MyID, MyFNAME=@MyFNAME, MyLNAME=@MyLNAME, MyMNAME=@MyMNAME, MyNUMBER=@MyNUMBER    WHERE MyID=@MyID"
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyID", Convert.ToInt16(txtID.Text)))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyFNAME", txtFNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyLNAME", txtLNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyMNAME", txtMNAME.Text))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyNUMBER", txtNumber.Text))

        cmd.ExecuteNonQuery()
        CONN.Close()
        MsgBox("Success")


    End Sub

    Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click


        Try
            Dim CONN As New SqlConnection With {.ConnectionString = "Server=HP-AC\SQLEXPRESS;Database=FirstSQL;Trusted_Connection=True;"}
            Dim cmd As New SqlCommand


            CONN.Open()
            cmd.Connection = CONN

            cmd.CommandText = "DELETE FROM MyTbl  WHERE MyID = '" & txtID.Text & " '"

            MsgBox("Success")

            Populate()

            txtSearch.Text = ""
            txtID.Text = ""
            txtFNAME.Text = ""
            txtLNAME.Text = ""
            txtMNAME.Text = ""
            txtNumber.Text = ""


            cmd.ExecuteNonQuery()
            CONN.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub btnRefr_Click(sender As Object, e As EventArgs) Handles btnRefr.Click
        Populate()

        txtSearch.Text = ""
        txtID.Text = ""
        txtFNAME.Text = ""
        txtLNAME.Text = ""
        txtMNAME.Text = ""
        txtNumber.Text = ""

    End Sub
End Class