Partager via


Creating a Parameterized Query

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.

Comments

  • Anonymous
    May 26, 2007
    Thanks Beth. You're the best one.

  • Anonymous
    May 27, 2007
    Thanks from a noob.... Your videos are great!  Make a thousand more.  After programming in unix for the last several years.  I feel like I have been in a desert  and suddenly came across a well of water.

  • Anonymous
    May 27, 2007
    Hi i need your help i have this error " Error 1 'login' no es un miembro de 'WindowsApplication1.UniversidadDataSetTableAdapters.usuariosTableAdapter'. C:Documents and SettingsKäsarMis documentosVisual Studio 2005ProjectsCesar_UniversityCesar_Universitylogin.vb 5 22 Cesar_University" What is Login??????If CType(Me.UsuariosTableAdapter1.login(

  • Anonymous
    May 27, 2007
    Creating parameterized queries is one of the major countermeasures to SQL Injection attacks (not the

  • Anonymous
    May 29, 2007
    Hi Cesar, The Login method is generated on the TableAdapter when you name the function "Login" after you enter your parameterized query in the wizard. -B

  • Anonymous
    June 06, 2007
    In my first post on parameterized queries I built a simple login form that really was a contrived example

  • Anonymous
    June 06, 2007
    In my first post on parameterized queries I built a simple login form that really was a contrived example

  • Anonymous
    June 13, 2007
    I've been heads down this week (re)learning an older technology -- Visual Basic 6! With all the things

  • Anonymous
    June 17, 2007
    Beth Great Job! Help me! In your first video you creat a stored procedures. My VS 2005 don´t create in toolbox options for insert update and delete ? What is wrong? São Paulo - Brasil  whait your visit. Thank you!

  • Anonymous
    June 18, 2007
    Hi Edy, Stored procedures are not created automatically for you. I wrote the stored procedures for the video and put them as snippets in the toolbox. You can create code snippets by selecting text in the code editor and then dragging that onto the toolbox. Then you can use that snippet of code again by dragging from the toolbox into the editor. Cheers, -B

  • Anonymous
    June 27, 2007
    The comment has been removed

  • Anonymous
    June 27, 2007
    The comment has been removed

  • Anonymous
    June 27, 2007
    A small variation on your project, I would like the user to enter values into two text boxes and use them as range parameters in the dataset and return those records between the two values.  How would I do that?

  • Anonymous
    June 27, 2007
    Hi Perry, You can add as many parameters as you like to the query. Just like I showed above, when you add the @Parameter1 to the SQL query then it will pick it up as a parameter. If you are using Access (the OleDb client) then the parameter placeholder is a question mark (?). All you need to do is write the query that selects the range of values you want. For information on writing T-SQL queries check out SQL Server Books Online: http://msdn2.microsoft.com/en-us/library/ms130214.aspx Cheers, -B

  • Anonymous
    June 27, 2007
    The comment has been removed

  • Anonymous
    June 28, 2007
    How and where do you transfer the value from the text boxes or controls on your form the parameters themselves?

  • Anonymous
    June 28, 2007
    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.

  • Anonymous
    June 28, 2007
    Perry, The TableAdapter handles this for you when you call Me.UsersTableAdapter1.Login(Me.UserNameTextBox.Text,             Me.PasswordTextBox.Text)

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

  • Anonymous
    July 23, 2007
    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

  • Anonymous
    July 23, 2007
    I find the error i need to Put dbo.[user]

  • Anonymous
    July 29, 2007
    I love you you have been the best guide for teaching these video tutorials I just love you for being there always thanks Bobby

  • Anonymous
    November 01, 2007
    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

  • Anonymous
    February 12, 2008
    The comment has been removed

  • Anonymous
    March 05, 2008
    I just want to express my gratitude to you for writing this. You save my project. Thank you.

  • Anonymous
    May 22, 2008
    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.

  • Anonymous
    June 03, 2008
    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.

  • Anonymous
    July 10, 2008
    The comment has been removed

  • Anonymous
    July 21, 2008
    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

  • Anonymous
    July 22, 2008
    How do we activate Enter key press event ? do we write the same code for that event or create a procedure or function

  • Anonymous
    July 23, 2008
    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

  • Anonymous
    August 08, 2008
    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.

  • Anonymous
    August 13, 2008
    The comment has been removed

  • Anonymous
    September 15, 2008
    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

  • Anonymous
    February 25, 2009
    thank you beth! this simple query may help me a lot to finish my documentation for thesis

  • Anonymous
    January 22, 2010
    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

  • Anonymous
    April 23, 2010
    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

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

  • Anonymous
    March 20, 2015
    c# conversion private void btnLOGIN_Click(object sender, EventArgs e)        {            try            {                Int64 integer = (Int64)this.employeesTableAdapter1.Login(this.tboxUSERNAME.Text,this.tboxPASSWORD.Text);                MessageBox.Show(integer.ToString());                //If CType(this.employeesTableAdapter1.Login(this.tboxUSERNAME.Text,this.tboxPASSWORD.Text), Integer ) > 0 Then                if (integer > 0)                {                    MessageBox.Show("Welcome to my application!");                }                else                {                    MessageBox.Show("Invalid Username or Password");                }            }            catch (Exception ex)            {                MessageBox.Show(ex.ToString());            }        }