ConvertEmptyStringToNull will not work for the SqlDataSource control

ConvertEmptyStringToNull will not work for the SqlDataSource control by Fredrik Normén

Closed

as By Design Help for as By Design

Type:	Bug
ID:	104201
Opened:	7/23/2004 8:52:04 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

The ConvertEmptyStringToNull of the Datas source's parameter will return a null and not DBNull that is required for the SelectCommand to be executed and pass a NULL value to the parameter of the select command.

Details (expand)

English

Visual Studio 2005 Beta 1

Controls

Windows XP Professional

US English

Create a parameter of type string for the SqlDataSource select parameters. Set the ConvertEmptyStringToNull to true. When the Value of the parameter is empty the SelectCommand will not be executed. The ParameterValue property returns an null if the ConvertEmptyStringToNull is true, shouldn’t it return a DBNull? When a DBNull is set as the value of the parameter the SelectCommand will be executed and the parameter will have the value NULL. I think the ConvertEmptyStringToNull should add a NULL value to the parameter, or you should have a new option of choosing that an empty string should return a DBNull. To make this work know we have to do use the Selecting event of the SqlDataSource control to do something like this:

void SqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (Request.QueryString["ID"] == null)
((IDbDataParameter)e.Command.Parameters["@CustomerID"]).Value = DBNull.Value;
}

In the example above we use a QueryStringParameter and if the QueryString is null we set our parameter of the Command object to DBNull. If we do so, the SelectCommand will be executed, but if the value of the paramerer is set to Null it will not.

The Select command will not be executed.

It should pass a NULL value to the parameter.

0 attachments

Hi Fredrik,

You are correct that the way to accomplish this is to set CancelSelectOnNullParameter to false if you want the SqlDataSource to select data even when one or more of the SelectParameters is null. Internally, the SqlDataSource will always convert null values to DbNull before passing on to the SelectCommand, so you don't need to do this in an event. The CancelSelectOnNullParameter property is still needed for the scenario where you don't want to execute the SelectCommand at all when any parameter is null. The common scenario is a TextBox bound to a ControlParameter, whose initial value is empty string (gets converted to null because ConvertEmptyStringToNull is true by default), where you only want the query to execute when the user has actually supplied a non-empty value. I agree however, that there are some scenarios where you want to execute the SelectCommand anyway even is there is a null parameter (which gets converted to DbNull by SqlDataSource), and that is precisely why we allow you to set the CancelSelectOnNullParameter property to false.

Hope this clarifies it a bit,
The Web Platform and Tools Team