CODEWITHSUNDEEP
sqlvbams-access
Juan Perez
Juan Perez

Reputation: 15

INSERT INTO Too few parameters

I have a table named tb_tools, with the following fields:

In a form called frm_insertion i have a button with a click event

Private Sub btn_add_Click()
    Dim part_temp As String
    Dim tempNum As Integer
    Dim tempStr As String

    tempNum = 12
    tempStr = "test"

    tool_temp = Nz(DLookup("[TOOL_ID]", "tb_tools", "[TOOL_ID]='" & [box_dien] & "'"), "-1")

    If StrComp(tool_temp, "-1", vbTextCompare) = 0 Then
        CurrentDb.Execute "INSERT INTO [tb_dies] ([TOOL_ID], [DESCRIPTION], [RACK], [COLUMN], [COMMENTS]) " _
            & "VALUES (" & tool_temp & "," & tempStr & "," & tempStr & "," & tempNum & "," & tempStr & ")"
    End If
End Sub

I get the error "Too few parameters. Expected 1" in the INSERT INTO line. I've checked the field names. In fact, i copied and pasted them into the code directly from the table.

I've also used the code provided here: http://forums.devarticles.com/microsoft-access-development-49/check-if-a-field-exists-in-a-table-using-vba-58559.html To check if the fields are available

Upvotes: 0

Views: 1471

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Your SQL string contains:

INSERT INTO [tb_dies] ([TOOL_ID], [DESCRIPTION], [RACK], [COLUMN], [COMMENTS]) 
VALUES (-1,temp,temp,12,temp,temp)

Which isn't valid SQL - right now the database server will consider those occurrences of temp to be a variable or column name; the temp probably needs to be in single apostrophes (meaning "a string"), like this: 

INSERT INTO [tb_dies] ([TOOL_ID], [DESCRIPTION], [RACK], [COLUMN], [COMMENTS]) 
VALUES (-1,'temp','temp',12,'temp','temp')

Hence you probably want your VB to be:

tempStr = "'test'"

PS; being MS Access, there's a chance that standard SQL wont work, and that single apostrophes aren't used for strings. If it's double quotes " to denote a string in access, you'll be looking at VBlike:

tempStr = """test"""

The commenters make valid points; you shouldn't use string concatenation to build SQL queries.. It's worth having a read through http://bobby-tables.com right now to get some background info on why it's bad, and then you'll be better equipped to embark down a path of software development that avoids writing software susceptible to this particular (and common) form of hacking

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

This may be easier to follow when using parameters in a query. 

Private Sub btn_add_Click()

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS lToolID LONG, DescText TEXT (255), RackText TEXT (255), " & _
        "ColumnNum LONG, CommentText LONGTEXT; " & _
        "INSERT INTO tb_dies (TOOL_ID, DESCRIPTION, RACK, COLUMN, COMMENTS) " & _
        "VALUES (lToolID, DescText, RackText, ColumnNum, CommentText)")

    With qdf
        .Parameters("lToolID") = 1
        .Parameters("DescText") = "Some Text"
        .Parameters("RackText") = "Some Rack Text"
        .Parameters("ColumnNum") = 5
        .Parameters("CommentText") = "Some really long comments"
        .Execute
    End With

    Set qdf = Nothing

End Sub

The SQL reads as: 

PARAMETERS  lToolID LONG
            , DescText TEXT (255)
            , RackText TEXT (255)
            , ColumnNum LONG
            , CommentText LONGTEXT; 
INSERT INTO tb_dies (TOOL_ID, DESCRIPTION, RACK, COLUMN, COMMENTS) 
VALUES      (lToolID, DescText, RackText, ColumnNum, CommentText);

Upvotes: 2

Related Questions