• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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


I'm working on asp.net 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?

1 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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now