SessionParameter DefaultValue DBNull

Hi there,

This may seem like an easy question but I can't seem to get it to work. I've got a SessionParameter in a SqlDataSource with a SelectCommandType of StoredProcedure. If the SessionParameter is null/nothing I need to pass the stored procedure a value of System.DBNull.Value. How do I do that?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

SammyConnect With a Mentor Commented:
You need to handle the onSelecting event of the SqlDataSource
<asp:SessionParameter Name="ProductID" DefaultValue=""ConvertEmptyStringToNull="true" SessionField="MySessionProductID" Type="String"  />
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        if (e.Command.Parameters["@ProductID"].Value==null)
            e.Command.Parameters["@ProductID"].Value = DBNull.Value;

that should take care of it
CoconutTelegraphAuthor Commented:
btw, I've tried this but it doesn't seem to work....

<asp:SessionParameter Name="ProductID" DefaultValue="" ConvertEmptyStringToNull="true" SessionField="MySessionProductID" />
CoconutTelegraphAuthor Commented:
Interesting. Can I ask you from a design perspective, which do you think is cleaner and better code?

1) Using the method you've suggested for setting the values to DBNull.Value when null.

2) Ditching the SqlDataSource, and opting to hit the database directly in the DataBinding event of the GridView and then attaching the returned ADO.Net DataTable to the DataSource property.

Hmmm. I can't decide now. :(

Anyhow thanks!!
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I a not a fan of the SQLDataSource for a simple reason. It mixes data layer login with the UI layer
If you really have to use a control like the SqlDataSource, I would recommend using the ObjectDataSource.
take a look at the docs here

good luck
CoconutTelegraphAuthor Commented:
Good point. Well that certainly gives me something to think about. Thanks for the quick and thorough response Sammy!

You welcome and thanks for the Points
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.