Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

DateTime, decimal type

I'm working on grid Add/Edit/Delete. If user enter nothing for DateType or decimal data type fields, I'm trying to fill null in database.

I used (DateTime)SqlDateTime.Null, but 01/01/1900 is inserted. For decimal, except manually inserted 0, I cannot find a way to insert null value, as it complains null is not a decimal type.

Is there a way to insert null value for datetime and decimal data type?

Thanks.
0
minglelinch
Asked:
minglelinch
  • 3
  • 3
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
In Stored Procedure, initialize parameter to NULL
... @date datetime = NULL

In C# cocde, you need to check whether it is default value for date and decimal. If not only pass the value to stored procedure.

So when the value of datetime variable is 01/01/1900 / decimal variable is 0, no value will be passed to Stored Procedure, and it will be inserted with the default value you set for the parameter inside Stored Procedure (NULL)

Hope it is clear
Raj
0
 
jagssiduralaCommented:
At the time of sending values to the DB check below condition

For Date Time variable
--------------------------
if (DateOfBirth == DateTime.MinValue.Date)
 {
                        cmd.Parameters.Add("DateOfBirth", SqlDbType.DateTime).Value = System.DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add("DateOfBirth", SqlDbType.DateTime).Value = DateOfBirth;
                    }


do same for decimal also


   if (dbPrice == "0")
                    {
                        cmd.Parameters.Add("Price", SqlDbType.Decimal).Value = System.DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add("Price", SqlDbType.Decimal).Value = dbPrice;
                    }
0
 
minglelinchAuthor Commented:
Thanks for the helpful comments.

I need to pass the format converted data to a C# function like

public void UpdateFin(int id, string accRec, decimal amt, DateTime payDate,...... )

The data are taken from front entry. If user does not enter any value in decimal or DateTime fields, I want to save null in DB.

I tried to pass the value of  (DateTime)System.DBNull.Value or System.DBNull.Value, but I got the error: CS0030: Cannot convert type 'System.DBNull' to 'System.DateTime'

If I pass  (decimal)System.DBNull.Value, I got the error: Cannot convert type 'System.DBNull' to 'decimal'

If I pass Convert.ToDecimal(System.DBNull.Value), I got error: Object cannot be cast from DBNull to other types.

Please help. Thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jagssiduralaCommented:
You can not send directly System.DBNull.Value in frontend directly to DB, this we can achive in DAL layer(if you have) or where your sqlcommand is present like my previous comments.  

If user does not enter any value in decimal or DateTime fields, set default value to these filelds and check the same cond in DAL or where your sqlcommand is present.

For ex: As per my comments

Default values are

PayDate = DateTime.MinValue,
amt = 0.00;

send these values to UpdateFin.
Thats it.




0
 
minglelinchAuthor Commented:
You mean insert DateTime.MinValue and 0..00 for dateTime and decimal type if frontend is empty.

I used "decimal amt  = 0.00M", it works fine.

I used DateTime.MinValue which is '1/1/0001 12:00:00 AM', I got the following error -
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It complains '1/1/0001' is out of range. If I tested the inserting statement with '1/1/2010', it inserts. DateTime.MinValue is out of range - it should not performs this way.
0
 
jagssiduralaCommented:
why char data type come into picture. are you dec;ared paydate as char or are you declared any variable declared as char. check it once.

my method will definatly work.  verify conversion in ur code. otherwise post ur code i will review and tell u.
0
 
minglelinchAuthor Commented:
Thank you for all the helps. I have made it work successfully.
Happy Holiday Season !
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now