Imports System.Data
Imports System.Data.SqlClient

Public Class LogInForm
    Public sqlConnection As SqlConnection

    Private Sub LogInForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sqlString As String = "Server=localhost;" + _
        "Data Source=Susan\SQLEXPRESS;Database=CSE_DEPT;" + _
        "Integrated Security=SSPI"
        sqlConnection = New SqlConnection(sqlString)
        If sqlConnection.State = ConnectionState.Open Then
            sqlConnection.Close()
        End If
        Try
            sqlConnection.Open()
        Catch SqlExceptionErr As SqlException
            MessageBox.Show(SqlExceptionErr.Message, "SQL Error")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            MessageBox.Show(InvalidOperationExceptionErr.Message, "SQL Error")
        End Try

        If sqlConnection.State <> ConnectionState.Open Then
            MessageBox.Show("Database connection is Failed")
            Exit Sub
        End If
    End Sub

    Private Sub TabLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabLogIn.Click
        Dim cmdString1 As String = "SELECT user_name, pass_word, faculty_id, student_id FROM LogIn "
        Dim cmdString2 As String = "WHERE (user_name LIKE @Param1 ) AND (pass_word LIKE @Param2)"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim paramUserName As New SqlParameter
        Dim paramPassWord As New SqlParameter
        Dim LogInTableAdapter As New SqlDataAdapter
        Dim sqlDataTable As New DataTable
        Dim sqlCommand As New SqlCommand
        Dim selForm As New SelectionForm

        paramUserName.ParameterName = "@Param1"
        paramUserName.Value = txtUserName.Text
        paramPassWord.ParameterName = "@Param2"
        paramPassWord.Value = txtPassWord.Text
        sqlCommand.Connection = sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add(paramUserName)
        sqlCommand.Parameters.Add(paramPassWord)
        'sqlCommand.Parameters.AddWithValue("@Param1", txtUserName.Text)
        'sqlCommand.Parameters.AddWithValue("@Param2", txtPassWord.Text)
        LogInTableAdapter.SelectCommand = sqlCommand
        LogInTableAdapter.Fill(sqlDataTable)
        If sqlDataTable.Rows.Count > 0 Then
            selForm.Show()
            Me.Hide()
        Else
            MessageBox.Show("No matched username/password found!")
        End If
        sqlDataTable.Dispose()
        sqlDataTable = Nothing
        sqlCommand.Dispose()
        sqlCommand = Nothing
        LogInTableAdapter.Dispose()
        LogInTableAdapter = Nothing
    End Sub

    Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
        ' clean up the objects used
        sqlConnection.Close()
        sqlConnection.Dispose()
        sqlConnection = Nothing
        Me.Close()
    End Sub

    Private Sub ReadLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReadLogIn.Click
        Dim cmdString1 As String = "SELECT user_name, pass_word, faculty_id, student_id FROM LogIn "
        Dim cmdString2 As String = "WHERE (user_name LIKE @name ) AND (pass_word LIKE @word)"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim paramUserName As New SqlParameter
        Dim paramPassWord As New SqlParameter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim selForm As New SelectionForm

        paramUserName.ParameterName = "@name"
        paramUserName.Value = txtUserName.Text
        paramPassWord.ParameterName = "@word"
        paramPassWord.Value = txtPassWord.Text
        sqlCommand.Connection = sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add(paramUserName)
        sqlCommand.Parameters.Add(paramPassWord)
        sqlDataReader = sqlCommand.ExecuteReader
        If sqlDataReader.HasRows = True Then
            selForm.Show()
            Me.Hide()
        Else
            MessageBox.Show("No matched username/password found!")
        End If
        sqlCommand.Dispose()
        sqlCommand = Nothing
        sqlDataReader.Close()
        sqlDataReader = Nothing
    End Sub
End Class
