How to set default value of paramatized query to extract all records from a table?

Posted on 2011-10-09
Last Modified: 2012-05-12

I'm working on with vb and sql database. I've created a webpage to perform a paramatized query. The project_number field serves as the sole parameter and the user inputs a value into a text box to generate query results.  The results are displayed in a gridview control.

At present, there are three possible project numbers: 10000, 11000, and 12000. The webpage works fine when I enter any of the 3 project numbers. However, I want the webpage to show all project records for all active projects if they don't fill in the textbox that is linked to the paramater. I've tried a default value of: *, LIKE 1%, LIKE '1'%, and LIKE '1%' but to no avail.

Do I have to write VB code behind the webpage to make it work?

Question by:WhyDidntItWork
    1 Comment
    LVL 11

    Accepted Solution

    More than likely. It sounds like you want to have a default value of SQL that gets injected into the query? The SQL query won't like any of the strings you are trying to use as a default because if probably looks like this:
    SELECT * FROM tb_MyTable WHERE project_number = {the_url_value}

    Open in new window

    the '=' here is probably messing you around. If you can change the SQL and you can use text like project_number (more specifically the name of the column where the constraint is being applied) so that the SQL ends like like this:
    SELECT * FROM tb_MyTable WHERE project_number = project_number

    Open in new window

    The direct column name match will return all the records.

    This kind of SQL is prone to injection attacks so just be warned. If you can change your query, make it something that will fallback onto the column and pass in NULL when there is no value from the textbox, using a parameter will also protect you fomr users modifying the SQL:
    SELECT * FROM tb_MyTable WHERE project_number = ISNULL(@projectNumber, project_number)

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now