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.
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.
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.)
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
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.
Dim r = Me.UsersTableAdapter1.v
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)
Private Sub But_Validate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles But_Validate.Click Private Sub But_Validate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles But_Validate.Click Dim r = Me.UsersTableAdapter1.ValidateUserNamePassword(Me.Txt_UserName.Text,Me.Txt_Password.Text) If r Is Nothing Then ' UserName / Password wrong MessageBox.Show("Invalid Username or Password") Else ' Valid Username & Password MessageBox.Show("Welcome") End If End Sub
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.
- We create the datebase.
- Add a few entries
- Imported the database
- Add a query to the dataset
- Created a simple login form
- Use a Data Table Adapter to translate then process our query,
- Wrote 8 lines of code (6 excluding the comments).
- Not had to deal with ConnectionStrings
- Not had to deal with QueryString
So now a your a DataSet welding Coding Ninja
I'll do another tutorial soon covering SELECT rows, UPDATES and DELETE.