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 4 and 2 and type the answer here:
  • Post
  • How and where do you transfer the value from the text boxes or controls on your form the parameters themselves?

  • Ahan,

    Watch the video referenced at the top of the post for a step-by-step on creating a parameterized query. You create them on the TableAdapters which connect to your database and fill a DataTable -- but you can create the DataTable from any query.

  • Perry,

    The TableAdapter handles this for you when you call Me.UsersTableAdapter1.Login(Me.UserNameTextBox.Text,             Me.PasswordTextBox.Text)

  • Thank you so much for your help, its hard being a rookie sometimes.

  • Hi beth...thanks you for all ur video they make very useful to me.....but when i trying to do the login form they give me an error,

    The wizard detected the follwing problems when configuring tableadapter query "user":

    Details:

    Generated Select Statement.

    Error in from clause near 'USER'.

    Unable tp parse query test.

    To apply these settings to you query, click finish.

    Can u help...i have following all the setup up in the page...

    My db Name is USER

    My variable is Userid and Password,

    and my code is

    SELECT COUNT(*) FROM USER where userid = @userid and password = @password

    can u help me please

  • I find the error i need to Put dbo.[user]

  • I love you you have been the best guide for teaching these video tutorials

    I just love you for being there always

    thanks

    Bobby

  • Hey there Beth you are the best. I have i little code that i need to perpare for an access database, for username and password this will be my start up page. Thanks. My email address is curtis_18u@hotmail.com

  • It works very well thanks Beth, in my database I have two tables admin and user the admin works fine admin id is a nchar but the user id uses int, so if I don't put anything in the box I get an exeption error invalidcastExeption conversion from string to type integer not valid? any sugestions

    thanks in advance

  • I just want to express my gratitude to you for writing this. You save my project. Thank you.

  • Hi Beth,

    How would you give the user three wrong attempts before kicking the application out completely?  I've tried various 'if' statements but to no avail - i'm just trying to do a standard

    'if attempts<3'

    try again

    else

    quit

    endif

    thanks again - these guys are 100% right - your videos and foundational teaching really peel away the mystery of VB for those of us first starting out.

  • Hi Beth,

    I had tried on the login form and it works well the first time, when I tried again, I would not get any response when

    username and password are correct,but when they are wrong, the responds is working, what could be wrong?

    Thank you very much.

  • Beth I have found your tutorials very useful thanks!

    I have a question for you. I am trying to set up a query that searches for matching ints. In the very same way that LIKE @Title would find a identicle "title" String. What syntax do you use in the filter box to do that comparsion? I keep getting data type errors.

    Any help you could provide would be greatly helpful!

  • Hi Owen,

    The query syntax would be the same but instead you would pass an integer to the query method generated on the TableAdapter.

    HTH,

    -B

  • How do we activate Enter key press event ?

    do we write the same code for that event or create a procedure or function

Page 2 of 3 (38 items) 123