SQL异常:不允许从数据类型DateTime到INT的隐式转换.使用转换函数运行此查询

Question

这是我的存储过程：

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();

这是我调用先前存储过程的C＃功能：

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(); }
}

我有错误：

不允许从数据类型dateTime到int的隐式转换。使用转换函数运行此查询。

为什么？

谢谢！

更新！

@bostigchristensen我从dbtype更改为sqldbtype类型。所以我的C＃函数看起来像：

   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(); }
}

，但不幸的是，它引发了错误：字符串[2]：大小属性的大小无效为0。

Answer 1

sqlParameter构造函数采用SqlDbType而不是DbType：

SqlParameter(String, SqlDbType)

通过正确的类型SqlDbType： - ）

通过将DbType.DateTime传递到SqlDbType参数中，您基本上是通过潜在的错误映射，从而导致无法预测的结果。

一个例子：

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();
        }
    }
}