ACCESS / SQL - 参数太少 [英] ACCESS/SQL - Too Few Parameters

问题描述

我使用下面的代码在事务表中创建一个新记录，插入语句的第二行抛出一个错误：太少的参数。我已经双重选中，所有的字段名称是正确的。还可能导致此类错误？

I am using the code below to create a new record in the "transactions table" the second line of the insert statement is throwing an error: Too few parameters. I have double checked and all of the field names are correct. What else could cause this type of error?

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = CurrentDb

Dim vblCustomerID As String
Dim vblMealType As String
Dim Charge As Currency
Dim vblDate As String
vblDate = Format(Date, "yyyy-mm-dd")
txtCustomerID.SetFocus
vblCustomerID = txtCustomerID.Text

txtMealType.SetFocus
vblMealType = txtMealType.Text

txtCharge.SetFocus
vblCharge = txtCharge.Text

dbs.Execute "INSERT INTO dbo_Transactions" _
    & "(CustomerID, MealID, TransactionAmount, TransactionDate) VALUES " _
    & "(" & vblCustomerID & ", " & vblMealType & ", " & vblCharge & ", " & vblDate & ");"
dbs.Close

推荐答案

，使用参数化查询是更好的方式来做你想做的事。尝试这样：

As others have suggested, using a parameterized query is a much better way of doing what you're attempting to do. Try something like this:

Dim qdf As DAO.QueryDef
Set qdf = dbs.CreateQueryDef("", _
        "PARAMETERS prmCustomerID Long, prmMealID Long, prmTransactionAmount Currency, prmTransactionDate DateTime;" & _
        "INSERT INTO dbo_Transactions (CustomerID, MealID, TransactionAmount, TransactionDate) " & _
        "VALUES ([prmCustomerID], [prmMealID], [prmTransactionAmount], [prmTransactionDate]) ")
qdf!prmCustomerID = txtCustomerID.Value
qdf!prmMealID = txtMealType.Value
qdf!prmTransactionAmount = txtCharge.Value
qdf!prmTransactionDate = Date()
qdf.Execute dbFailOnError
Set qdf = nothing

这篇关于ACCESS / SQL - 参数太少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！