ss

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


Public Class Form1



    'trial 1
    Dim sql As New sqlClass
    Public cmd As SqlCommand

    'trial 2
    Dim conn As New SqlConnection With {.ConnectionString = "Server=localhost;Database=EMP201;Trusted_Connection=True;"}
    Dim S As String
    Dim daSql As New SqlDataAdapter(S, conn)
    Dim dsSql As New DataSet




    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        If sql.hconnection = True Then
            status.Text = "Database connection succeed"

        Else
            status.ForeColor = Color.Red
            status.Text = "Database connection failed"
        End If

    End Sub


    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim oConnection As New SqlConnection()
        Dim oCommand As New SqlCommand()


        Dim oAdapter As New SqlDataAdapter()
        Dim dt As New DataTable()


        oConnection.ConnectionString = "Server=localhost;Database=EMP201;Trusted_Connection=True;"
        oConnection.Open()

        oCommand.Connection = oConnection
        oCommand.CommandText = "SELECT * FROM EMPLOYEES WHERE EMPID = @EMPID"
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EMPID", txtbxSearch.Text))

        oAdapter.SelectCommand = oCommand
        oAdapter.Fill(dt)

        txtbxEmpID.Text = dt.Rows(0)(0).ToString()
        txtbxLname.Text = dt.Rows(0)(1).ToString()
        txtbxFname.Text = dt.Rows(0)(2).ToString()
        txtbxMname.Text = dt.Rows(0)(3).ToString()



    End Sub

    Private Sub btnADD_Click(sender As Object, e As EventArgs) Handles btnADD.Click

        Dim oConnection As New SqlConnection()
        Dim oCommand As New SqlCommand()



        oConnection.ConnectionString = "Server=localhost;Database=EMP201;Trusted_Connection=True;"
        oConnection.Open()

        oCommand.Connection = oConnection
        oCommand.CommandText = "INSERT INTO Employees (LNAME, FNAME, MNAME, EMPID) VALUES (@LNAME, @FNAME, @MNAME, @EMPID)"
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LNAME", txtbxLname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FNAME", txtbxFname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MNAME", txtbxMname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EMPID", Convert.ToInt16(txtbxEmpID.Text)))

        oCommand.ExecuteNonQuery()


    End Sub



    Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click
        Dim oConnection As New SqlConnection()
        Dim oCommand As New SqlCommand()


        Dim oAdapter As New SqlDataAdapter()
        Dim dt As New DataTable()

        oConnection.ConnectionString = "Server=localhost;Database=EMP201;Trusted_Connection=True;"
        oConnection.Open()


        oCommand.Connection = oConnection

        oCommand.CommandText = "DELETE FROM EMPLOYEES WHERE EMPID = '" & txtbxEmpID.Text & "'"                                     'oCommand.CommandText = "DELETE FROM EMPLOYEES (LNAME,FNAME,MNAME) WHERE EMPID = @EMPID"



        oCommand.ExecuteNonQuery()




    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim oConnection As New SqlConnection()
        Dim oCommand As New SqlCommand()


        oConnection.ConnectionString = "Server=localhost;Database=EMP201;Trusted_Connection=True;"
        oConnection.Open()

        oCommand.Connection = oConnection
        oCommand.CommandText = "UPDATE Employees set LNAME=@LNAME, FNAME=@FNAME, MNAME=@MNAME WHERE EMPID= @EMPID"
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LNAME", txtbxLname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FNAME", txtbxFname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MNAME", txtbxMname.Text))
        oCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EMPID", Convert.ToInt16(txtbxEmpID.Text)))

        oCommand.ExecuteNonQuery()
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        txtbxFname.Text = ""
        txtbxLname.Text = ""
        txtbxMname.Text = ""
    End Sub

    Private Sub txtbxSearch_TextChanged(sender As Object, e As EventArgs) Handles txtbxSearch.TextChanged

    End Sub
End Class