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

Posted on 2004-09-08
Medium Priority
Last Modified: 2008-01-09
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!!!
Question by:VtecVsix
LVL 19

Expert Comment

ID: 12013080
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

Author Comment

ID: 12013104
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)
LVL 19

Expert Comment

ID: 12013164
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12013261
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!!!

Expert Comment

ID: 12013635

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);
LVL 19

Accepted Solution

arif_eqbal earned 400 total points
ID: 12014580
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

Expert Comment

ID: 12018372
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.


Author Comment

ID: 12019326
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.


Author Comment

ID: 12019522
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

LVL 19

Expert Comment

ID: 12023617
you need to Import the NameSpace

Imports System.Data.OleDb

Author Comment

ID: 12023677
Now i got:

Specified cast is not valid
LVL 19

Expert Comment

ID: 12023687
On Which Line????
What Code???

Author Comment

ID: 12023740
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?
LVL 19

Expert Comment

ID: 12023788
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()

Author Comment

ID: 12029228
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!!!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question