.NET datetime issue with SQL stored procedure

Question

I am getting the below error when executing my application on a Windows XP machine with .NET 2.0 installed. On my computer Windows 7 .NET 2.0 - 3.5 I am not having any issues. The target SQL server version is 2005. This error started occurring when I added the datetime to the stored procedure. I have been reading alot about using .NET datetime with SQL datetime and I still have not figured this out. If someone can point me in the right direction I would appreciate it.

Here is the where I believe the error is coming from.

private static void InsertRecon(string computerName, int EncryptState, TimeSpan FindTime, Int64 EncryptSize, DateTime timeWritten)
{
    SqlConnection DBC = new SqlConnection("server=server;UID=InventoryServer;Password=pass;database=Inventory;connection timeout=30");
    SqlCommand CMD = new SqlCommand();
    try
    {
        CMD.Connection = DBC;
        CMD.CommandType = CommandType.StoredProcedure;

        CMD.CommandText = "InsertReconData";
        CMD.Parameters.Add("@CNAME", SqlDbType.NVarChar);
        CMD.Parameters.Add("@ENCRYPTEXIST", SqlDbType.Int);
        CMD.Parameters.Add("@RUNTIME", SqlDbType.Time);
        CMD.Parameters.Add("@ENCRYPTSIZE", SqlDbType.BigInt);
        CMD.Parameters.Add("@TIMEWRITTEN", SqlDbType.DateTime);

        CMD.Parameters["@CNAME"].Value = computerName;
        CMD.Parameters["@ENCRYPTEXIST"].Value = EncryptState;
        CMD.Parameters["@RUNTIME"].Value = FindTime;
        CMD.Parameters["@ENCRYPTSIZE"].Value = EncryptSize;
        CMD.Parameters["@TIMEWRITTEN"].Value = timeWritten;

        DBC.Open();
        CMD.ExecuteNonQuery();
    }
    catch (System.Data.SqlClient.SqlException e)
    {
        PostMessage(e.Message);
    }
    finally
    {
        DBC.Close();
        CMD.Dispose();
        DBC.Dispose();
    }
}

Unhandled Exception: System.ArgumentOutOfRangeException: The SqlDbType enumeration value, 32, is invalid. Parameter name: SqlDbType at System.Data.SqlClient.MetaType.GetMetaTypeFromSqlDbType(SqlDbType target) at System.Data.SqlClient.SqlParameter.set_SqlDbType(SqlDbType value) at System.Data.SqlClient.SqlParameter..ctor(String parameterName, SqlDbType dbType) at System.Data.SqlClient.SqlParameterCollection.Add(String parameterName, SqlDbType sqlDbType) at ReconHelper.getFilesInfo.InsertRecon(String computerName, Int32 EncryptState, TimeSpan FindTime, Int64 EncryptSize, DateTime timeWritten) at ReconHelper.getFilesInfo.Main(String[] args)

Can you show us the table structure, and tell us what the value is of th eDateTime you ar etrying to insert? — Adriaan Stander
– Adriaan Stander, Commented Mar 9, 2010 at 7:02

Mike Two · Accepted Answer · 2010-03-09 07:04:52Z

2

Is your local box using SQL server 2008 but the other box is 2005? The @RUNTIME parameter is type SqlDbType.Time. That type did not exist in SQL server 2005. Also SqlDbType.Time has a value of 32 as the exception says. You can't store just time values before sql server 2008. You have to store @RUNTIME as a SqlDbType.DateTime in 2005.

answered Mar 9, 2010 at 7:04

Mike Two

46.5k9 gold badges84 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DanO Over a year ago

This comment made me realize that "@RUNTIME" SQL datatype was set to nvarchar. Once I updated the code all was well. Thanks.

Dustin Laine · Accepted Answer · 2010-03-09 07:04:23Z

0

Debug and see where this is happening. I do no think it is the DateTime.

answered Mar 9, 2010 at 7:04

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Collectives™ on Stack Overflow

.NET datetime issue with SQL stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related