非常奇怪的SqlClient错误与SmallDatetime参数在SQL Server 2014存储过程

Question

在过去的一个小时里，我一直被以下错误所困扰:

"过程或函数'write_call'期望参数'@Date'，但没有提供该参数。"

@date参数是一个smalldatetime。我试图传递值#1/27/2009 04:32:00 PM#

我正在使用SQLExpress 2014。

相同的存储过程已经在SQLExpress 2008上生产

T-SQL存储过程:

 ALTER proc [dbo].[write_call]
(
    @Date smalldatetime, @Duration smallint, @Ring tinyint, @Extension smallint = null, @Number varchar(20) = null, @LineID smallint, 
    @AccountID smallint = null, @AccountName varchar(30) = null, @UnitID smallint = null, @UnitName varchar(30) = null, @AreaName varchar(100) = null, @CallType char(1) = null,
    @CallCode varchar(6) = null, @Rate numeric(5,2) = 0.0, @Cost numeric(12,2) = 0.0, @TAC varchar(10) = null
)
as
declare @id int=0, @old_acctid int=0  --, @old_rate numeric(5,2)=0.0, @old_cost numeric(12,2)=0.0
select @id=o.ID,@old_acctid=o.AccountID from OutboundCalls o where o.Date=@Date and o.Duration=@Duration and o.Number=@Number
if @id=0
    begin
        insert into _OutboundCalls (Date, Duration, Ring, Extension, Number, LineID, AccountID, AccountName, UnitID, UnitName, AreaName, CallType,CallCode, Rate, Cost, TAC,redirected,contactid,CompanyID,CompanyName)
        values (@Date, @Duration, @Ring, @Extension, @Number, @LineID, @AccountID, @AccountName, @UnitID, @UnitName, @AreaName, @CallType, @CallCode, @Rate, @Cost, @TAC,1,0,1,'PricewaterhouseCoopers')
    end
else if @id>0 and @old_acctid<>@AccountID
    update OutboundCalls set AccountID=@AccountID, UnitID=@UnitID,AccountName=@AccountName,UnitName=@UnitName
    where OutboundCalls.ID=@id

VB。净2015:

Private Sub write_call(c As CallInfo)
    Using cnn = New SqlClient.SqlConnection(connString)
        Using cmd = New SqlClient.SqlCommand("write_call", cnn)
            cmd.Parameters.AddWithValue("@Date", c.dt)
            cmd.Parameters.AddWithValue("@Duration", c.secDurn)
            cmd.Parameters.AddWithValue("@Ring", 0)
            cmd.Parameters.AddWithValue("@Extension", c.src)
            cmd.Parameters.AddWithValue("@Number", c.dst)
            cmd.Parameters.AddWithValue("@LineID", c.lineId)
            cmd.Parameters.AddWithValue("@AccountID", c.account_id)
            cmd.Parameters.AddWithValue("@AccountName", c.account_name)
            cmd.Parameters.AddWithValue("@UnitID", c.unit_id)
            cmd.Parameters.AddWithValue("@UnitName", c.unit_name)
            cmd.Parameters.AddWithValue("@AreaName", c.area?.Name)
            cmd.Parameters.AddWithValue("@CallType", c.dst_type)
            cmd.Parameters.AddWithValue("@CallCode", c.callcode)
            cmd.Parameters.AddWithValue("@Rate", c.rate?.rate)
            cmd.Parameters.AddWithValue("@Cost", c.Cost)
            cmd.Parameters.AddWithValue("@TAC", c._oC)
            cnn.Open()
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                Stop
            End Try
        End Using
    End Using
End Sub

Answer 1

似乎您的代码错过了设置CommandType属性。此属性默认为CommandType.Text，当您将sql字符串作为CommandText传递时，它是合适的。

如果传递存储过程的名称，则需要设置

cmd.CommandType = CommandType.StoredProcedure

如果没有这个设置，SqlCommand的行为是不可预测的。