Need to pass a Date parameter to be empty ?

[glenn]

Hi folks,

I guess this question has to do with formatting dates. Seems like it should
be a no brainer but have spent too much time hunting so I am posting the
forum.

I have a function that needs to pass a Date data-type that is empty much
like a string would be empty if we were to place double quotes as in "".

The reason it needs to be empty is because I am passing parameters to a
database with an INSERT statement and if I pass a Null value, I get errors in
other parts of my web application.

Thanks for any insight on this.
glenn

[Jeff Dillon]

Pass a NULL, and in your stored procedure, handle the NULL appropriately.

You are using sprocs for everything, right?

Jeff

"glenn" <gl***@discussi ons.microsoft.c om> wrote in message
news:03******** *************** ***********@mic rosoft.com...

Hi folks,

I guess this question has to do with formatting dates. Seems like it
should
be a no brainer but have spent too much time hunting so I am posting the
forum.

I have a function that needs to pass a Date data-type that is empty much
like a string would be empty if we were to place double quotes as in "".

The reason it needs to be empty is because I am passing parameters to a
database with an INSERT statement and if I pass a Null value, I get errors
in
other parts of my web application.

Thanks for any insight on this.
glenn

[dotnetjose]

Glenn,

As you may already know the DateTime type can't be null. When declare:

DateTime dayToPass;

It's essentially the same as:

DateTime dayToPass = new DateTime();

Which has a value of "1/1/0001 12:00:00 AM" for both declarations.

So when you want to see if a DateTime variable was set or "null" you
can just do something like this:

private bool isValidDate(Dat eTime param)
{
DateTime nullDate = new DateTime();
return (param != nullDate);
}

That should do the validation for you.

Now when you are trying to setup the parameters for a datetime data
type in the stored procedure command, you can do this to pass in a
valid date time or a null if it's not valid. Here is an example of how
you could set up the function that would update a record with a valid
date or null value:

private void UpdateShippemen t(int deliveryId, string address, DateTime
shippedOn, int status)
{
SqlConnection setupconnection ;
SqlCommand command = new SqlCommand();
command.Connect ion = setupconnection ;
command.Command Type = System.Data.Com mandType.Stored Procedure;
command.Command Text = "dbo.StoredProc ToUse";
SqlParameter pDeliveryID = new SqlParameter("@ DeliveryID",
SqlDbType.Int);
SqlParameter pAddress = new SqlParameter("@ Address",
SqlDbType.VarCh ar, 50);
SqlParameter pShippedOn = new SqlParameter("@ ShippedOn",
SqlDbType.DateT ime);

pDeliveryID.Val ue = deliveryId;
pAddress.Value = address;

if (isValidDate(sh ippedOn))
{
pShippedOn.Valu e = shippedOn;
}
else
{
pShippedOn.Valu e = DBNull.Value;
}

int returnValue = command.Execute NonQuery();
}

I hope that helped, or atleast helped you think in the right
direction.... Have a good one.

Jose