Creating a Parameterized Query

Creating a Parameterized Query

Rate This
  • Comments 38

I've been asked by a couple readers on how to pass parameters into a SQL statement to your database using the TableAdapters. I actually created a video on how to do this in the context of a search query (play video | download videoentire series). It's really easy to do this using the TableAdapter Query Configuration Wizard. Let's create a quick login form for an example.

I've got a table called Users in my database and I've created a Dataset called UsersDataset by opening up the Datasources window and adding a new datasource to my database. Then I created a form called LoginForm and from the Toolbox I dragged some labels and textboxes for the entry and a couple buttons, OK and Cancel, onto the form. I then set the PasswordChar property on the PasswordTextbox to "*". This indicates that the textbox should display this character instead of what the user types, but the value of the Text property will still be what the user enters.

(By the way, this example does NOT demonstrate a secure way of writing login forms. We'll be passing what the user enters directly into the database which stores the password in clear text. It is NOT safe practice to store clear text passwords in your database. I'll post a follow-up that talks about techniques we can use to protect users' passwords, especially if we need to store them in a database. For now, let's concentrate on how we add parameterized queries to our TableAdapters. UPDATE: Here's the follow up.)

Now at this point I build the project and in the WindowsApplication1 Components section at the top of the Toolbox I can now see the UsersDataset and the UsersTableAdapter listed (it's important that you build your project to get the components listed in the Toolbox). I drag the UsersTableAdapter onto the LoginForm which creates a component in the tray named UsersTableAdapter1.

So now that we've got that all set up, we're going to add a parameterized query to the UsersTableAdapter. First open up the UsersDataset and right-click on the TableAdapter in the designer and choose "Add Query".

This will open up the TableAdapter Query Configuration Wizard. First it asks you to choose a command type. Keep the default selected as "Use SQL Statement" and then click Next. Next it asks you to choose a query type. We're going to select the second option, to create a SELECT statement that returns a single value, not a result set.

Next we'll write the parameterized query as "SELECT COUNT(*) FROM Users WHERE UserName = @UserName AND Password = @Password". Then click Next and name the function "Login". This will create a TableAdapter method called Login that accepts a UserName and Password as a parameter and returns an Object which will be an Integer in our case since we're specifying to return the COUNT(*) of the rows that match the WHERE clause.

Now back to the LoginForm, we can double-click on the OK button to add a Click event handler.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles Button1.Click

   

    Try

        If CType(Me.UsersTableAdapter1.Login(Me.UserNameTextBox.Text, _

                 Me.PasswordTextBox.Text), Integer) > 0 Then

 

            MsgBox("Welcome to my application!")

        Else

            MsgBox("Invalid username or password.")

        End If

 

    Catch ex As Exception

        MsgBox(ex.ToString)

    End Try

End Sub

Now when we run this example and enter a user name and password that is contained in the database table Users, we get the greeting message otherwise we get the message that our user name or password is invalid.

As you can see it's really easy to add parameterized queries to your TableAdapters using the wizards. There's even a simple query builder you can use to help you write your queries and test them out before they are generated on the TableAdapter. In a follow up post I'll show you how to add secure password storage to your application using hashing and encryption.

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • hi, the steps are just very clear, i followed it and was able to create my login form. thanks for the good work massi.

    could you please tell me how create a form that will change my password that is in the database now.

    plaese, will be very greatfull if you reply to my url

  • Hi Beth,

    I'm a big fan.... I could use your help in converting the login app to an Web-app. Under vb 2005, I could not figure how to create a usertableadaptor.

  • How do I execute a parameterized query using Access instead of SQL Server

    Your videos are great.

    Thx.

  • Hi Beth,

    Thank for form over data series,... i learn a lot

    please help,..

    i create from with parent and child relation, it work fine to save in their table.

    my problem is, i need to save the result to another new table with new ID,

    is there any series to learn about it

    thank you

  • thank you beth! this simple query may help me a lot to finish my documentation for thesis

  • Hi Beth, I have become a fan of yours after going through the amazing how do I videos and this easy approach to creating a login form. Thanx a lot

  • Thank u alot ... I did it :) and I will look in the next lesson "Building a Secure Login Form (Parameterized Queries Part 2)"

    I hope its fun and easy like this one

    Thank u Beth

  • Hi Beth,thanks.wish to have you as my pal.it's that possible.

Page 3 of 3 (38 items) 123