Problem with "Insert Into" in SQL

  • Thread starter Thread starter CDM
  • Start date Start date
C

CDM

I have a "Insert Into" statement using the "Values" keyword to add a row to
my table. I'm using variables instead of literal values. Example:

INSERT INTO mytable(Field1, Field2,..Field6) VALUES (Var1,Var2,...Var6)

I'm getting an error message that says, "Expected 6 parameters." I'm
wondering if variables don't work? and, if not, is there some other way to
insert them?
 
INSERT INTO mytable(Field1, Field2,..Field6) VALUES (Var1,Var2,...Var6)

You need to take the variables out of the string and contencate the values
into the string.

strSQL = "INSERT INTO mytable(Field1, ...) " _
& "VALUES (" & Var1 & ", " & Var2 & "," ... & Var6 & ")"

That way the SQL is taking the values of the variables, rather than
inserting the variable names.

At least, I think thats what it is... I haven't spent a lot of time using
insert queries, but the above is standard procedure for entering variable
values into an SQL string.

hth


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Also, keep in mind that if you are using string datatype variables, you may
need to enclose them in single quotes within the SQL statement. Here's a
sample vased on Var1 being a String, Var 2 being a string, and Var3 being
some sort of numeric datatype (long, byte, etc)


strSQL = "INSERT INTO mytable(Field1, ...) " _
& "VALUES ('" & Var1 & "', '" _
& Var2 & "', " _
& Var3 &")"


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Just be aware that text values need to be delimited with quotes, and dates
need to be delimited with # (and must be in a format Access will recognize).

That means something like:

strSQL = "INSERT INTO mytable(NumericField, TextField, DateField, ...) " _
& "VALUES (" & Var1 & ", '" & Var2 & "'," & _
Format(Var3, "\#yyyy\-mm\-dd\#") & ... & Var6 & ")"

Just to be explicit, that second line is

& "VALUES (" & Var1 & ", ' " & Var2 & " ' ," & _

Note that it won't work if var2 contains an apostrophe.
 
Back
Top