Hello Everyone,
     I have a small website with 4 buttons. Each button will take you to a page that retrieves database information and places the information in a table.

     What I would like to do is add a search feature on that page so people can drill further down to get a more refined result. Is it possible for me to place a search field on that page and have the page refresh with the more drilled down results?

Thank you for your time in Advance.

Matt

It certainly is. You'll need to determine how you want people to be able to search the data and provide the search logic for it. Then you just limit the database results by incorporating the user's search value into the query.

-Peter
peterlanoie.blog

Ok, but how do I go about doing this?

When the user first hits the page, the following SQL statement automatically runs:

"SELECT * FROM dbo.Bids WHERE Active='1' AND DATEDIFF(day,OpeningDate,GetDate()) < 1 ORDER BY BidCategory, BidName, ExpirationDate" and the results are shown.

Now, I have the search feature on the page which will allow them to search by Bid Name. When they type in a bid name, I'd like to refresh the page and append the SQL statement to say:

"SELECT * FROM dbo.Bids WHERE Active='1' AND DATEDIFF(day,OpeningDate,GetDate()) < 1 AND BidName LIKE 'Value' ORDER BY BidCategory, BidName, ExpirationDate"

That way the original statement stays intact and it appends the new search criteria.

Of course, if the user just hits the submit button without inputing any data, then it will refresh without the added criteria.

Is this done with a function and hidden form values or is it something else?

Thank you for your time.

Matt

When the page posts back, the search value will be in the text box. Construct the new query as you describe and get the value from <textboxid>.Text. You'll probably want to wrap the search string in % so it will find the search string within the column value instead of expecting it to be exact.

You'll probably want to build this query using a SQL command object so you don't have to worry about the SQL injection risk. You can add a parameter into your query, then add the search value to the command's parameters collection. Something like this:

sqlCommand.CommandText = "SELECT * FROM dbo.Bids WHERE Active='1' AND DATEDIFF(day,OpeningDate,GetDate()) < 1 AND BidName LIKE @BidSearch ORDER BY BidCategory, BidName, ExpirationDate"
sqlCommand.Parameters.Add(New SqlParameter("@BidSearch", String.Format("%{0}%", <textboxid>.Text)))


-Peter
peterlanoie.blog