Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: Open different forms depending on SQL Statement

    I want to edit this code so that depending on the Status of the Employee a different form will open.
    Example: ManagerForm, UserForm
    It gives a warning if a user logs in incorrectly. The column in the table Employees will be StatusID which is a number that relates to Admin,User,Manager etc.

    Any help would be appreciated or point me in the right direction.


    Code:
    connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
            command.CommandText = "SELECT * FROM [tblEmployees] WHERE FirstName= '" & txtName.Text & "'AND Password='" & txtPassword.Text & "';"
            connection.Open()
    
            command.Connection = connection
    
            adaptor.SelectCommand = command
            adaptor.Fill(dataset, "0")
    
            Dim count = dataset.Tables(0).Rows.Count
    
            If count > 0 Then
                ManagerForm.Show()
                    Me.Hide()
    
            Else
                    MsgBox("Incorrect login", MsgBoxStyle.Critical)
                    txtName.Clear()
                    txtPassword.Clear()
    
                End If
    Im using Visual Studio 2010.

  2. #2
    Join Date
    Aug 2004
    Posts
    178
    As this code wasn't working I did some research and found this other code that kind of does what I want

    Code:
    Imports System.Data
    Imports System.Data.OleDb
    
    Public Class Login
        Dim connection As New OleDbConnection
    
        Private Sub btnLogin_Click(sender As System.Object, e As System.EventArgs) Handles btnLogin.Click
            
    
            connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
    
            Try
                Dim adaptor As New OleDbDataAdapter
                Dim dataset As New DataSet
                Dim sql As String = "SELECT FirstName, Password, StatusID FROM tblEmployees WHERE FirstName= '" & txtName.Text & "'AND Password='" & txtPassword.Text & "'"
                Dim command As New OleDbCommand(sql)
    
                command.Connection = connection
                connection.Open()
    
                adaptor.SelectCommand = command
                adaptor.Fill(dataset, "0")
    
                Dim sqlRead As OleDbDataReader = command.ExecuteReader()
    
                If sqlRead.Read Then
                    If sqlRead[2] == 2 Then
                        AdminForm.Show()
                    ElseIf sqlRead[2] == 3 Then
                        ManagerForm.Show()
                    ElseIf sqlRead[2] == 4 Then
                        SupervisorForm.Show()
                    ElseIf sqlRead[2] == 5
                        UserForm.Show()
                    End If
    
                Else
                    MsgBox("Incorrect Login", MsgBoxStyle.Critical)
                    txtName.Clear()
                    txtPassword.Clear()
    
                    txtName.Focus()
                End If
    
            Catch ex As Exception
                MessageBox.Show("Failed to connect to databse. System Error:" & ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            End Try
    
    
        End Sub
    End Class
    I am getting an error on this code for line 27. Line 27 is
    Code:
    If sqlRead[2] == 2 Then
                        AdminForm.Show()
    and I am getting the error "Value of type 'System.Data.OleDb.OleDbDataReader' cannot be converted to 'Boolean'."

    Any help would be appreciated.

  3. #3
    Join Date
    Aug 2004
    Posts
    178
    ok I now have this solved with the following code

    Code:
            Dim connection As New SqlClient.SqlConnection
            Dim command As New SqlClient.SqlCommand
            Dim adaptor As New SqlClient.SqlDataAdapter
            Dim dataset As New DataSet
            Dim accesslevel As Integer
    
            connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
            command.CommandText = "SELECT StatusID FROM tblEmployees WHERE FirstName= '" & txtName.Text & "'AND Password='" & txtPassword.Text & "'"
            connection.Open()
            command.Connection = connection
            adaptor.SelectCommand = command
            adaptor.Fill(dataset, "0")
            accesslevel = CType(command.ExecuteScalar(), Integer)
    
            Try
    
                If accesslevel = 2 Then
                    AdminForm.Show()
                ElseIf accesslevel = 3 Then
                    ManagerForm.Show()
                ElseIf accesslevel = 4 Then
                    SupervisorForm.Show()
                ElseIf accesslevel = 5 Then
                    UserForm.Show()
    
                Else
                    MsgBox("Incorrect Login", MsgBoxStyle.Critical)
                    txtName.Clear()
                    txtPassword.Clear()
                    txtName.Focus()
                End If
    
            Catch ex As Exception
                MessageBox.Show("Failed to connect to databse. System Error:" & ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •