• (2 Pages)
  • +
  • 1
  • 2

[WalkThrough] UserNames & Password Database Picture Heavy. DataTableAdapter + Query Rate Topic: ****- 2 Votes

#1 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2216
  • View blog
  • Posts: 9,352
  • Joined: 29-May 08

Post icon  Posted 21 August 2009 - 10:30 AM

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.
Attached Image
Save the table.

Lets add a few users

0 Administrator Administrator
1 User User
2 User2 abc

Attached Image
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.
Attached Image
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.
Attached Image

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.)
Attached Image
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
Attached Image

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

Attached Image
If you know the SQL needed to accomplish the task enter it here.
Click Query Builder, open up the query builder window.
Attached Image
Modify the query so it looks like
Attached Image

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
Attached Image

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
Attached Image

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
Attached Image
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.
Attached Image
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.
Attached Image
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.

5. Review
  • 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
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 :ph34r:

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

Is This A Good Question/Topic? 3
  • +

Replies To: [WalkThrough] UserNames & Password Database

#2 jatobtech  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 28-August 09

Posted 31 August 2009 - 02:59 AM

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 :rolleyes:
Was This Post Helpful? 0
  • +
  • -

#3 kriaz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 16-October 09

Posted 16 October 2009 - 10:29 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2216
  • View blog
  • Posts: 9,352
  • Joined: 29-May 08

Posted 17 October 2009 - 12:23 AM

Thank You for spotting that.
Edited Tutorial to include correction
Was This Post Helpful? 0
  • +
  • -

#5 TRSwaterloo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 23-October 09

Posted 23 October 2009 - 06:19 PM

:ph34r: skills on this tutorial, it was like wow I actually learned something and I didn't have to think.
Was This Post Helpful? 0
  • +
  • -

#6 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 35
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Posted 07 December 2009 - 11:34 AM

Hi Adam

Great tutorial and you are right everybody seems mad on doing it the hard way instead of using the functionality of .net
Look forward to the next one

Best regards

Sharpy
Was This Post Helpful? 0
  • +
  • -

#7 IrishCoder  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 37
  • Joined: 07-April 10

Posted 12 September 2010 - 04:36 AM

Hello Adam

Fantastic tutorial! It really is. I have a quick question. How would i build a query that finds the last value of UserID and returns it so that i can add a new username and password to the database in the correct place via a textbox and a add button?

Many thanks

This post has been edited by IrishCoder: 12 September 2010 - 04:55 AM

Was This Post Helpful? 1
  • +
  • -

#8 Geck0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 11

Posted 30 January 2011 - 04:58 PM

hay,this tutoral is perfect forwhat im working on.. its been a very long time since i have programed anything. to make things worse last time i ws programming was in vb6 there is one question i have though, if u have the time to answer.

: same question another person asked is there a way to create additonal users with this? as the system im using will need an "Admin" feture with a way too view all usernames and passwords and create new ones or delete old ones... the unfortunat thing about this is iv not had a chance to play around with it myself yet as i dont have microsoft access yet "disk is in in-laws", and im NO expert in programming, so what im asking in full is! is this all possable to do, useing this tutoral as the base line, or do i need alot more added into the program for that? thanx
Was This Post Helpful? 0
  • +
  • -

#9 Geck0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 11

Posted 30 January 2011 - 05:07 PM

View PostGeck0, on 30 January 2011 - 04:58 PM, said:

hay,this tutoral is perfect forwhat im working on.. its been a very long time since i have programed anything. to make things worse last time i ws programming was in vb6 there is one question i have though, if u have the time to answer.

: same question another person asked is there a way to create additonal users with this? as the system im using will need an "Admin" feture with a way too view all usernames and passwords and create new ones or delete old ones... the unfortunat thing about this is iv not had a chance to play around with it myself yet as i dont have microsoft access yet "disk is in in-laws", and im NO expert in programming, so what im asking in full is! is this all possable to do, useing this tutoral as the base line, or do i need alot more added into the program for that? thanx




just to explain a little clearer to maby help.. im (Trying, with my utter nubishness) to designing a pos "cash register system", so if the "Password" thing works then i will use the same method to as the barcode,items,price,waight,+vat. structure to it too so it needs to beable to be added and deleted to, withsome kind of Listview, for this reason too... not to make it complecated or anything :-P


thanx again for ur time :-)

This post has been edited by Geck0: 30 January 2011 - 05:09 PM

Was This Post Helpful? 0
  • +
  • -

#10 philip_john  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 04-February 11

Posted 07 February 2011 - 03:53 AM

The tutorial that you did is not advisable because you will encounter a problem with this code when you create a sign up form. When you sign up, the username and password that you create will not read by the login because the current usernames and passwords will be the one to read by the system. Instead, use sql command like SELECT and INSERT.
Was This Post Helpful? 0
  • +
  • -

#11 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2216
  • View blog
  • Posts: 9,352
  • Joined: 29-May 08

Posted 07 February 2011 - 04:01 AM

You haven't use DataSet Queries before, you can add users and their passwords, just as easily with the DataSet Queries they can insert, update, create and delete.
See image
Attached Image
The designer assists you by creating the query and the code for you.

Do some research.

This post has been edited by AdamSpeight2008: 07 February 2011 - 04:04 AM

Was This Post Helpful? 1
  • +
  • -

#12 Geck0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 30-January 11

Posted 07 February 2011 - 07:05 AM

Thanx guys really appreciate the help.. im still learning this vb.net mlarky so im just trying to get used to the language :-)
Was This Post Helpful? 0
  • +
  • -

#13 r0tanddecay  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 02-March 11

Posted 24 March 2011 - 10:19 AM

How does one go about creating a button to add a new user to the database?
Was This Post Helpful? 0
  • +
  • -

#14 alfkonee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 16-June 11

Posted 16 June 2011 - 07:01 PM

Thanks for a great tutorial. It was really helpful but I noticed on thing.
That's the passwords become case insensitive.

I mean the Login is successful even when the case of the password is not the same as that in the database. Could you Help me correct that.
Was This Post Helpful? 0
  • +
  • -

#15 MslMnMike  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 28-January 10

Posted 14 July 2011 - 06:42 AM

First off this was exactly what I needed but would request more simple tutorials like this such as one to show how to insert after building a query to insert into a table and so on. Which leads me to my question, I built a query using this same process but now need help with how I call the "nonquery" to execute when I click a button I need it to get the data and insert into the database.

INSERT INTO inventorytbl
                         (inventoryType, inventoryMake, inventoryModel, inventorySerial, inventoryYear, inventoryPrice)
VALUES        (?, ?, ?, ?, ?, ?)



    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim addInventory = Me.InventorytblTableAdapter.InsertInventory(Me.txtType.Text, Me.txtMake.Text, Me.txtModel.Text, Me.txtVin.Text, Me.txtYear.Text, Me.txtCost.Text)

        'need to execute the nonquery to get the data from text boxes and insert to table

    End Sub


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2