从存储过程插入期间"The precision is invalid"？

Question

我正在尝试使用存储过程将值从VB6插入SQL Server，但是当我想执行该过程时，出现此错误：

精度无效

我已经改变了很多次尝试进入它们的方式，以至于我不再知道该怎么做。

这是 VB6 中的代码

Dim strSQL As String
strSQL = "INSERT INTO FacturasPendientes (IdFactura,FechaFactura,CodigoProveedor,NombreProveedor," _
& " SubTotal,Iva,Total,FechaVencimiento,DiasDescuento,DescProntoPago,Pagado) VALUES (?,?,?,?,?,?,?,?)"
Dim CmdCont As ADODB.Command
Set CmdCont = New ADODB.Command
With CmdCont
Set .ActiveConnection = Cnn
.CommandType = adCmdStoredProc
.CommandText = "sp_FacturasCompras"
.Parameters.Append CmdCont.CreateParameter("@IdFactura", adVarChar, adParamInput, 50, TxtFactura.Text)
.Parameters.Append CmdCont.CreateParameter("@FechaFactura", adDate, adParamInput, 50, dtpFactura.Value)
.Parameters.Append CmdCont.CreateParameter("@CodigoProveedor", adVarChar, adParamInput, 50, TxtCodigoProveedor.Text)
.Parameters.Append CmdCont.CreateParameter("@NombreProveedor", adVarChar, adParamInput, 100, txtProveedor.Text)
.Parameters.Append CmdCont.CreateParameter("@SubTotal", adNumeric, adParamInput, 18, Round(Val(m_Subtotal), 2))
.Parameters.Append CmdCont.CreateParameter("@Iva", adNumeric, adParamInput, 18, Round(Val(m_Iva), 2))
.Parameters.Append CmdCont.CreateParameter("@Total", adNumeric, adParamInput, 18, Round(Val(m_Total), 2))
.Parameters.Append CmdCont.CreateParameter("@FechaVencimiento", adDate, adParamInput, 50, FechaFinal)
.Parameters.Append CmdCont.CreateParameter("@DiasDescuento", adVarChar, adParamInput, 50, txtDias.Text)
.Parameters.Append CmdCont.CreateParameter("@DescProntoPago", adVarChar, adParamInput, 50, txtDescuento.Text)
.Parameters.Append CmdCont.CreateParameter("@Pagado", adBigInt, adParamInput, chkPago.Value)
.Prepared = True
.Execute
End With
MsgBox "Se Grabaron los datos"

存储过程是这样的：

ALTER PROCEDURE [dbo].[sp_FacturasCompras] 
-- agregamos los valores de la consulta
@IdFactura VARCHAR(50),
@FechaFactura DATE,
@CodigoProveedor VARCHAR(50),
@NombreProveedor VARCHAR(100),
@SubTotal NUMERIC(18),
@Iva NUMERIC(18),
@Total NUMERIC(18),
@FechaVencimiento DATE,
@DiasDescuento VARCHAR(50),
@DescProntoPago VARCHAR(50),
@Pagado BIGINT
AS
BEGIN
INSERT INTO FacturasPendientes (IdFactura, FechaFactura, CodigoProveedor, NombreProveedor,
SubTotal, Iva, Total, FechaVencimiento, 
DiasDescuento, DescProntoPago, Pagado)
VALUES (@IdFactura, @FechaFactura, @CodigoProveedor, @NombreProveedor,
@SubTotal, @Iva, @Total, @FechaVencimiento,
@DiasDescuento, @DescProntoPago, @Pagado)
END

Answer 1

调用存储过程的最简单方法是使用Cnn如下所示的连接对象

Cnn.sp_FacturasCompras TxtFactura.Text, dtpFactura.Value, _
TxtCodigoProveedor.Text, txtProveedor.Text, _
Round(Val(m_Subtotal), 2), Round(Val(m_Iva), 2), _
Round(Val(m_Total), 2), FechaFinal, txtDias.Text, _
txtDescuento.Text, chkPago.Value

。这看起来像对Cnn对象的普通方法调用。

如果您必须使用ADODB.Command那么最好是使用一些帮助程序函数来初始化ADODB.Parameter实例并像这样ADODB.Command实例

Option Explicit
Private m_Subtotal      As Double
Private m_Iva           As Double
Private m_Total         As Double
Private Sub Form_Load()
Dim oCmd As ADODB.Command
Set oCmd = InitCommandHelper("sp_FacturasCompras", _
InitParamHelper(TxtFactura.Text, adVarChar, 50), _
InitParamHelper(dtpFactura.Value, adDBDate), _
InitParamHelper(TxtCodigoProveedor.Text, adVarChar, 50), _
InitParamHelper(txtProveedor.Text, adVarChar, 100), _
InitParamHelper(Round(Val(m_Subtotal), 2), adDecimal, Precision:=18, NumericScale:=2), _
InitParamHelper(Round(Val(m_Iva), 2), adDecimal, Precision:=18, NumericScale:=2), _
InitParamHelper(Round(Val(m_Total), 2), adDecimal, Precision:=18, NumericScale:=2), _
InitParamHelper(FechaFinal, adVarChar, 50), _
InitParamHelper(txtDias.Text, adVarChar, 50), _
InitParamHelper(txtDescuento.Text, adVarChar, 50), _
InitParamHelper(chkPago.Value, adBigInt))
Debug.Print oCmd.Parameters.Count
End Sub
Public Function InitCommandHelper( _
ByVal sStoredProc As String, _
ParamArray Params() As Variant) As ADODB.Command
Const FUNC_NAME     As String = "InitCommandHelper"
Dim lIdx            As Long
On Error GoTo EH
Set InitCommandHelper = New ADODB.Command
With InitCommandHelper
If InStr(sStoredProc, ".") > 0 Then
.CommandText = sStoredProc
Else
.CommandText = "dbo." & sStoredProc
End If
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
lIdx = LBound(Params)
Do While lIdx <= UBound(Params)
.Parameters.Append Params(lIdx)
lIdx = lIdx + 1
Loop
End With
Exit Function
EH:
Debug.Print "Critical error: " & Err.Description & "[Form1." & FUNC_NAME & "]", Timer
End Function
Public Function InitParamHelper( _
ByVal vValue As Variant, _
ByVal eType As ADODB.DataTypeEnum, _
Optional ByVal Size As Long, _
Optional Direction As ADODB.ParameterDirectionEnum = adParamInput, _
Optional ByVal Name As String, _
Optional ByVal Precision As Long, _
Optional ByVal NumericScale As Long) As ADODB.Parameter
Const FUNC_NAME     As String = "InitParamHelper"
Const DBL_NUM_LIMIT As Double = 10# ^ 12 ' 922337203685477#
Const EMPTY_GUID    As String = "{00000000-0000-0000-0000-000000000000}"
On Error GoTo EH
Select Case eType
Case adVarChar, adVarWChar, adChar, adWChar
'--- trim varchar params'
If Not IsNull(vValue) And Not IsEmpty(vValue) Then
vValue = Left$(vValue, Size)
End If
Case adLongVarChar, adLongVarWChar, adLongVarBinary
'--- fix default size for BLOB params'
If Size = 0 Then
Size = -1
End If
Case adDBTimeStamp
If CDate(vValue) = CLng(CDate(vValue)) Then
eType = adDBDate
ElseIf CLng(CDate(vValue)) = 0 Then
eType = adDBTime
Else
eType = adDate
End If
Case adNumeric, adDecimal, adCurrency
'--- numeric range'
If IsNumeric(vValue) Then
If vValue > DBL_NUM_LIMIT Then
vValue = DBL_NUM_LIMIT
ElseIf vValue < -DBL_NUM_LIMIT Then
vValue = -DBL_NUM_LIMIT
End If
End If
Case adGUID
If Direction = adParamInput Then
If IsEmpty(vValue) Then
vValue = EMPTY_GUID
End If
End If
End Select
Set InitParamHelper = New ADODB.Parameter
InitParamHelper.Name = Name
InitParamHelper.Type = eType
InitParamHelper.Direction = Direction
InitParamHelper.Size = Size
InitParamHelper.Value = vValue
If Precision > 0 Then
InitParamHelper.Precision = Precision
End If
If NumericScale > 0 Then
InitParamHelper.NumericScale = NumericScale
End If
Exit Function
EH:
Debug.Print "Critical error: " & Err.Description & "[Form1." & FUNC_NAME & "]", Timer
Set InitParamHelper = Nothing
End Function

请注意，NUMERIC(18)被解析为浮点后有 0 位数字的NUMERIC(18, 0)，但您对这些参数使用Round(..., 2)，因此可能是错误。只需使用NUMERIC(18, 2)或 evern 更好的DECIMAL(19, 2)因为精度 18 和精度 19 使用相同的 5 字节进行存储。

另请注意，sp_前缀是为系统存储过程保留的，请不要将其用于用户定义的存储过程。您必须为sp提供不同的前缀，例如usp_(不带下划线(。

Answer 2

我怀疑问题出在adNumeric列上。 尝试如下代码：

Dim p As Parameter
With CmdCont
Set p = .CreateParameter("@SubTotal", adNumeric, adParamInput)
p.Precision = 18
p.NumericScale = 2
p.Value = Round(Val(m_Subtotal), 2)
.Parameters.Append p
End With

您可以根据需要调整精度和小数位数。