c# DateTime and T-SQL DateTime

Question

3.00/5 (1 vote)

See more:

How i can convert t-sql datetime to c# datetime?

I always got InvalidCastException.

code Employee:

C#

public Employee(string fName, string lName, int age, int earnings, DateTime dateOfRegistration)
		{
			this.First_Name = fName;
			this.Last_Name = lName;
			this.Age = age;
			this.Earnings = earnings;
			this.DateOfRegistration = dateOfRegistration;
		}

code Insert To Data Base: It work good!

C#

string command = string.Format("Insert into Employees Values ('{0}', '{1}', '{2}', '{3}', '{4}')",
				emp.First_Name, emp.Last_Name, emp.Age, emp.Earnings, emp.DateOfRegistration.Date);

code From data base to new employee: there is InvalidCastException:

C#

while(dr.Read())
			{
				emps.Add(new Employee((string)dr["First_Name"], (string)dr["Last_Name"], (int)dr["Age"], (int)dr["Earnings"],Convert.ToDateTime(dr["Date_of_registration"].ToString())));
			}

Posted 31-Aug-13 5:41am

AjmLikeIt

Add a Solution

Comments

Mike Meinz 31-Aug-13 11:56am

I don't think you have to do a conversion. I think you do have to tell C# that the value returned from the database using the dr class is a DateTime data type similar to what you did for the string and int data types.

Try this:

while(dr.Read())
{
emps.Add(new Employee((string)dr["First_Name"], (string)dr["Last_Name"], (int)dr["Age"], (int)dr["Earnings"],(DateTime)dr["Date_of_registration"]));
}

AjmLikeIt 31-Aug-13 11:58am

Still that same error.

Mike Meinz 31-Aug-13 16:06pm

Is there a possibility that the value in the database is NULL?

If it is possible that the value is NULL, than an ISNULL() in the SELECT statement or a dr.IsDBNull() check in the application should solve it.

Use Visual Studio Interactive Debugger to step through the code and see what is returned by dr["Date_of_registration"] or use SQL Server management Studio to execute the Select statement and see if any of the date values are returned as NULL.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-08-31T06:05:00

Solution 1

Assuming that the data in your DB is stored as a DateTime column, then it should just be a case of casting it:

C#

emps.Add(new Employee((string)dr["First_Name"], (string)dr["Last_Name"], (int)dr["Age"], (int)dr["Earnings"],(DateTime)dr["Date_of_registration"]));

If it isn't then you need to look at what is stored in your DB, as there is a major problem!

Posted 31-Aug-13 6:05am

OriginalGriff

Comments

AjmLikeIt 31-Aug-13 12:10pm

That doesn't help. I trying using "date", "datetime" and still that same error.

CHill60 31-Aug-13 12:14pm

What is actually in dr["Date_of_registration"]

AjmLikeIt 31-Aug-13 12:17pm

2013-08-31 00:00:00

CHill60 31-Aug-13 12:21pm

That looks fine, are your regional settings such that dates are expected in yyyy-MM-dd format and not yyyy-dd-mm and is your database column a DateTime?

AjmLikeIt 31-Aug-13 12:27pm

Where i can see these regional settings?

CHill60 31-Aug-13 13:29pm

http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo.currentculture.aspx[^]

OriginalGriff 31-Aug-13 13:38pm

If the cast is failing, then the value coming back from the DB is either not coming from a DateTime column in the database, or the value is NULL. Have you checked in the code against DBNull.Value to eliminate the second possibility? Have you checked the DB definition to ensure it is a DateTime column?

AjmLikeIt 1-Sep-13 4:26am

Yes the DB column isn't null. And it's the same column ;/

OriginalGriff 1-Sep-13 4:41am

No, have you checked in the C# code to see what the value being returned is? And have you checked the column is defined as DateTime, rather than "a string containing a date and time value"?
Sorry to ask these - but they are the most obvious starting points, and I can't access your DB directly! :laugh: