How to authorization users for VB.NET with MS ACCESS?

Question

0.00/5 (No votes)

See more:

When I selected "administrator" from the combobox it logged in directly without input from username and password. please help!!

Here's My Codes:

VB

Public Class frmLogin
    Dim sqlcode As String
    Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim Clinton Amarga\Desktop\Class Records\Class Records\bin\Debug\dbClassRecords.accdb"
end class

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
 If cmbLoginType.Text = "Administrator" Then

 Dim sqlLoginCode As String = "select * from tblusers where User_username = '" & txtUsername.Text & "' AND User_password = '" & txtPass.Text & "' AND User_usertype = '" & "Administrator" & "' "
            Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)
            loginCommand.Connection = New OleDb.OleDbConnection(connstring)


            loginCommand.Connection.Open()
            loginCommand.ExecuteNonQuery()
            Dim da As New OleDb.OleDbDataAdapter(loginCommand)

            Dim ds As New DataSet


            da.Fill(ds, "tblusers")

            MessageBox.Show("Welcome Admin")
            frmAdmin.Show()
            Me.Hide()

        ElseIf cmbLoginType.Text = "Teacher" Then

            Dim sqlLoginCode As String = "select * from tblusers where User_username = '" & txtUsername.Text & "' AND User_password = '" & txtPass.Text & "' AND User_usertype = '" & "Teacher" & "' "
            Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)
            loginCommand.Connection = New OleDb.OleDbConnection(connstring)


            loginCommand.Connection.Open()
            loginCommand.ExecuteNonQuery()
            Dim da As New OleDb.OleDbDataAdapter(loginCommand)

            Dim ds As New DataSet


            da.Fill(ds, "tblusers")

            MessageBox.Show("Welcome Teacher")
            frmTeacherForm.Show()
            Me.Hide()

        Else
            MessageBox.Show("Login Failed!!")
            txtUsername.Clear()
            txtPass.Clear()
            txtUsername.Focus()

        End If

 End Sub

My MS ACCESS DATABASE:

tblUsers

User_id | primary Key
User_fname | short text
User_lname | short text
User_username | short text
User_password | short text
User_usertype | short text

What I have tried:

The content in this block was exactly the same as Describe the problem. Refer to Describe the problem section (Comment by Bryian Tan).

Posted 3-Mar-17 3:01am

Jim Clinton

Updated 3-Mar-17 3:47am

Bryian Tan

v2

Add a Solution

Comments

[no name] 3-Mar-17 9:07am

You would probably want to actually check the result of your query....

F-ES Sitecore 3-Mar-17 9:08am

There is no code where you need a password, just step through the code in the debugger. You simply create a SELECT statement, execute it but ignore the results and that's all you do. What part of your code makes you think it will abort the login?

Bryian Tan 3-Mar-17 9:13am

I agreed with your observation :) . Initially I though I had deleted it while modifying the question.

Richard Deeming 3-Mar-17 12:51pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 3-Mar-17 12:51pm

Also, you're storing passwords in plain text. Don't do that.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

Jim Clinton 3-Mar-17 23:09pm

Dim accessReader As OleDbDataReader
accessReader = loginCommand.ExecuteReader
If accessReader.Read Then
If accessReader

--------------------------------------------------------------------------------------
invalidoperationexecption was handled

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2017-03-03T03:47:00

The code to process the return from the sql query does not exist,it will never validate. Moreover, your sql query is inviting SQL injection[^], you should be using parameterized query instead, see the following snippet:

Dim sqlLoginCode As String = "select * from tblusers where User_username = @username AND User_password = @userpassword AND User_usertype = @usertype"

Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)
loginCommand.Connection = New OleDb.OleDbConnection(connstring)

loginCommand.CommandType = CommandType.Text

loginCommand.Parameters.AddWithValue("@username", txtUsername.Text)
loginCommand.Parameters.AddWithValue("@userpassword", txtPass.Text)
loginCommand.Parameters.AddWithValue("@usertype", cmbLoginType.Text)

Dim accessReader As OleDbDataReader
accessReader = loginCommand.ExecuteReader
If accessReader.Read Then
    If accessReader("User_usertype") = "Administrator"  Then
        Console.WriteLine("User is Administrator.")
    Else
        Console.WriteLine("User is Teacher.")
    End If
Else
    Console.WriteLine("Invalid user or password")
End If

This is a quick draft from notepad for demo purpose, adapt it to your need. One last thing, why do you want the user to indicate his role as admin or teacher since you can get it from the database.