locked
SQL query using a Variable with WHERE clause

    Question

  • I am creating an application to help me with my Income Tax Return. I have a form with 3 textboxes that are bound to three columns in a table. The first textbox requires my input of the tax year and is linked to the year column of the table. Since that information will be a variable what is the syntax in my query to provide the Allowance information from the Allowance column in the  Table for textbox 2 and the Income Limit information from the Limit column in the Table for textbox 3. I have tried the following:

    SELECT Year,Allowance,Limit FROM dbo.TaxYear WHERE Year=2010.

    This syntax works perectly, but my efforts with substituting 2010 with a variable have been disastrous.  I am a beginner and I would be very grateful for some help with this problem.  I have tried various suggestions on other forums but I have not fully understood the procedures such as declaration of variables etc. Please help.
    Thursday, February 25, 2010 10:18 AM

Answers

  • dim Youryear as integer = 2010
    "SELECT Year,Allowance,Limit FROM dbo.TaxYear WHERE Year '" + youryear.tostring + "'"

    or

    "SELECT Year,Allowance,Limit FROM dbo.TaxYear WHERE Year = @year"
    yourcommand.parameters.addwithvalue("@year", youryear)


     |
     |
    \ /         :-)
     
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    • Marked as answer by Jeff Shan Thursday, March 04, 2010 2:15 AM
    Thursday, February 25, 2010 2:00 PM
  • Presuming you have used the designer wizards to create your form and textboxes there is no need to query the database.  You could simply apply a filter to your bindingsource, e.g:

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            TaxYearBindingSource.Filter = "Year =" & YearTextBox.Text

        End Sub

     

    • Marked as answer by Jeff Shan Thursday, March 04, 2010 2:15 AM
    Thursday, February 25, 2010 11:27 AM

All replies

  • Let's suppose we use the query written by you (with 2010). How do you execute it?
    Then we can reply in more useful way.
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Thursday, February 25, 2010 11:04 AM
  • Presuming you have used the designer wizards to create your form and textboxes there is no need to query the database.  You could simply apply a filter to your bindingsource, e.g:

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            TaxYearBindingSource.Filter = "Year =" & YearTextBox.Text

        End Sub

     

    • Marked as answer by Jeff Shan Thursday, March 04, 2010 2:15 AM
    Thursday, February 25, 2010 11:27 AM
  • dim Youryear as integer = 2010
    "SELECT Year,Allowance,Limit FROM dbo.TaxYear WHERE Year '" + youryear.tostring + "'"

    or

    "SELECT Year,Allowance,Limit FROM dbo.TaxYear WHERE Year = @year"
    yourcommand.parameters.addwithvalue("@year", youryear)


     |
     |
    \ /         :-)
     
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    • Marked as answer by Jeff Shan Thursday, March 04, 2010 2:15 AM
    Thursday, February 25, 2010 2:00 PM
  • We are going to need to know what type of database you are working with. For example, if you're using Microsoft Access then I see one problem already, and that is that Year is reserved word. You would either need to rename the column or enclose it within brackets.

    Second, we don't know what the Year data type is. Is it Date/Time? Text?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, February 25, 2010 3:33 PM
  • Hello,

    Just remember that year is a string in your query, not a date, and it should make more sense.

    Adam
    Dibble and dabble but please don't babble.
    Thursday, February 25, 2010 3:46 PM
  • Many thanks for  your help and your suggestion has solved my problems.
    Friday, February 26, 2010 5:15 PM
  • Many thanks Dave. You have provided me with an alternative method to solve the problem
    Friday, February 26, 2010 5:16 PM
  • Thanks for replying but I had not reached that point in your enquiry
    Friday, February 26, 2010 5:18 PM
  • I had createdm the table in VB express 2008. Many thanks for your reply
    Friday, February 26, 2010 5:20 PM
  • Thanks Adam I used the expression Integer.Parse to convert it to an integer. I found that expression after submitting my problem and you are quite right in that there was a confusion in my mind
    Friday, February 26, 2010 5:23 PM
  • I was going to use a calculate button to do all the calculations on the form but when adding a query, a button is added for that particular query. Although I have been provide with answers from other forum members I have to rethink the query method. Many thanks for your support.
    Friday, February 26, 2010 5:28 PM
  • You only need to use a query when you need to get new data from the database.  If the data is already loaded into a dataset then you should operate on the dataset\tables rather than repeatedly querying the database.
    Friday, February 26, 2010 5:38 PM
  • I like the second (parameter-based) approach as it prevents SQL injection attacks.

    William (Bill) Vaughn -- Mentor, Author, Dad and MVP Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva
    Friday, February 26, 2010 7:02 PM
  • I like the second (parameter-based) approach as it prevents SQL injection attacks.

    William (Bill) Vaughn -- Mentor, Author, Dad and MVP Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva
    Totally agree! Always use parameterized queries to avoid nasty sql injection attacks!
    Saturday, February 27, 2010 1:25 AM