I am trying to add some datetime values into a table. However, the database keeps throwing the following error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
The code is as follows:
string start = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + startTB.Text;
string end = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + endTB.Text;
//DateTime starts = Convert.ToDateTime(start);
//DateTime ends = Convert.ToDateTime(end);
sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES ('" + refno + "', '" + start + "', '" + end + "')";
where startTB and endTB contains a time in valid format (HH:MM)
The same error would occur even if I converted the string to a DateTime object before I try to insert it into the database.
a sample value of start would be "6/19/2007 10:00"
What puzzles me is that only the error is only thrown by the database (SQL Server Express) but not C#
I have tried using both datetime and smalldatetime for the columns in question in the database also.
There is no problem with your SQL Server or C#. The problem is on your input data (mismatched date format).
I strongly recommand to change your code as follow as. It is really very dangerous code you are writing (SQL Injuction).
Don't use Dynamic Query generation on the UI. Beaware of SQL Injunction. (use parameters or Stored Procs)
The following code will work for you, (the sql server parse the param before executing it, so you can easily identify the problem)
Code Snippet
sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES (@.refno, @.start, @.end)";
sqlInsert.Parameters.Add(new SqlParameter("@.refno", DbType.String));
sqlInsert.Parameters.Add(new SqlParameter("@.start", DbType.DateTime));
sqlInsert.Parameters.Add(new SqlParameter("@.end", DbType.DateTime));
sqlInsert.Parameters[0].Value = refno;
sqlInsert.Parameters[1].Value = starts;
sqlInsert.Parameters[2].Value = ends;
sqlInsert.ExecuteNonQuery();
|||Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.
The first most important step in a process like this is to figure out what you are trying to execute and take that statement to Management Studio (Express will do for this) and work out what the issue is in the query.
Date values should ideally use the standard formats. Look up "datetime data type, formats" in the index, and check out the ODBC timestamp format. It will always work.
|||thx all for the help
just a further question. what is sql injunction and what problems does it bring about?
couldn't seem to find anything substantial on it
|||
Louis Davidson wrote:
Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.
will bear this in mind
most of what i do now is more for interest or sch work, so my choice of tools are more restricted.
