时间戳

首页 > 解决方案 > 将常规查询转换为参数化查询的问题

vb.net - 将常规查询转换为参数化查询的问题

问题描述

我遇到了这段看起来像这样的旧代码：

        Dim sql As String = "exec wa_vterm_Insert_upd_Purchase_Card @merchant_id = '" & db.SqlSafe(sMerchantID) & "'" &
        ", @reference = '" & db.SqlSafe(reference) & "' " &
        ", @national_tax = " & nationalTax & " " &
        ", @customer_vat_number = '" & db.SqlSafe(customerVatNumber) & "' " &
        ", @discount_amount = " & discountAmount & " " &
        ", @duty_amount = " & dutyAmount & " " &
        ", @vat_invoice_number = '" & db.SqlSafe(vatInvoiceNumber) & "' " &
        ", @vat_tax_amount = " & vatTaxAmount & " " &
        ", @vat_tax_rate = " & vatTaxRate & " " &
        ", @destination_country_code = '" & db.SqlSafe(destCountryCode) & "' "
    Trace.WriteLine(sql, LogLevel.Debug)
    db.ExecuteNonQuery(sql)

我正在将其转换为参数化查询，并对其进行了编码： 

 Dim cmd As New SqlCommand("wa_vterm_Insert_upd_Purchase_Card", con)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@merchant_id", sqlDbType:=SqlDbType.VarChar).Value = sMerchantID
    cmd.Parameters.Add("@reference", sqlDbType:=SqlDbType.VarChar).Value = reference
    cmd.Parameters.Add("@national_tax", sqlDbType:=SqlDbType.VarChar).Value = nationalTax
    cmd.Parameters.Add("@customer_vat_number", sqlDbType:=SqlDbType.VarChar).Value = customerVatNumber
    cmd.Parameters.Add("@discount_amount", sqlDbType:=SqlDbType.VarChar).Value = discountAmount
    cmd.Parameters.Add("@duty_amount", sqlDbType:=SqlDbType.VarChar).Value = dutyAmount

    cmd.Parameters.Add("@vat_invoice_number", sqlDbType:=SqlDbType.VarChar).Value = vatInvoiceNumber

    cmd.Parameters.Add(" @vat_tax_amount", sqlDbType:=SqlDbType.Decimal).Value = vatTaxAmount

    cmd.Parameters.Add(" @vat_tax_rate", sqlDbType:=SqlDbType.Decimal).Value = vatTaxRate
    cmd.Parameters.Add(" @destination_country_code", sqlDbType:=SqlDbType.VarChar).Value = destCountryCode

    Try
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Trace.WriteLine(ex.Message & ex.Source, LogLevel.Debug)
    Finally
        cmd.Connection.Close()
    End Try

但是对于新的代码，我得到了这个异常：“过程或函数'wa_vterm_Insert_upd_Purchase_Card'需要参数'@vat_tax_amount'，但没有提供。”

我不明白为什么？

标签： vb.net

解决方案

根据上面的建议，答案是@var_tax_amount 前面的空格

推荐阅读