Login System Using MS Access Database

[TrueCoding]

Hi Im new here-I wanted to create a Login System. I am using Visual Basic 2008 Express Edition, Microsoft Access 2007/2010 Beta.

Ok this is my current code-the main problem I have is that I dont how to compare what the user puts into the textboxes and then check that against the data in my database.

Public Class LoginForm1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = D:\Work\Computing\Computing CourseWork\The Quiz DataBase.mdb"

        con.Open()

        If sql = "Select Username ID, Password FROM tblStudentLogin WHERE Username ID = UserName.Text  AND Password = & PassWord.Text " Then

            MsgBox("You are now logged in " & UserName.Text)
            Me.Hide()
            StudentMainScreen.Show()
        Else
            MsgBox("Incorrect Login Details - Please try again.")
        End If

        con.Close()


    End Sub

When I run/debug the program and enter the correct username and password into the program it comes up with the MsgBox("Incorrect Login Details - Please try again.").
The connection string for where the database is coming from is right, because I checked it..so must be something wrong with the SQL Coding I guess. I honestly don't have a clue what's wrong with this code-according to my logic it makes sense, but maybe I'm missing some keyword or something? Please any help at all will be greatly appreciated!

Thankyou

[samxcui]

The sql is defined as string, before you assign any value to sql, it's nothing. and when you compare sql with "Select Username ID, Password FROM tblStudentLogin WHERE Username ID = UserName.Text AND Password = & PassWord.Text ", of course the result is false.

What you should do is to run the select statement and fill the result into the datatable you defined. Then to check if any row retunred. If teh rows.count>=1, means the userID and password match one of the record.

[pritesh2010]

your sql query is wrong write where condition with qoutes. like

sql="Select UserName,Password from tblStudentLogin
 WHERE [Username ID] ='" & trim(UserName.Text) & "'  AND [Password =]'" & trim(PassWord.Text) & "' "

try this will run.

[pritesh2010]

your sql query is wrong write where condition with qoutes. like

sql="Select UserName,Password from tblStudentLogin
 WHERE [Username ID] ='" & trim(UserName.Text) & "'  AND [Password =]'" & trim(PassWord.Text) & "' "

try this will run.

[TrueCoding]

First of all thanks for the quick replys!

I dont fully understand-what do I Dim SQl as instead of String?
What I mean is

Dim Sql as (what?)

Also Pritesh

sql="Select UserName,Password from tblStudentLogin
 WHERE [Username ID] ='" & trim(UserName.Text) & "'  AND [Password =]'" & trim(PassWord.Text) & "' "

Your second line of Code is an error because ' (goes green) in Vb - which means that you want to comment the code. Also, why do you have so many & ? Is that the way Sql is suppposed to be coded?

I dont mean to be rude or anything, just not understanding because Im not that experienced with Sql/Vb lol.

Thanks again.

[TrueCoding]

Also one more thing I thought of a different approach trying to code this.......

Dim sql As New DbType

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = D:\Work\Computing\Computing CourseWork\The Quiz DataBase.mdb"

        sql = "SELECT * FROM tblStudentLogin WHERE Username ID and Password = 'UserName.text' ,'PassWord.text'"
    End Sub

I want to put this into an If statement but not sure how i would I do this...What I mean is IF the UserName.text and PassWord.text = Whats in the table THEN it links to the next form, but if it doesnt match ELSE MsgBox("Incorrect Details")

Im not sure how I would be able to code this, if someone can help me with this approach or the other it would be greatly appreciated!

Thanks

[pritesh2010]

on the command buttton click write this code

con.ConnectionString="" ''here your connection string
con.open
dim sql as string, dim i as BOOLEAN	
sql="SELECT UserName,Password from tblStudentLogin
Where [UaserName]='" & Trim(txtuname.text) & "'And [Password]='" & trim(txtpwd.text) & "'"
cmd= new OledCommand(sql,con)
i=cmd.ExecuteNonquery
if (i=true) then
''welcome to system
else
'' login fails
end if
cmd= nothing
con.close

''+++++================================
here is second way to code in vb.net

con.ConnectionString="" ''here your connection string
con.open
dim sql as string
 dim i as BOOLEAN	
dim dr as oledDatareader
sql="SELECT UserName,Password from tblStudentLogin
Where [UaserName]='" & Trim(txtuname.text) & "'And [Password]='" & trim(txtpwd.text) & "'"
cmd= new OledCommand(sql,con)
dr=cmd.Executereader()

if (dr.hasRows)=false then exit sub
else
while(dr.read())
if (txtuname.text)=dr(0).Tostring() and (txtpwd.text)=dr(1).tostring() then

''welcome to system
else
'' login fails
end if
end while
dr.close()
cmd= nothing
con.close

check this both code will run

[pritesh2010]

on the command buttton click write this code

con.ConnectionString="" ''here your connection string
con.open
dim sql as string, dim i as BOOLEAN	
sql="SELECT UserName,Password from tblStudentLogin
Where [UaserName]='" & Trim(txtuname.text) & "'And [Password]='" & trim(txtpwd.text) & "'"
cmd= new OledCommand(sql,con)
i=cmd.ExecuteNonquery
if (i=true) then
''welcome to system
else
'' login fails
end if
cmd= nothing
con.close

''+++++================================
here is second way to code in vb.net

con.ConnectionString="" ''here your connection string
con.open
dim sql as string
 dim i as BOOLEAN	
dim dr as oledDatareader
sql="SELECT UserName,Password from tblStudentLogin
Where [UaserName]='" & Trim(txtuname.text) & "'And [Password]='" & trim(txtpwd.text) & "'"
cmd= new OledCommand(sql,con)
dr=cmd.Executereader()

if (dr.hasRows)=false then exit sub
else
while(dr.read())
if (txtuname.text)=dr(0).Tostring() and (txtpwd.text)=dr(1).tostring() then

''welcome to system
else
'' login fails
end if
end while
dr.close()
cmd= nothing
con.close

check this both code will run

[TrueCoding]

Hmmm Ok thanks so much Pritesh...Ill try it out now and tell you if it works =D

[TrueCoding]

One more question Pritesh, do you have like MSN so i can talk to you, because It will be easier to talk between ourselves and solve my problem quicker please.?

[TrueCoding]

VB has 2 problems with the code...Ive attached an image of what the problems...I really dont understand what is wrong there?

Please help
Thanks

[pritesh2010]

your image is not clear so i can't see what is the problem occurs in your page write your code and error so i can help you.
and sorry i don't have MSN.

[TrueCoding]

Oh Ok....

Public Class LoginForm1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim i As Boolean
    Dim cmd = New OleDb(sql, con)


    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = D:\Work\Computing\Computing CourseWork\The Quiz DataBase.mdb"
        con.Open()
       
        sql = "SELECT UserName,Password from tblStudentLogin"

        WHERE("Username ID" = "& Trim(UserName.text) &" And ("PassWord" = "& Trim(PassWord.text)")

        i = cmd.ExecuteNonquery
        If (i = True) Then
            MsgBox("You are now logged in,")
        Else
            MsgBox("Incorrect Login Details.")
        End If
        cmd = Nothing
        con.Close()

    End Sub

1st Error =

Dim cmd = New [U][B]OleDb[/B][/U](sql, con)

-(OleDb) It says "Type Expected".

2nd Error =

[B][U]WHERE[/U][/B]("Username ID" = "& Trim(UserName.text) &" And ("PassWord" = "& Trim(PassWord.text)")

- (WHERE) It says "Name WHERE is not declared."

Thanks for all the help...but I dont understand these errors?

[pritesh2010]

see here u can't defined command as globally
and you are specifying OLEDB, but it's a namespace you should defined it's oledbcommand where you can specify it's sql query.
in global variable u just defined like this

dim sql as string,i As Boolean
dim cmd as OledCommand 
''in button event write

private sub btnok_click
''open your connection
con.open
'' here yor sqlstring Like
sql="SELECT UserName,Password from tblStudentLogin Where Username_ID Like' "& Trim(UserName.text) & " 'And PassWord Like '" & Trim(PassWord.text)& " ' "
cmd=new Oledbcommand(sql,con)
i = cmd.ExecuteNonquery
#
If (i = True) Then
MsgBox("You are now logged in,")
Else
MsgBox("Incorrect Login Details.")
End If
cmd = Nothing
con.Close()
End sub

and in your where condition is also some mistake like at the end of the password.text box theres is messing of &.
try like this.

[TrueCoding]

Ok thanks Pritesh...

Public Class LoginForm1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        Dim sql As String
        Dim i As Boolean
        Dim cmd As OleDb.OleDbCommand

        con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = D:\Work\Computing\Computing CourseWork\The Quiz DataBase.mdb"
        con.Open()
       
        sql = "SELECT Username ID,Password from tblStudentLogin Where Username ID Like' " & Trim(UserName.Text) & " 'And Password Like '" & Trim(PassWord.Text) & " ' "
        cmd = New OleDb.OleDbCommand(sql, con)
        i = cmd.ExecuteNonQuery
#If (i = True) Then
            MsgBox("You are now logged in,")
        Else
            MsgBox("Incorrect Login Details.")
#End If
        cmd = Nothing
        con.Close()

    End Sub

But when I debug/run the program and I try and login it says this error.

1:

i = cmd.ExecuteNonQuery

Error = Syntax Error (missing operator) in query expression 'Username ID'.

What does it mean by missing operator?
Thanks again for all your help Pritesh.

[pritesh2010]

it's simple just remove space from your table tblStudentLogin where you are defined column name [Username Id] and and write like [UsernameId] without space and change in where condition without space. then it will not give any error. if this done make thread as solve.

[TrueCoding]

Ok I amended the code as you said Pritesh..and 1 error still..

Public Class LoginForm1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        Dim sql As String
        Dim i As Boolean
        Dim cmd As OleDb.OleDbCommand

        con.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = D:\Work\Computing\Computing CourseWork\The Quiz DataBase.mdb"
        con.Open()

        sql = "SELECT UsernameID,Password FROM tblStudentLogin WHERE UsernameID Like' " & Trim(UserName.Text) & " 'And Password Like '" & Trim(PassWord.Text) & " ' "
        cmd = New OleDb.OleDbCommand(sql, con)
       [B][U]i = cmd.ExecuteNonQuery[/U][/B]

#If (i = True) Then
            MsgBox("You are now logged in.")
            Me.Hide()
            StudentMainScreen.Show()
        Else
            MsgBox("Incorrect Login Details.")
            UserName.Clear()
            PassWord.Clear()
#End If
        cmd = Nothing
        con.Close()

    End Sub

Error :

i = cmd.ExecuteNonQuery

- No value given for one or more required parameters. Thanks again for the help. Im sorry but I really dont know why Im having so many problems with this program:angry:

[pritesh2010]

see this is my login form code

getconnect() '''where i'm open the connection
        sqlstr = "SELECT [EmployeeNO],[Password]FROM [EmpMaster] WHERE [EmployeeNO] like'" & Trim(txtuname.Text) & "' and [Password] like '" & Trim(txtpwd.Text) & "';"
        Dim dr As OleDbDataReader
        cmd = New OleDbCommand(sqlstr, con)
        dr = cmd.ExecuteReader
        If dr.HasRows = False Then
            MessageBox.Show("Invalid userName and Password", "Invalid Employee", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            dr.Close()
            dr = Nothing
            Exit Sub
        Else
           ''messagebox.Show("Valid User")
            dr.Close()
            dr = Nothing
        End If

[SolutionExpert]

Hi ,
Try this code, it is running on my site.

Dim con As New OleDb.OleDbConnection
    Dim sql As String
    Dim dr As OleDb.OleDbDataReader
    Dim cmd As New OleDb.OleDbCommand
    Dim cmd1 As New OleDb.OleDbCommand
    Dim findID, newID As String

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        findID = 0
        con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\San\Projects\Try_Vb_DotNET\LoginSystem.accdb")
        con.Open()
        sql = ""
        sql = "select ID from Table1 where UserName='" & Trim(TextBox1.Text) & "'"
        cmd.Connection = con
        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        If (dr.HasRows) Then

            dr.Read()
            findID = dr.GetValue(0)
            dr.Close()
        End If

        sql = ""
        sql = "select ID from Table1 where Password='" & TextBox2.Text & "' and ID =" & findID & " "

        cmd1.Connection = con
        cmd1.CommandText = sql
        dr = cmd1.ExecuteReader
        If (dr.HasRows) Then
            dr.Read()
            newID = dr.GetValue(0)
            dr.Close()
        End If
        If (findID = newID) Then
            Response.Write("Login is successfully")
        Else
            Response.Write("Wrong Username")
        End If
    End Sub

If it works on your site then please let me know.

Solution Expert

[TrueCoding]

Ok thanyou Pritesh and SolutionExpert...Thanks for your help, and Ill try and see if the code works Solution...

@Pritesh your code is coming up with the same errors

[TrueCoding]

Hi, in response to your kind and very helpful post to my thread..I was wondering at the end of your code it says Response...What did you DIM Response as because on my VB Error says: "Response is not declared."

[pritesh2010]

it's ok not a problem i think you are doing something wrong . but it's working on pc. in another code don't write Response.Write
Because he had done code in for website instead of Response.Write write your Messagebox.Show method. if that code is work that is also good one.

[TrueCoding]

Ok thanks Pritesh but I cant see what IM doin wrong.....

@ Solution look at the attachement because thats the error Im getting.

[pritesh2010]

i had attached the zip file for what i had done in login system pleas see that

[pritesh2010]

i had attached the zip file for what i had done in login system pleas see that

[TrueCoding]

Thanks for that, but still the same error keeps coming up...Has anyone else had this same problem and solved it?

[TrueCoding]

Ok the Login System does work now, but from someone else (not on this forum), but I would like to thank you all for taking the time out to help me :) When I've handed my project in Ill post my code for the successful Login System.

[teena gupta]

Imports System.Data
Imports System.Data.OleDb
Public Class Form7
    Inherits System.Windows.Forms.Form
    Dim mypath = Application.StartupPath & "\login.mdb"
    Dim mypassword = ""
    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mypath & ";Jet OLEDB:Database Password=" & mypassword)
    Dim cmd As OleDbCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sql = "SELECT UserID ,PassID FROM admin WHERE USERID='" & TextBox1.Text & "' AND PASSID='" & TextBox2.Text & "'"

        cmd = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader

        Try
            If dr.Read = False Then
                MessageBox.Show("Authentication failed...")
            Else
                MessageBox.Show("Login successfully...")
                Dim frmDialogue As New Form6

                frmDialogue.ShowDialog()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        conn.Close()


    End Sub

   
  
End Class

try this out!!!

[karthi5390]

i=cmd.ExecuteNonquery i got exception error