To be honest with you I am not even sure what a 'SelectParameters' is. I haven't used such a keyword/command before.
HOWEVER...
If you know in advance that you need to be able to have that date null then set the default value in the stored procedure and don't set ANY parameter value something like this:
Now when you want to pass the parameter for @ADateValue you declare the parameter but NEVER SET ANY VALUE. (Dont try to set it to null, don't ever set the value) The stored procedure does the rest.
Hi I had the same problem. I spent lot of time to resolve this problem, but finally I found on SqlDataSource property
CancelSelectOnNullParameter
which will be set to false. I hope that help you.[:)]
mycwcgr
Member
151 Points
920 Posts
How to pass a null to SelectParameters in SqlDataSource?
Feb 17, 2006 06:11 AM|mycwcgr|LINK
How to pass a null to SelectParameters in SqlDataSource?
The type of "CreateDate" is DateTime, the following code can be run correctly!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;
SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "2006-11-12";
now I hope to pass null value to the Parameter "CreateDate", but the following 3 section codes don't work!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;
SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = string.Empty;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;
SqlDataSource1.SelectParameters["CreateDate"].DefaultValue =null;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;
SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "";
jmurdock
Member
109 Points
430 Posts
Re: How to pass a null to SelectParameters in SqlDataSource?
Feb 17, 2006 12:04 PM|jmurdock|LINK
To be honest with you I am not even sure what a 'SelectParameters' is. I haven't used such a keyword/command before.
HOWEVER...
If you know in advance that you need to be able to have that date null then set the default value in the stored procedure and don't set ANY parameter value something like this:
<code>
CREATE PROCEDURE dbo.prInsertSomeStuff
(
@Value1 varchar(50),
@Value2 int,
@ADateValue datetime = Null
)
AS
</code>
Now when you want to pass the parameter for @ADateValue you declare the parameter but NEVER SET ANY VALUE. (Dont try to set it to null, don't ever set the value) The stored procedure does the rest.
That's how I do it anyway.
I hope that helps.
mvilcek
None
0 Points
1 Post
Re: How to pass a null to SelectParameters in SqlDataSource?
Oct 02, 2008 05:31 PM|mvilcek|LINK
Hi I had the same problem. I spent lot of time to resolve this problem, but finally I found on SqlDataSource property CancelSelectOnNullParameter which will be set to false. I hope that help you.[:)]
iandroutledg...
Member
21 Points
16 Posts
Re: How to pass a null to SelectParameters in SqlDataSource?
Oct 02, 2008 05:57 PM|iandroutledge|LINK
A good way to do this would be to handle the selecting event of your data souce:
<asp:SqlDataSource OnSelecting="SqlDataSourceSelectingEventHandler" ... />
Your event handler would look something like this:
protected void SqlDataSourceSelectingEventArg(Object sender, SqlDataSourceSelectingEventArgs e)
{
((SqlCommand)e.Command) .Parameters["paramNameHere"].Value = DBNull.Value;
// ((SqlCommand)e.Command) .Parameters.Add("paramNameHere", SqlDbType.VarChar, 32).Value = DBNull.Value;
}
MiaF
Member
41 Points
85 Posts
Re: How to pass a null to SelectParameters in SqlDataSource?
Feb 01, 2012 08:52 AM|MiaF|LINK
Thanks. This solves my problem.