<asp:ControlParameter - pass null value to DB via sqldatasource

Question

I'm having problems passing a null value to a stored procedure, e.g. if an option isn't selected on a dropdown how can this be passed as null to through the sqdatasource to the database.

I currently have a number of dropdown controls on my page which are holding paramater values to get passed to a stored procedure.

When I select some values from these controls the gridview will display the results that it should, but what I'm having a problem with is when no values are selected is passing a null value to the SP. I've checked the SP and when I execute it and pass in null values it gets me the correct results so I'm happy with the SP. I've tried

ConvertEmptyStringToNull="true" DefaultValue=""

settings in the control paramater with no luck, and the dropdown's "ALL" option has a value of ""

The code for the sqldatasource is:

                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:scConnString %>"
                    SelectCommand="spGetOrgTickets" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="false">
                    <SelectParameters>
                        <asp:SessionParameter Name="org_id" Type="Int32" SessionField="org_id" DefaultValue="" ConvertEmptyStringToNull="false" />
                        <asp:ControlParameter ControlID="drpPriority" Name="priority_id" PropertyName="SelectedValue"
                            Type="Int32" ConvertEmptyStringToNull="true" DefaultValue="" />
                        <asp:ControlParameter ControlID="drpStatus" Name="status_id" PropertyName="SelectedValue"
                            Type="Int32" ConvertEmptyStringToNull="true" DefaultValue=""/>
                    </SelectParameters>
                </asp:SqlDataSource>

One of the dropdown's is :

                            <asp:DropDownList Style="width: 100%" ID="drpStatus" runat="server" class="field select"
                                AppendDataBoundItems="True" AutoPostBack="True" DataSourceID="SqlDataSource2_Status"
                                DataTextField="status" DataValueField="status_id">
                                <asp:ListItem Value="">All</asp:ListItem>
                            </asp:DropDownList>

Any ideas?

Thanks

thegunner · Accepted Answer · 2013-02-13 22:29:48Z

33

Sorted...just need CancelSelectOnNullParameter="false" within sqldatasource.

answered Feb 13, 2013 at 22:29

thegunner

7,22334 gold badges98 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Uğur Gümüşhan Over a year ago

this helped me immensely. thansk for replying yourself. so many people dont do it

Sarsaparilla Over a year ago

This option needs to be false by default. It is an insane time waster if one is not aware of this option

Collectives™ on Stack Overflow

<asp:ControlParameter - pass null value to DB via sqldatasource

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related