Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

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?

Question by:WhyDidntItWork
1 Comment
LVL 11

Accepted Solution

brutaldev earned 2000 total points
ID: 36939262
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


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

580 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