CODEWITHSUNDEEP
asp.netado.netsqldatasource
siavash
siavash

Reputation: 17

sqlDataSource select not work with where clause

I bind gridview to sqldatasource.my problem is that when i use sqldatasource without where clause in select statement it works fine,but when I use it with where clause its work fine in Query Builder test and return records but not working in runtime. I used Sql Profiler and see query not run when i use where clause.I heard .NET prevent run query with where clause because of sql injection but I dont know how to correct my query. My sqldatasource:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:chargeDBConnectionString %>"  SelectCommand="SELECT CARDNUMBER, VISITDATE, ACCNUMBER, ACTIONCODE FROM LOGTABLE WHERE (CARDNUMBER = @cardno OR @cardno IS NULL AND CARDNUMBER &lt;&gt; N'-' AND @ttype = 1 OR @ttype = 0) AND (VISITDATE &gt;= @fdate AND VISITDATE &lt;= @edate) AND (ACCNUMBER = @accno OR @accno IS NULL AND ACCNUMBER &lt;&gt; N'-' AND @ttype = 0 OR @ttype = 1) AND (ACTIONCODE = @actioncode OR @actioncode IS NULL)">
        <SelectParameters>
            <asp:FormParameter FormField="cardNo" Name="cardno" />
            <asp:ControlParameter ControlID="ddlType" Name="ttype" 
                PropertyName="SelectedValue" />
            <asp:FormParameter FormField="fromDate" Name="fdate" />
            <asp:FormParameter FormField="toDate" Name="edate" />
            <asp:FormParameter FormField="accNo" Name="accno" />
            <asp:ControlParameter ControlID="ddltransname" Name="actioncode" 
                PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:SqlDataSource>

Upvotes: 1

Views: 1732

Answers (1)

Josh Darnell
Josh Darnell

Reputation: 11433

The most likely culprit is that one of your parameters is evaluating to null, and the SqlDataSource is cancelling the select query.

To correct that, you need to set the SqlDataSource.CancelSelectOnNullParameter property to false (it is true by default) in your SqlDataSource declaration:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:chargeDBConnectionString %>"  
    SelectCommand="SELECT CARDNUMBER, VISITDATE, ACCNUMBER, ACTIONCODE FROM LOGTABLE WHERE (CARDNUMBER = @cardno OR @cardno IS NULL AND CARDNUMBER &lt;&gt; N'-' AND @ttype = 1 OR @ttype = 0) AND (VISITDATE &gt;= @fdate AND VISITDATE &lt;= @edate) AND (ACCNUMBER = @accno OR @accno IS NULL AND ACCNUMBER &lt;&gt; N'-' AND @ttype = 0 OR @ttype = 1) AND (ACTIONCODE = @actioncode OR @actioncode IS NULL)"
    CancelSelectOnNullParameter="False">
    <SelectParameters>
        <asp:FormParameter FormField="cardNo" Name="cardno" />
        <asp:ControlParameter ControlID="ddlType" Name="ttype" 
            PropertyName="SelectedValue" />
        <asp:FormParameter FormField="fromDate" Name="fdate" />
        <asp:FormParameter FormField="toDate" Name="edate" />
        <asp:FormParameter FormField="accNo" Name="accno" />
        <asp:ControlParameter ControlID="ddltransname" Name="actioncode" 
            PropertyName="SelectedValue" />
    </SelectParameters>
</asp:SqlDataSource>

Upvotes: 3

Related Questions