Solved - writing  a generic query page

Posted on 2009-04-22
Last Modified: 2013-11-26
Experts whats the best way to deal with this problem. Do I write a query form that builds up a select statement or is there some webbased query tool that I can just hand over to the client? Let me explain;

I have an webpage with about 30 textboxes.

The client wants another screen that allows them to run a query on any combination of these fields.

(In reality I expect that they wont ever query for more than a combination of 5 of the fields though)

So for example they want something that would allow them to choose zipcode,startdate,enddate,value and address1 and filter like this -

ZIPCODE => 94122 AND ZIPCODE < 94126
STARTDATE > '01/04/2009'
ENDDATE < '22/04/2009'
VALUE < 10000

If you think I should build up the select statement then an example would be great if you could find one.

Im using Visual Studio 2008 (VB Code), sql server express 2008,
Question by:Real_coffee
    LVL 4

    Expert Comment

    I suugest you to use subsonic as ORM and create your sql query using subsonic query builder. Like

    //Sorry it a C# code example...
    Query q = YourTableOrViewName.Query();
    if (string.IsNullOrEmpty(txtStartDate.Text))
    if (string.IsNullOrEmpty(txtZipCode.Text))
    // and so on..
    YourGridView.DataSource = q.ExecuteDataSet();

    Open in new window


    Author Comment

    right thanks. I get the code. Id use a big case statement and build up a sql select string i guess but whats the benefit of subsonic? I havent heard of that before - what does it do that visual studio doesnt?
    LVL 2

    Expert Comment


    i'd create a stored proc with default values in the parameters. Then test in the code whether the text boxes have been used and if so add the value. The default values should be set so they would still return all data. for example

    cretae proc testproc
    @StartDate datetime = '01/01/1900',
    @EndDate datetime = '12/12/2199'
    @Value int = 1000000

    select * from tablename where yourdate between @startdate and @enddate and value <@Value

    then in the code do the

    dim objsql as new sqlcommand("testproc",connection)
    objsql.commandtype = data.commandtype.storedprocedure
    if startdate.text.length > 0 then objsql.parameters.add("@StartDate",sqldbtype.datetime).value = startdate.text

    then tie in that into your datagrid or whatever output you wish.

    Author Comment

    but with 30 textboxes and they need the ability to use AND OR. This looks a bit of a problem to me.

    perhaps Im missing something. Id hoped that maybe there was something like a crytsal reports add-in that I could simply point at the table? It can stand outside the app if neccesary.
    LVL 4

    Expert Comment

    What is the use of subsonic? Subsonic generates vb or c# codes of your database schema and you will be able to build sql queries simply without using "Select * from bla bla... " ..
    And you will be able to create complex queries using subsonic.. You can watch tutorials about subsonic at  It s also open-source

    Author Comment

    Im thinking of something like this -

    What do you think? If you havent tried this product is there anything else like this that you have experienced?
    LVL 2

    Accepted Solution

    So what is the problem just to use this product (EasyQuery.NET) on your web-site?
    We use it in one of our projects and our customers love its UI.
    The guys from propose free version which is fully functional but has a link to their web-site. If you would like to remove that link - you will need to pay about $200 for the license.

    Author Comment

    I've purchased the Korzh product and its working great.


    Author Closing Comment

    An elegant product. Im happy to recommend this if you want to provide query abilities to your web application.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    21 Experts available now in Live!

    Get 1:1 Help Now