• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 984
  • Last Modified:

Login form using VisualBasic.NET, ADO.NET and MS Access

Hi, i am new to programming..

Here is what I am trying to do:  I will have like 4 forms, each forms can by access by different type of user assigned in access table when login..

Table Users
user   password    type       name      Address
john        doe         1           John       at home

Login Form:   will have txtUser and txtPassword,   how will I check user's input username and password against the table?
                     Once the user login, how do I pull all the information just on that ONE user that just logged in, example, his type, name and his addresss to be loaded  into a different form. Aso, if user is type 1 , then load form Type1 for that user..  Basically I am not sure how to pull the exact field from the database to assigned it to a variable.

Here's what I have for Login form but. it keep showing "Valid" message box if i enter the correct username and password then "Invalid" msg box as if it keep checking for the next user name and password.  How do I end the loop once it finds the matching user and password?

   Dim password As String
    Dim user As String
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

        Dim tempUser As String
        Dim tempPassword
        Dim dv As DataView
        dv = DsEmployees.Tables(0).DefaultView
        empID = txtUser.Text
        password = txtPassword.Text
        ' Loop through table and compare each record against user's entry
        For Each datarow As DataRowView In dv
            ' Extract user id from the current row
            tempEmpID = datarow.Item("empid").ToString()
            tempPassword = datarow.Item("password").ToString()
            ' Compare user id against user's entry
            If (tempEmpID = empID) And (tempPassword = password) Then
                Exit For
            End If
    End Sub

End Class

Thank you guys and gals!!!
1 Solution
You need not create a DataSet at all if you just want to validate a user and open a page according to his role.

Just open a DataReader with a Query

Dim Qry as String
Qry=String.Format("Select Type From Users Where User='{0}' And password='{1}'",TxtUser.Text, TxtPassword.Text)

Run this Query and Open a DataReader
   if DatReader.GetInteger(0)=1 then 'Check for Type value
       'Open Form1
   else .....
catch Exceptions Here (if no record you know what to do....)

End Try
VtecVsixAuthor Commented:
will I be able to get the user's info using this method? like his full name, address, etc. once he's logged in?

let's say when John login, a new form will have a label:  Welcome (John)
Well yes modify the query to select Name and address etc. as well
then after Read of DataReader store the values in a variable and pass to the new form so that it displays the name on the form

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

VtecVsixAuthor Commented:
If you could, please help me with that using Dataset instead of DataRead? as i am never exposed to DataRead method... or if you can show me a step by step on how to use DataRead.. Thank you so muchh!!!

Just use the SQLReader from .NET

I've pasted some .NET code .. it's in C# you have to port in to VB.

            public SqlDataReader doQuery(string lsQuery)
                  string lsDBconnection = ConfigurationSettings.AppSettings["DBConnectionString"];

                  SqlConnection con = new SqlConnection(lsDBconnection);
                  SqlCommand cmd = new SqlCommand(lsQuery, con);
                  SqlDataReader loDataReader;

                        loDataReader = cmd.ExecuteReader();
                        return loDataReader;
//                  catch (Exception e)
//                        throw new Exception(e.Message);
                        return null;

You need to call this function this way... see the example code below.

            public void doAuditLog(string lsEntiteit, string lsActie)
                        doExecQuery("insert into EDM..EBeheerAuditlog (datum, tijd, gebruiker, machine, entiteit, entiteit_actie) " +
                                          "values ('" + DateTime.Now.ToString("dd-MM-yyyy") + "','" + DateTime.Now.ToString("HH:mm:ss") + "','" +
                                          Session["Gebruiker"] + "','" + Session["Computer"] + "','" + lsEntiteit + "','" +
                                          lsActie + "')");
                  catch(Exception e)
                        throw new Exception (e.Message);
If you are using VB.NET here goes....
On Click of OK button on the Login Page

Dim Cn As OleDbConnection = New OleDbConnection(YourConnectionString)
Dim Rdr As OleDbDataReader
Dim CmdText As String = String.Format("Select Type,name From Users Where User='{0}' And password='{1}'",TxtUser.Text, TxtPassword.Text)
Dim Cmd As OleDbCommand = New OleDbCommand(CmdText, Cn)
Rdr = Cmd.ExecuteReader
If Rdr.HasRows() Then
   If Rdr(0)=1 then 'If value of Type is 1 Then Open MyFormOne
       Dim Frm as New MyFormOne(Rdr(1)) 'Here the Form MyFormOne takes the UserName as Argument in Constructor
    else If Rdr(0)=2 then 'If 2 Open MyFormTwo and So On
       Dim Frm as New MyFormTwo()
       Frm.UserName=Rdr(1) 'Or you can Pass the userName as Property Value to the New Form
       Frm.Show 'Now Your Opened Form has the UserName Display it Where you Like
    End if
    MessageBox.Show("Invalid UserID or Password")
End If
The above solution is adequate for a simple login, but what happens when you have a large number of user types and parameters you want to look up ? - if thens will be very inefficient

To give you a suggestion, and this will depend on the type and scope of your project, I use a slightly different method for my users login to a web application.

I have a business objects class called users which contins, amongst other things a function which does almost the same as the above code. However - once I have returned the usr type to the requester, I use this to look up an xml file for the users permissions.

eg - i return from the function one of the folowing values for the type :

admin, db_admin, default, login

This is then passed into an xml reader class to find not only the start page, but also the location of an xml image location file. eg:


      <!-- Location of Advert / Image files for home page -->
      <!-- Location of home page -->

This way I can use the same homepage for different users, and show different messages on it.

You can of course take this to the nth degree ! - but it really depends on your app and how flexible you want to make it.

VtecVsixAuthor Commented:
Hi arif_eqbal

your solution is what i am looking for but here's the error i got

Type 'OleDbConnection' is not defined.
Type 'OleDbCommand' is not defined.
Type 'OleDbDataReader' is not defined.

VtecVsixAuthor Commented:
OR can someone correct the For Each loop that I have in my original where it compare to ALL the data in database instead of ONe by one

you need to Import the NameSpace

Imports System.Data.OleDb
VtecVsixAuthor Commented:
Now i got:

Specified cast is not valid
On Which Line????
What Code???
VtecVsixAuthor Commented:
Hey Thanks i got it

one question though..

Let's say if i want to list all user that live in a certain area code..

i would

Dim CmdText As String = String.Format("Select fname, lname From Users Where areacode='{0}'", areaCode.Text)


Frm.txtFirstname.Text = Rd(0).ToString()

Would this work?
It will work but Not as you expect it to

Let me just put in brief how a dataAdapter works,

If you have worked with VB6 you might be aware of ReadOnly, ForwardOnly Recordsets a DataAdapter is similar to that. So a DataAdapter may have more than One record and you can just move forward to each record, moving to next record is achieved by calling the Read Method.

In our Login Example we were giving a Where Clause which we were sure will either return one Record or None so we just used one single Read method call.
However when you give a Query giving a condition Where AreaCode='12345' it might contain a number of Records So You need to Loop through the DataAdapter to get All Records.

So Now you should go like this...

While Rdr.Read()
     'Put the values in some sort of an Array or ListBox or wherever
     'Rather than TextBox because the values will be overwritten in loop
     Frm.txtFirstname.Text = Rd(0).ToString()
     Frm.txtLastname.Text = Rd(1).ToString()
VtecVsixAuthor Commented:
So I still do not need to create a Dataset ?
I am really interest in using a dataset...

Thank you , you have been a great help!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now