yqever (yqever at (no spam) 163.com) writes:
TEMPLATES table has a field X_UPDATED whose type is datetime. I use the
code to update it in SQL Server 2008 but an exception occurs.
cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=?
WHERE FILENAME='" + fileName + "'", this._dbConn);
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = DateTime.Now; // Exception
cmd.ExecuteNonQuery();
The exception is : System.Data.Odbc.OdbcException: ERROR [22008]
[Microsoft][SQL Server Native Client 10.0]Datetime field overflow.
Fractional second precision exceeds the scale specified in the parameter
binding.
It looks like the OdbcType.DateTime doesn't match SQL Server 2008's
datetime. How should I change my code to update the datetime field in
sqlserver 2008? Thanks.
BTW, sql server 2005 doesn't throw this exception.
Correct, they did some changes there. I ran into the same thing with
OLE DB. If memory serves the issue is that you cannot provide a value
with more than three decimals for the fractional value of a datetime
value in SQL Server, as datetime has a precision of 3.33 ms. So if
DateTime.Now includs microseonds you get this error. You would need
to strip these first.
Two other comments:
1) Why do you inline fileName in the query? You should make it a parameter
at all.
2) Why do you use Odbc Client to access SQL Server? OK, if your
application
also can interact with other platforms, there maybe reason for it. But
if you access SQL Server only, you should use SqlClient instead.
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
