.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Eminent IT
Post New Web Links

optional select criteria for an SqlDataSource

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :ASP.Net

I have a form that searches for images in a database.  I have several DropDownLists with various search criteria e.g. Genus, Photographer, State, Project.

I'd like the user to be able to filter using any combination of these.  For example they might pick a specific photographer and a specific genus but choose not filter by project or state.

I'd like to find a good, clean, elegent way to do this.

Any advice??

Here is an example of the code I have now, it has only two possible criteria.  It works but I have a feeling there are better ways to do this....

<script runat="server">

    Sub Page_Load()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim thisGenus As String = ddlGenus.SelectedValue
        Dim thisDeterminer As String = ddlDeterminer.SelectedValue
        Dim whereClause As String
        whereClause = "1=1"
        If (thisGenus <> "") Then whereClause = whereClause + " AND genus like '" + thisGenus + "'"
        If (thisDeterminer <> "") Then whereClause = whereClause + " AND determiner like '" + thisDeterminer + "'"
        dsImageList.SelectCommand = "SELECT 

View Complete Post

More Related Resource Links

ASP.NET Search Engine with MS Access for optional search criteria




I am creating a simple search engine similiar to:  


I have a text box for the last name and a dropdown list for the club name. If one value is entered, it will search for just one value, and ignore the blank value in the other. If there are 2 values it will search for 2 values, and no values should pull no results.

Using VS 2005 and Access 2003-2005.  My statement works fine in access but in the accessdatasource there are issues with the @ symbol in VS 2005, the ? symbol works, but generates multiple parameters, when I only need 2.

This statement works fine in the access query: (I do believe the statement is correct), but then again im using a drop down list, does that change the rules? Such as dropdownlist.selecteditem.text = ???

Use of CASE statement in Select using SqlDataSource


Hi All,

I need help regarding the use of CASE in Select of SQLDataSource. Actually I am using FORMVIEW and in the Grid i have "Consumer Numbers" as link and on click I save the Consumer Number in Session Variable. When user go into Edit and change the Consumer Number after updation the formview was coming blank cause i was using that number for select statement now. Just need to correct the SELECT and use the case properly and want to do is




How to do that please correct.

       <asp:SqlDataSource ID="SqlDSBillers" runat="server" 
ConnectionString="<%$ ConnectionStrings:SQLE2K8 %>"

SelectCommand="SELECT Bill_Master.Bill_Master_ID,
CONVERT (varchar(20),
Bill_Master.Enroll_Date, 105) AS Enroll_Date,

How to select cuurent month in sqldatasource?


Hi all,

I have a sqldatasource statement. How can i select current month in my statement?

Here is what i done.

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
                SelectCommand="SELECT SUM(Amount) AS Total2 FROM Budget WHERE (UserId = @UserId) AND (CategoryId = 1) AND (CreateDate = @CreateDate)">
                    <asp:SessionParameter Name="UserId" SessionField="UserId" DefaultValue="" />
                    <asp:Parameter Name="CreateDate" Type="DateTime" />


in page load C#,


if (!this.IsPostBack)
                SqlDataSource3.SelectParameters["CreateDate"].DefaultValue = tnow.Month.ToString();


But i get this error <String was not recognized as a valid DateTime>.

How can i do it?

Any help is appreciated.

Thank you.



AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Select value in gridview


Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />

Binding GridView with SqlDataSource at runtime?


Hi, I want to create a generic page which contains a gridview and sqldatasource. I want to put these two controls on .aspx (C#) page but bind them at runtime. It should have selectcommand, updatecommand, deletecommand and insertcommand. Stored procedures are created for that. Help is needed to make it. Please post some link or code. Regards, ap.

SqlDataSource how to change the stored procedures at run time



Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?



SqlDataSource UpdateCommand using 2 tables


I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.


I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".


I tried taking out the set for CustomerID and I still get the error on page.


Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.


Do I need to do something with Control Parameters?

Digital Signatures: Application Guidelines on Digital Signature Practices for Common Criteria Securi


This article is an overview of digital signature practices and user considerations necessary to write applications in compliance with ISO/IEC 15408 Common Criteria security. Signing categories are described, including those that are a potential risk to the implied trust association that goes with a digital signature.

Jack Davis

MSDN Magazine November 2009

The XML Files: XSLT Keys, Select vs. Match, Conflict Resolution, and More


This month Aaron Skonnard covers how keys work in XSLT, XSLT match and select attributes, SelectNodes order, finding