SqlParameter

Hi!

Why isn't it possible to use <asp:QueryStringParameter Name="ClientID" Type="String" asp:QueryStringField="clientid" /> or <asp:SessionParameter Name="ClientID" Type="Object" asp:SessionField="clientid" /> as SQL Parameter Filter when using GetPaged as SelectMethod? It is however possible to use these types of Parameters when using GetByClientId as selectMethod.

When I try the above Parameters with GetPaged, I get a Null Reference in SqlParameter.cs at line 125:

                    returnValue = ( filters[0] as ISqlFilter ).GetSqlFilterString(control, filters, isCallback);

( filters[0] as ISqlFilter ) returns null although filters[0] is not null. It looks as if there is not much missing to get this working, but this is beyond my knowledge of C#. It would be useful if it could work though.

Cheers!

JF

Can you post a code example of how you are trying to use the Parameters to make sure I understand the request?

Thanks.

Hi Bobby!

Here is my ASPX code for the DataSource:

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="GetPaged"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
                <Filters>
                    <asp:SessionParameter Name="ClientNom" SessionField="ClientNom" Type="Object" />
                    <asp:SessionParameter Name="ClientTypeID" SessionField="ClientType" Type="Object" />
                    <asp:SessionParameter Name="RegionID" SessionField="Region" Type="Object" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

I also tried with Type="String" but it did not help.

 Cheers!

JF

Hi Dudleya!

No, I did not receive more feedback, so I bypassed the problem with properties in the page class (code-behind) and the following DataSource:

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="GetPaged"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
                <Filters>
                    <data:RechercheAvanceeFilter Column="ClientNom" ControlID="__Page" PropertyName="clientNom" ComparisionType="Contains" />
                    <data:RechercheAvanceeFilter Column="ClientTypeID" ControlID="__Page" PropertyName="clientType" ComparisionType="Equals" />
                    <data:RechercheAvanceeFilter Column="RegionID" ControlID="__Page" PropertyName="region" ComparisionType="Equals" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

It is too bad though that nobody answered that one, just for the sake of understanding how all of this is working.

Cheers!

JF

Velum,

The reason why it doesn't work the way you intended, is because SqlParameter is working with SqlFilterBuilder/ParameterizedSqlFilterBuilder classes  that use entity column enumerations. I have made a slight enhancement (see the SqlParameter.cs attached, change the namespace to match your project namespace) to be able to specify either SessionField or QueryStringField. Would be great if you check to see that it works as expected, and may be we could get the feedback from others before checking the change in to svn.

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="GetPaged"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
                <Filters>
                    <data:RechercheAvanceeFilter Column="ClientNom" SessionField="clientNom" ComparisionType="Contains" />
                    <data:RechercheAvanceeFilter Column="ClientTypeID" SessionField="clientTypeID" ComparisionType="Equals" />
                    <data:RechercheAvanceeFilter Column="RegionID" SessionField="regionID" ComparisionType="Equals" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

Hi Mike!

I'm still getting an error in sqlParameter.cs at line 125:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Line 123:				else
Line 124:				{
Line 125:					returnValue = ( filters[0] as ISqlFilter ).GetSqlFilterString(control, filters, isCallback);
Line 126:				}
Line 127:			}

filters[0]                            {ClientNom}    object {System.Web.UI.WebControls.SessionParameter}
( filters[0] as ISqlFilter )     null                 Mammouth.Web.Data.ISqlFilter

You can see that the cast of filter[0] into ans ISqlFilter gives a null.

Cheers!

JF

Hey Velum,

It looks like you still seem to be passing SessionParameter? 

I did a watch a these two values:
filters[0]                            {ClientNom}    object {System.Web.UI.WebControls.SessionParameter}

FYI: You could use parameterized query using Find method to achive the same result 

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="Find"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="Parameters" UseParameterizedFilters="true">
                <Filters>
                    <data:RechercheAvanceeFilter Column="ClientNom" SessionField="clientNom" ComparisionType="Contains" />
                    <data:RechercheAvanceeFilter Column="ClientTypeID" SessionField="clientTypeID" ComparisionType="Equals" />
                    <data:RechercheAvanceeFilter Column="RegionID" SessionField="regionID" ComparisionType="Equals" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

Hi Mike!

Ok! Thanks for the reply! I misunderstood what you had done. I re-tried your code, now that I understand, and it is working beautifully with GetPaged:

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="GetPaged"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
                <Filters>
                    <data:RechercheAvanceeFilter Column="ClientNom" SessionField="RechercheClientNom" ComparisionType="Contains" />
                    <data:RechercheAvanceeFilter Column="ClientTypeID" SessionField="RechercheClientType" ComparisionType="Equals" />
                    <data:RechercheAvanceeFilter Column="RegionID" SessionField="RechercheRegion" ComparisionType="Equals" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

and with Find as well:

    <data:RechercheAvanceeDataSource ID="RechercheAvanceeDataSource"
        SelectMethod="Find"
        EnablePaging="false"
        Sort="ClientNom"
        OnDataBinding="RechercheAvanceeDataSource_DataBinding"
        runat="server" >
        <Parameters>
            <data:SqlParameter Name="Parameters" UseParameterizedFilters="True">
                <Filters>
                    <data:RechercheAvanceeFilter Column="ClientNom" SessionField="RechercheClientNom" ComparisionType="Contains" />
                    <data:RechercheAvanceeFilter Column="ClientTypeID" SessionField="RechercheClientType" ComparisionType="Equals" />
                    <data:RechercheAvanceeFilter Column="RegionID" SessionField="RechercheRegion" ComparisionType="Equals" />
                </Filters>
            </data:SqlParameter>
        </Parameters>
    </data:RechercheAvanceeDataSource>

By the way, except for paging, what is the difference between using GetPaged and Find? Is one better or does it come to the same?

Many thanks!

JF 

Velum,

That's great.

The differences between the two, is that _GetPaged method using dynamic sql (there for making it sql injection vulnerable) vs _Find that uses parameterized expression (more secure) 

 Mike,

Thanks for the SqlParameter code the includes the SessionField, it solved a big problem. Now my GridViewSearch no longer works however. I'm just starting to dig into it but will entertain any suggestions.

Dean Manion

Dean,

GridViewSearchPanel manipulates Typed Datasource by setting ControlParameter named "WhereClause". For the GridViewSearchPanel to work, you may want to revert back to the ControlParameter and use seachpanel's filter property (ie. GridViewSearchPanel.Filter = string.Format("UserId={0}", Session["UserId"].ToString())

Hope that helps

 Mike,

That seemed to work and it helped explain the relationship between the filter and whereclause. I still have a problem with the initial load not applying the filter. I may just disable the GridView so that it does not load on page_load and enable it when the search is enabled.

Thanks for your help.

Dean

Dean,

Once you set the filter, also invoke databind method on the searchpanel

GridViewSearchPanel1.Filter = "UserId=1";

GridViewSearchPanel1.DataBind();

 Mike,

Duh, I invoked the DataBind on the Gridview, corrected it and it worked like charm.

Many thanks.

Dean