Need to pass a Date parameter to be empty ?

[Guest]

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

 

[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(DateTime 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 UpdateShippement(int deliveryId, string address, DateTime
shippedOn, int status)
{
SqlConnection setupconnection;
SqlCommand command = new SqlCommand();
command.Connection = setupconnection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.StoredProcToUse";
SqlParameter pDeliveryID = new SqlParameter("@DeliveryID",
SqlDbType.Int);
SqlParameter pAddress = new SqlParameter("@Address",
SqlDbType.VarChar, 50);
SqlParameter pShippedOn = new SqlParameter("@ShippedOn",
SqlDbType.DateTime);

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

if (isValidDate(shippedOn))
{
pShippedOn.Value = shippedOn;
}
else
{
pShippedOn.Value = DBNull.Value;
}

int returnValue = command.ExecuteNonQuery();
}

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

Jose