How to verify if login name and password are match using a ms access 2010 as DB in VB.Net 2010

Question

User553892048 posted

Good day,

Pls help on the codes that im working on..

I am biginner regarding Database in vb.net

 

I have a ms access as db

Field names as follows..

Uname and UPassword, the table is named as accdb and saved as useracc.mdb in 2k3 format...

While in my vb.net my main page form has the followings

• 2 label boxes for Name and Password
• 2 text boxes namely loginid.text and loginpass.text
• A button named Login

Here are the codes that i am working on

________________

Imports System.Data.OleDb

Public Class Form1

    Private Sub login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles login.Click

        Dim connection As OleDb.OleDbConnection

        Dim mydb, mystr As String

 

        mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                 "Data Source=..\useracc.mdb")

        connection = New OleDb.OleDbConnection(mystr)

        connection.Open()

mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

I am stock here..............

dont know how to compare correctly..

i also try some code but it bypasses the password, meaning if the username is exsisting on the db it will not confirm the password anymore..

pls help....tnx alot

 

Answer 1

User1836848059 posted

Hi,

try this

mydb = "SELECT count(*) FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

Dim MyCmd As New OleDbCommand("mydb",connection)

int count=cint( MyCmd.ExecuteScalar());

if count=1

//go for login and redirect the apge

else 

 // login faild

 

(Note:-Check all vb syntax)

http://www.daniweb.com/web-development/aspnet/threads/6028

Answer 2

User-1199946673 posted

Be aware of SQL injections. To avoid them, always use parameterized queries:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

To create a simple login system using Access, read this:

http://www.mikesdotnetting.com/Article/75/Simple-Login-and-Redirect-for-ASP.NET-and-Access

But instead of reinventing the wheel, you can also use ASP.NET Forms Authentication with Access (or any other DataSource):

http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

But instead of using Access, which isn't really designed for web environments, you can use SQL Server Compact Edition 4.0:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2

Answer 3

User3866881 posted

Hi, you can also try this:

 mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                 "Data Source=..\useracc.mdb")

        connection = New OleDb.OleDbConnection(mystr)

        connection.Open()

mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

Dim com As New Command(mydb,connection)

Dim flag As Boolean = com.Read()

if(flag)

{

    //Login successfully....

}

else

{

   //Login failing……

}

Answer 4

User-1199946673 posted

Hi, you can also try this

Although your example might work, it also opens the door for a SQL injection attack. An attacker will be able to login without knowing a username/password combination!

Answer 5

User-1561814533 posted

 mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

 

As other people have said, if I were to type something like this in the password field:

' OR '1'='1

Then I would have succeeded in bypassing your login check with some sql injection.

It may mean following links and reading a lot more than you want to but hans_v gives the best advice.

 

Answer 6

User3866881 posted

Many thanks for hans_v and frez. Their detailed sample tells you that altough combining splits will work. However it's not safe and will cause the problem of SQL injection. Compared with the solutions. Hans_v is your choice

:)

Answer 7

User553892048 posted

Good Day Sir;

I do have a follow up question..

What if i have 2 tables in my ms access db namely accdb and adminaccdb.

the AIM is will search in the firs table (accdb) and if no account found will search to the second table (adminaccdb)

ive tried the try and catch, if statement but it fails..

what is the best to use??..

 

Thnks for ur help..

sorry for the late update in my threads..

been busy in the past days..

Answer 8

User3866881 posted

Hey, have a nice day man!

Hello, you can try this:

mystr = "Provider=Microsoft.JET.OLEDB.4.0;" & _

                 "Data Source=..\useracc.mdb"

        connection = New OleDb.OleDbConnection(mystr)

        connection.Open()

mydb = "SELECT * FROM accdb where [Uname] = @UName and [Password]=@Password

Dim com As New Command(mydb,connection)

com.Parameters.AddWithValue("@UserName",YourName)
com.Parameters.AddWithValue("@Password",YourPassword)

Dim rd As SqlDataReader = com.Read()

Dim flag As Boolean = rd.Read()

If(flag) Then

 'Login successfully....

 

Else

 'Login failing……
 
 rd.Close()
 
 mydb = "SELECT * FROM adminaccdb where [Uname] = @UName and [Password]=@Password

Dim com As New Command(mydb,connection)

com.Parameters.AddWithValue("@UserName",YourName)
com.Parameters.AddWithValue("@Password",YourPassword)

Dim rd As SqlDataReader = com.Read()

Dim flag As Boolean = rd.Read()
 
 If(flag) Then

  'Login successfully....
 Else
  'Do Login fails things……
 End If
End If

 

Answer 9

User-1199946673 posted

Hello, you can try this:

Decker. Maybe you should take more time to answer. In your example code your using a OleDbConnection (which is OK), a Command and a SQLDataReader.

Furthermore, you don't close and dispose the connection, which is very important when using Access. Also, you; r using a datareader to check if a record is retrieved, which is unnecessary. Instead of SELECT *, SELECT Count(*) will return the amount of records that match the search and can be retrieved using ExecuteScalar. And instead of 2 calls to the database, 1 call with will do the job also with only a few lines of code which will also take care of properly closing and disposing the connection:

 

        Dim Authenticated As Boolean
        Using cn As New OleDbConnection(mystr)
            Using cmd As New OleDbCommand("SELECT Count(*) + (SELECT Count(*) FROM adminaccdb where [Uname] = @UName and [Password]=@Password) FROM accdb where [Uname] = @UName and [Password]=@Password", cn)
                cmd.Parameters.AddWithValue("Uname", "some_username")
                cmd.Parameters.AddWithValue("Password", "xyz")
                cn.Open()
                Authenticated = (cmd.ExecuteScalar > 0)
            End Using
        End Using

 

Answer 10

User-1199946673 posted

What if i have 2 tables in my ms access db namely accdb and adminaccdb

I think you need to rethink your design, because most likely eventually will not work. What if in both tables you've users with the same username. How could you tell which user you're dealing with?

I really think you should first learne more about how to deal with data before you design a login system. Perhaps you better start using forms authentication, read my first reply in this thread....

Answer 11

User553892048 posted

Good day Sir Hans;

any suggestion for that??..

the reason why i separate the table is that different form will appear for a regular user and for an admin.

basically the reason is that for the restrictions...

 

Tnx for ur help..

Answer 12

User-1199946673 posted

the reason why i separate the table is that different form will appear for a regular user and for an admin

That's what I thought already. When you're using forms authentication, you can assign roles to users and secure some pages to be accesible by specific users or users in a role.

More on Forms authentication:

http://www.asp.net/web-forms/security

But by default, this works with SQL Server, but as I said, you can use Access also:

http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

Answer 13

User553892048 posted

Good day...

 

Now i redesign my project..ive created a radio button to differentiate whats the standard from admin..

but i encountered a new problem...if ever there are same password from different user..

is does not log in for both of user that has same password..

Help...

is there a way that i can minimize the code..tnx

Here is my code:

    Legend:

             standarduser - name of the table for standard user

             AdminTable - for the admin user

 

_________________________________________________________________

 

Dim myprovider As String

        Dim mydbstandard As String

        Dim mydbadmin As String

 

        myprovider = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                      "Data Source=..\accountdb.mdb")

        myconnection = New OleDb.OleDbConnection(myprovider)

        mydbstandard = "Select * From standarduser Where UserName = '" & Loginid.Text & "' AND UPassword = '" & loginpass.Text & "'"

        mydbadmin = "Select * From AdminTable Where UserAdmin = '" & Loginid.Text & "' AND PassAdmin = '" & loginpass.Text & "'"

        myconnection.Open()

 

        If suser.Checked = True Then

            Try

                Dim mycommands As OleDb.OleDbCommand

                mycommands = New OleDbCommand(mydbstandard, myconnection)

                Dim sdr As OleDb.OleDbDataReader = mycommands.ExecuteReader

                If (sdr.Read() = True) Then

                    Me.Hide()

                    MsgBox("Welcome: " & Loginid.Text)

                    profilepage.userpofilename.Text = Me.Loginid.Text

                    profilepage.Show()

                Else

                    MsgBox("No exsisting User")

                    Loginid.Text = ""

                    loginpass.Text = ""

                End If

            Catch ex As Exception

            End Try

        ElseIf (auser.Checked = True) Then

            Try

                Dim mycommanda As OleDb.OleDbCommand

                mycommanda = New OleDbCommand(mydbadmin, myconnection)

                Dim sdr As OleDb.OleDbDataReader = mycommanda.ExecuteReader

                If (sdr.Read() = True) Then

                    Me.Hide()

                    MsgBox("Welcome: " & Loginid.Text)

                    AdminPage.Adminname.Text = Me.Loginid.Text

                    AdminPage.Show()

                Else

                    MsgBox("No exsisting Adminastrator")

                    Loginid.Text = ""

                    loginpass.Text = ""

                End If

            Catch

            End Try

 

        ElseIf (suser.Checked = False And auser.Checked = False) Then

                MsgBox("Choose type of user")

        Else

                MsgBox("User name and password did not match")

                Loginid.Text = ""

                loginpass.Text = ""

        End If

        myconnection.Close()

 

_______________________________________________________________________________________

 

Tnx for the help..

Answer 14

User-1199946673 posted

You have been directed to many links already explaining how to write better and more secure code, but you don't seem to read them. I suggest you firts start to read them!