Sunday, March 11, 2012

Conditional query on ASPNET page

Dear Friends,

I am working on search customer information page.

I have 5 search options,

Name,

Email,

Order Number,

Product Name,

Order Date

I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don’t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

So here are a couple suggestions that might help you with your task:

1. Look into calling system stored procedure sp_columns (http://msdn2.microsoft.com/en-us/library/ms176077.aspx) on the table to get the column metadata information.

Using this information you can then dynamically generate a query based on the check boxes selected by the user.

2, If you schema is simple and you already know the column information, then all you need to do is have a string that concatenates differect WHERE clauses based on the options selected and then submit the query when the user hits the search button.

HTH,

No comments:

Post a Comment