Loss of datetime precision when attaching parameters to an ADO.NET command

[Chris Lacey]

Hi,

I am encountering some strange losses of datetime precision when calling a
stored procedure through ADO.NET. I'm using ExecuteNonQuery in the
Microsoft Data Access Application Block for .NET, which simply creates an
ADO.NET command, and attaches each of the parameters, before calling
cmd.ExecuteNonQuery().

I have a stored procedure that does nothing but take a whole load of input
parameters and insert them into a table. Calling the SP directly from Query
Analyser works fine. However, calling the SP from code as follows:

....
parameters[5] = new SqlParameter("@Time", SqlDbType.DateTime);
parameters[5].Value = time;
....
SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.StoredProcedure,
"MyStoredProc", parameters);

....results in the time entry made into the database always having a zero
value for the seconds.


Similarly, if I execute:

SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.Text, "EXEC
MyStoredProc ..., @Time, ...", parameters);

....the same thing happens.


However, if I dump the INSERT statement that's in the stored procedure into
a string called sql in the code, and then execute:

SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.Text, sql,
parameters);

....then everything works just fine and I don't get the rounding error.


Does anyone have any ideas as to why this may be?

Many thanks in advance for your help,

Chris.

 

[Rogas69]

Hi Chris,
it seems that somewhere your parameter is converted to smalldatetime
datatype. What are parameters of your stored procedure?
Peter

 

[Chris Lacey]

Thanks, Peter - I was being an idiot with my SP input parameter types, as
you rightly suggested!