Thursday, March 22, 2012

DateTime unable to save in datetime field of SQL database

Hi all, having a little problem with saving dates to sql database

I've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up

Error "

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

I've tried researching it but not been able to find something similar.

Heres the code:

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')";

Any help would be much appreciated

If you are using SQL Server, change the statement to

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',GetDate())

If you are using Access then use this:

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',Date())

|||

Sorry, my fault i should have said, i'm coding in c sharp, heres the expanded function

void AddToQuizIndex(String userName,String quizName,String description,String question_xml)

{

DateTime createOn = DateTime.Now;

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"','" +createOn+"','" + question_xml +"')";this.ActionSQLStatement(sSQLStatement);

}

|||

C# makes no difference. GetDate() in SQL Server will automatically apply the equivalent of C# datetime.now. But your database won't complain. Try it.

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"',GetDate(),'" + question_xml +"')";

Really, you should be using parameters rather than compiling dynamic SQL statements, but that's another topic.

|||

nice one, first time i tried it i didn't put ' ' round the GetDate()

Thanks very much for the replyMikesdotnetting, you really helped me out.

No comments:

Post a Comment