UserNames & Password Database

This seems a popular topic over the last few weeks, and it seem a lot get confused with ConnectionString and QueryStrings.
Wheeras if they utilise DataSet Qeuries there tasks will be a lot simpler

1. Creating the database.

In this tutorial I will using Microsoft Access to create my database, but is it possible use other method.
This part of the tutorial is the only section that will differ.

Create a new Database named UserPasswords

Create a new table called Users
Add UserID field Number not Autonumber
Add UserName field Text
Add Password field Text

Set UserID to be the primary key.

Save the table.

Lets add a few users

0 Administrator Administrator
1 User User
2 User2 abc


Save the table.

All done with creating the database now lets use it.


2. Importing the Database.
After creating a new windows form project.
Project -> Add Existing Item -> Find & Select the UserPasswords database.

Tick the Tables box -> Finish

This will the import the database in the project. It will also create an additional item in the solution explorer called UserPasswordsDataSet.xsd this is called a DataSet.


Save the project. In the next part will be using that dataset.


3. Adding a Query
It some point will need some capability to query the database to see if the username and password are valid. Let add part of that capability.

Double click on the dataset. This will open a new window with and overview of all the tables in our database. (Obviously there only in ours.)

You see the table has two sevtions;-
Users Which is are table from the database.
UsersTableAdapter

What's a TableAdapter? The job of a tableadapter is to translate our actions and queries into something the technology used by the database can understand.
If the database was using Microsoft Access then uses Access language, a SQL database uses SQL. It make our tasks and coding simpler. (You'll see.)

Right Click on the TableAdapter and select Add ->Add Query. -> Next


On this select SELCT which returns a single value, this is because we are don't need to return any of the rows to check the validity of a UserName and Password.

Click Next


If you know the SQL needed to accomplish the task enter it here.
Click Query Builder, open up the query builder window.

Modify the query so it looks like


What does =? mean? Simply you're saying to the query I'll tell you later so create a parameter for me to fill in.

Let test it. See in the bottom left a button with the text Execute Query. Click It


For some reason it doesn't display the parameter aliases, but it simple to figure that out there in order top to bottom as they appear in the query.
So in this case the first is UserName then Password.
For the first try; Administrator adminstrator
Click OK and you'll see we get result 1


Try it again but this time mis spell the password (or remove it completely) you see we get no results.
If fact we do it is NOTHING and this is an important point to remember.

We now finished building our query so click OK, this will now return to the form were we could enter the SQL directly. (You'll see it now has a different one.)
Click Next

Name the function ValidateUserNamePassword
Click Next -> Finished

And we're back in the DataSet, with the query added to the datatableadapter.


It this point it is a good idea and practice to save the project.
Next Part: Creating the form and Using the DataTableAdapter


4. Form Creation & Using a DataTable Adapter

On Form1 add two textboxes and a button. Let use good coding practices and give the controls better names than Textbox1.
So rename Textbox1 -> Txt_UserName , Textbox2 -> Txt_Password , Button1 -> But_Validate

Save

To enable use to add the TableAdapter to the Form, we need build the project. So build the project, one of the result of building is three (3) new controls in the toolbox.

Add a UsersTableAdapter to the form.

So far in this tutorial we not written a single line of vb.net code.
Double Click on the button.

In the intellisense you see.

Hang on is that the same name we entered for the query? And you correct. (Didn't it made are coding simpler)


Why are checking the result r for NOTHING?
Remember in Query Builder when we tested, the possible results return were 1 (Valid User) or NOTHING (Incorrect UserName or Password)

Save and run the project, try it out.

5. Review

1. We create the datebase.
2. Add a few entries
3. Imported the database
4. Add a query to the dataset
5. Created a simple login form
6. Use a Data Table Adapter to translate then process our query,
7. Wrote 8 lines of code (6 excluding the comments).
8. Not had to deal with ConnectionStrings
9. Not had to deal with QueryString

To me, I don't mined the additional step of adding a query to a DataSet to simplify the coding.

So now a your a DataSet welding Coding Ninja

I'll do another tutorial soon covering SELECT rows, UPDATES and DELETE.

Hi, I regard this topic as perfect for the go 'cos i've been having problems dealing with this end of my project. But can U please help me out with creating user Name and Pass Word using SQL Server rather than microsoft Access.

Thanks for your assistance in this regards.
Tunde

Very useful tutorial ,

how ever there is a slight problem with your following code:

Dim r = Me.UsersTableAdapter1.ValidateUserNamePassword(Me.Txt_UserName.Text, Me.Txt_UserName.Text)

it should be ;

Dim r = Me.UsersTableAdapter1.ValidateUserNamePassword(Me.Txt_UserName.Text, Me.Txt_Password.Text)

kriaz

Thank You for spotting that.
Edited Tutorial to include correction

skills on this tutorial, it was like wow I actually learned something and I didn't have to think.