ACCESS/SQL - Too Few Parameters

Question

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

Please use parameterized queries when executing SQL; otherwise, you're extremely vulnerable to SQL injection attacks. That should also fix your problemn as well. — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Nov 13, 2013 at 17:51
I would highly suggest changing your code so that you first create the SQL string and assign it to a variable, and then dbs.Execute that variable. This way you can put a breakpoint at the variable and see what Access thinks the SQL string is. Most times, what Access thinks it is and what you think it is are completely different. — Johnny Bones
– Johnny Bones, Commented Nov 13, 2013 at 18:01

Gord Thompson · Accepted Answer · 2013-11-17 23:18:36Z

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

ShadowLiberal · Accepted Answer · 2013-11-13 18:15:15Z

Do any of the text fields you're loading into your vbl fields contain special characters like these?

, ' "

All of those in a text field in a perfectly good SQL Insert command could screw things up, I bet that's what happening here.

It would be better if you actually use parameters here to, rather than loading the text in textboxes directly into your SQL queries, since you're opening yourself up to SQL Injections. What if someone types

"; Drop Table dbo_Transactions;

in one of your textboxes and you run this query? Your database is then totally screwed up because someone just deleted one of your tables.

A few links to info on using Parameters to prevent this issue, which I'll bet will also fix the too few parameters issue you're having.

http://forums.asp.net/t/886691.aspx

http://sqlmag.com/blog/t-sql-parameters-and-variables-basics-and-best-practices

Collectives™ on Stack Overflow

ACCESS/SQL - Too Few Parameters

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related