Sql Exception : Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query

Question

This is my stored procedure:

ALTER PROCEDURE [dbo].[NextPrevPost]
@PostQueryString NVARCHAR(100),
@NextID INT OUTPUT,
@NextTitle NVARCHAR(500) OUTPUT,
@NextPostDate DATETIME OUTPUT,
@NextQueryString NVARCHAR(100) OUTPUT,
@PrevID INT OUTPUT,
@PrevTitle NVARCHAR(500) OUTPUT,
@PrevPostDate DATETIME OUTPUT,
@PrevQueryString NVARCHAR(100) OUTPUT
AS
BEGIN
    DECLARE @ID INT;
    SELECT @ID=PostID FROM Post WHERE PostQueryString=@PostQueryString;

   SELECT TOP 1 
      @NextID = COALESCE(P.PostID,0),
      @NextTitle = P.PostTitle,
      @NextPostDate = COALESCE(P.PostDate, getdate()),
      @NextQueryString = P.PostQueryString 
   FROM 
      Post P 
   WHERE 
      P.PostDate >= (SELECT PostDate FROM Post WHERE PostID = @ID) 
      AND P.PostID != @ID
   ORDER BY 
      PostDate ASC

   SELECT TOP 1 
      @PrevID = COALESCE(P.PostID, 0),
      @PrevTitle = P.PostTitle,
      @PrevPostDate = COALESCE(P.PostDate, GETDATE()),
      @PrevQueryString = P.PostQueryString
   FROM 
      Post P 
   WHERE 
      P.PostDate <= (SELECT PostDate FROM Post WHERE PostID = @ID) 
      AND P.PostID != @ID
   ORDER BY 
      PostDate DESC

   IF(@PrevPostDate IS NULL)
   BEGIN
        SET @PrevPostDate = GETDATE();
   END

   IF(@NextPostDate IS NULL)
   BEGIN
        SET @NextPostDate = GETDATE();
   END

SET @PrevPostDate=GETDATE();
SET @NextPostDate=GETDATE();

And this is my c# function that call the previous stored procedure:

private void SetNextPrev(string s)
{
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlogConnectionString"].ConnectionString);

    SqlCommand command = new SqlCommand("NextPrevPost",connection);
    connection.Open();

    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("PostQueryString", DbType.String)).Value = s;

    SqlParameter nextid = new SqlParameter("NextID", DbType.Int32);
    nextid.Direction = ParameterDirection.Output;
    command.Parameters.Add(nextid);

    SqlParameter nexttitle = new SqlParameter("NextTitle", DbType.String);
    nexttitle.Direction = ParameterDirection.Output;
    command.Parameters.Add(nexttitle);

    SqlParameter NextPostDate = new SqlParameter("NextPostDate", DbType.DateTime);
    NextPostDate.Direction = ParameterDirection.Output;
    command.Parameters.Add(NextPostDate);

    SqlParameter NextQueryString = new SqlParameter("NextQueryString", DbType.String);
    NextQueryString.Direction = ParameterDirection.Output;
    command.Parameters.Add(NextQueryString);

    SqlParameter prevtid = new SqlParameter("PrevID", DbType.Int32);
    prevtid.Direction = ParameterDirection.Output;
    command.Parameters.Add(prevtid);

    SqlParameter prevtitle = new SqlParameter("PrevTitle", DbType.String);
    prevtitle.Direction = ParameterDirection.Output;
    command.Parameters.Add(prevtitle);

    SqlParameter PrevPostDate = new SqlParameter("PrevPostDate", DbType.DateTime);
    PrevPostDate.Direction = ParameterDirection.Output;
    command.Parameters.Add(PrevPostDate);

    SqlParameter PrevQueryString = new SqlParameter("PrevQueryString", DbType.String);
    PrevQueryString.Direction = ParameterDirection.Output;
    command.Parameters.Add(PrevQueryString);

    try
    {
        command.ExecuteNonQuery();

        if (prevtid.Value.ToString()!=string.Empty)
        {
        }

        if (nextid.Value.ToString()!=string.Empty)
        {
        }
    }
    catch { }
    finally { command.Dispose(); connection.Close(); }
}

I got the error:

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Why?

Thanks!

UPDATE!

@BoStigChristensen I changed from DbType into SqlDbType type. So my c# function looks like that:

   private void SetNextPrev(string s)
{

    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlogConnectionString"].ConnectionString);
    SqlCommand command = new SqlCommand("NextPrevPost",connection);
    connection.Open();

    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("PostQueryString", SqlDbType.NVarChar)).Value = s;


    SqlParameter nextid = new SqlParameter("NextID", SqlDbType.Int);
    nextid.Direction = ParameterDirection.Output;
    command.Parameters.Add(nextid);

    SqlParameter nexttitle = new SqlParameter("NextTitle", SqlDbType.NVarChar);
    nexttitle.Direction = ParameterDirection.Output;
    command.Parameters.Add(nexttitle);

    SqlParameter NextPostDate = new SqlParameter("NextPostDate", SqlDbType.DateTime);
    NextPostDate.Direction = ParameterDirection.Output;
    command.Parameters.Add(NextPostDate);


    SqlParameter NextQueryString = new SqlParameter("NextQueryString", SqlDbType.NVarChar);
    NextQueryString.Direction = ParameterDirection.Output;
    command.Parameters.Add(NextQueryString);

    SqlParameter prevtid = new SqlParameter("PrevID", SqlDbType.Int);
    prevtid.Direction = ParameterDirection.Output;
    command.Parameters.Add(prevtid);


    SqlParameter prevtitle = new SqlParameter("PrevTitle", SqlDbType.NVarChar);
    prevtitle.Direction = ParameterDirection.Output;
    command.Parameters.Add(prevtitle);


    SqlParameter PrevPostDate = new SqlParameter("PrevPostDate", SqlDbType.DateTime);
    PrevPostDate.Direction = ParameterDirection.Output;
    command.Parameters.Add(PrevPostDate);


    SqlParameter PrevQueryString = new SqlParameter("PrevQueryString", SqlDbType.NVarChar);
    PrevQueryString.Direction = ParameterDirection.Output;
    command.Parameters.Add(PrevQueryString);

    try
    {
        command.ExecuteNonQuery();

        if (prevtid.Value.ToString()!=string.Empty)
        {

        }

        if (nextid.Value.ToString()!=string.Empty)
        {

        }
    }
    catch { }
    finally { command.Dispose(); connection.Close(); }

}

But unfortunately it throw the error: String[2]: the Size property has an invalid size of 0.

Answer 1

SqlParameter constructor takes a SqlDbType and not DbType:

SqlParameter(String, SqlDbType)

Pass the correct type SqlDbType :-)

By passing DbType.DateTime into a SqlDbType parameter, you are basically passing a potentially wrong mapping, leading to unpredictable results.

An example of this:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            const SqlDbType MY_SQL_ENUM = new SqlDbType();
            const DbType MY_DB_ENUM = new DbType();

            var wrongParam = new SqlParameter("WrongTest", DbType.DateTime);
            Console.WriteLine(Enum.GetName(MY_DB_ENUM.GetType(), wrongParam.DbType));
            Console.WriteLine(Enum.GetName(MY_SQL_ENUM.GetType(), wrongParam.SqlDbType));

            var rightParam = new SqlParameter("RightTest", SqlDbType.DateTime);
            Console.WriteLine(Enum.GetName(MY_DB_ENUM.GetType(), rightParam.DbType));
            Console.WriteLine(Enum.GetName(MY_SQL_ENUM.GetType(), rightParam.SqlDbType));

            Console.ReadKey();
        }
    }
}