datetime type in SQL Server 2008

[yqever]

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 param = cmd.Parameters.Add("FILEDATA",
OdbcType.VarBinary);
param.Value = data;
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = updatedTime; // 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.

 

[yqever]

Sorry, the code is:

cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=? WHERE
FILENAME='" + fileName + "'", this._dbConn);
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = updatedTime; // Exception
cmd.ExecuteNonQuery();

 

[Peter Duniho]

[...]
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = updatedTime; // 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.

You'd probably have a lot better luck posting your question in a newsgroup
where database-related questions are on-topic. Also, if you're not going
to post a concise-but-complete code example, at the very least you ought
to show all of the declarations for all the variables you are using.

That said, assuming "updatedTime" is of the type System.DateTime, then it
seems to me you've got at least a couple of possible solutions. The first
would be to assign "updatedTime.ToString()" where you provide some exact
format string that is compatible with the SQL data type. The second would
be to consider the documentation for OdbcParameter.Scale where it reads
"When using SQL Server Native Client 10 (or later) to bind a parameter
whose type is Decimal, Numeric, VarNumeric, DBDate, or DBTimeStamp, you
must manually specify an appropriate Scale value".

Note that I don't personally know what "an appropriate Scale value" might
be, never mind whether either of the two suggestions would even help.
That's where posting to the correct newsgroup comes in.

Pete

 

[Alberto Poblacion]

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 param = cmd.Parameters.Add("FILEDATA",
OdbcType.VarBinary);
param.Value = data;
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = updatedTime; // Exception
cmd.ExecuteNonQuery();

Is this the real code you are using? I see a silly mistake: when using
ODBC (contrary to the SqlClient), the name of the parameters is irrelevant,
and they are used in the same order in which you declare them. Your FIRST
parameter in the query is the "?" after X_UPDATED, but when assigning the
cmd.Parameters you create that parameter as the SECOND one. So the Odbc
driver tries to assign to the X_UPDATED field which is of type DateTime the
contents of FILEDATA, thereby resulting in an error.

 

[yqever]

Yes. What's wrong?

<snip>

Seriously? You're using ODBC to connect to SQL Server 2008...?

 

[yqever]

Sorry, the code is:

cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=? WHERE
FILENAME='" + fileName + "'", this._dbConn);
OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);
param2.Value = updatedTime; // Exception
cmd.ExecuteNonQuery();

 

[yqever]

Thank you very much.

ODBC was superseded *thirteen* years ago by OleDb:
http://msdn.microsoft.com/en-us/library/ms810892.aspx
http://database.ittoolbox.com/documents/odbc-vs-oledb-18150

The very first version of the .NET Framework back in 2002 introduced
native
.NET data providers to replace OleDb and didn't even include ODBC
natively,
which was available as a separate add-on to support very old databases for
which there were no OleDb drivers.

These days, there are native .NET data providers for all major databases,
and these run rings round OleDb in terms of performance and manageability.

Generally speaking, there is no good reason for using ODBC if OleDb is
available, and no good reason for using OleDb if a native .NET data
provider
is available.

Especially with SQL Server...
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.aspx

 

[yqever]

Thanks guys.
No exception occurs after truncating the milliseconds of DateTime.