login form connected to the database(microsoft sql server)

[Christopher_12]

hi
i'm struggling to read data from database
i created a table with 5 rows as for Login form
username and password
i would like to have access to forms when i enter password and username of department 1
it shows department 1 but when i enter password and username for department 2 it still showing department 1
i need help!!!!!!

here is my code

Imports System.Data.SqlClient

Public Class Frmlogin

    Private Sub BtnLogin_Click(sender As Object, e As EventArgs) Handles BtnLogin.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader


        con.ConnectionString = "Data Source=OCHO_CINCO;Initial Catalog=CGHMS;Integrated Security=True"
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "select log_username, log_password from login where log_username = '" & 
               TxtUsername.Text & "' and log_password='" & Txtpassword.Text & "'"
        dr = cmd.ExecuteReader

        If dr.HasRows Then
            MessageBox.Show("Ekurhuleni")
            FrmDashBoardMenuDept1.Show()
            Me.Hide()

        ElseIf dr.HasRows Then
            MessageBox.Show("West Rand")
            FrmDashBoardMenuDept2.Show()
            Me.Hide()

        Else
            MessageBox.Show("Invalid Username and Password")

        End If
        con.Close() 

[Hardz]

Hi Christopher,

Use an indicator as departmentId so that you can login to either department1 or department2. And use parameterized query to avoid sql injection attack.

Private strCon As String = "Data Source=IT-Hardz\SqlExpress;Initial Catalog=CGHMS;Integrated Security=True "
    Private Sub btnLogIn_Click(sender As System.Object, e As System.EventArgs) Handles btnLogIn.Click
        Dim cmd As New SqlCommand()
        Using con As New SqlConnection(strCon)
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select log_username, log_password, DepartmentId From Login Where log_username = @username And " + "log_password = @password "
            cmd.Parameters.AddWithValue("@username", txtUserName.Text)
            cmd.Parameters.AddWithValue("@password", txtPassword.Text)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                Dim indicator As Boolean = False
                While dr.Read()
                    Dim mydept As String = dr("DepartmentId").ToString()
                    If dr.HasRows AndAlso mydept = "1" Then
                        MessageBox.Show("Department1")
                        department1.Show()
                        indicator = True
                    ElseIf dr.HasRows AndAlso mydept = "2" Then
                        MessageBox.Show("Department2")
                        department2.Show()
                        indicator = True
                    End If
                End While
                If Not indicator Then
                    MessageBox.Show("Invalid User name and password")
                End If
            End Using
        End Using
    End Sub

Hardz

[Santanu.Das]

@Hardz : This is good suggession to create a field for department Id. And obviously it is needful to enter in a particular department by checking User Id and Password, where all id and passwords for all departments have stored in a single table. But your codes specially between line 12 and line 23. Do not under stand why are you using if elseif statement 2 times for a single work. You can do it most simply by taking an another field for department name into the database. And you can show the department name.

Using con As New SqlConnection(strCon)
            .Open()
            cmd.Connection = con
            cmd.CommandText = "Select DepartmentId, DepartmentName From Login Where log_username = @username And log_password = @password"
            cmd.Parameters.AddWithValue("@username", txtUserName.Text)
            cmd.Parameters.AddWithValue("@password", txtPassword.Text)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                If .HasRows Then
                    MessageBox.Show(.("DepartmentName") & Space(5) & .("DepartmentId"))
                Else
                    MessageBox.Show("Invalid User name and password")
                End If
                .Close()
            End Using

            cmd.Dispose()
            .Close()
        End Using

Hope it can help.

[Christopher_12]

according to your code
what if the departmentid is in another table which is called department (dept_id is the foreign key)

[Santanu.Das]

@Christopher_12 : It is a perfect question

what if the departmentid is in another table which is called department (dept_id is the foreign key)

First check for uId and Pwd, If they exist then pick up the Dept_Id and go for your jobs for another tables, if they do not exist then show a message and exit.

[Hardz]

@shark: yes, you are right, the one that I have provided causes a code redundancy which I didn't notice. :) Thanks...

@chris: I think you just need to join the two tables, Departments and logins, in which Dept_id will be used as a reference. Follow these links on how to join the tables: link1Link2