Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DateTime, decimal type

Posted on 2010-11-27
7
473 Views
Last Modified: 2012-05-10
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
Comment
Question by:minglelinch
  • 3
  • 3
7 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 250 total points
ID: 34222247
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
 
LVL 12

Expert Comment

by:jagssidurala
ID: 34222256
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
 
LVL 1

Author Comment

by:minglelinch
ID: 34227354
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 12

Assisted Solution

by:jagssidurala
jagssidurala earned 250 total points
ID: 34227579
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
 
LVL 1

Author Comment

by:minglelinch
ID: 34239778
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
 
LVL 12

Expert Comment

by:jagssidurala
ID: 34250072
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
 
LVL 1

Author Comment

by:minglelinch
ID: 34346888
Thank you for all the helps. I have made it work successfully.
Happy Holiday Season !
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question