Inserting a variable with SQL

[michelle]

Hello VB users,

I am trying to insert via SQL a record in my table.

Source:

Private Sub Form_Load()
Set DB = OpenDatabase("c:\q.mdb")
Dim QQQ As String
QQQ = "test"
DB.Execute " INSERT INTO MyTable " _
& "(MyField) VALUES " _
& "(QQQ);"
End Sub

error message: Too few parameters!

Normaly is variable QQQ a hard coded string like: "Test".

How do I change the SQL code so it accept a varible?

Thanks for reading,

Michelle.

[Bigley]

If you are inserting a string it has to be in single quotes, it doesn't necessarily have to be hard coded. If you hard code it as a variable and insert the variable you will need to concatenate it into your SQL statement along with the single quotes.

[JohnAtWork]

DB.Execute " INSERT INTO MyTable " _
& "(MyField) VALUES " _
& "(QQQ);"

should read

DB.Execute " INSERT INTO MyTable " _
& "(MyField) VALUES " _
& QQQ & ";"

In one line it looks like this:
DB.Execute " INSERT INTO MyTable (MyField) VALUES " & QQQ & ";"

[KentJ]

JohnAtWork,

I noticed you took care to maintain the concluding semicolon on the SQL statement. I have been bitten in the b*tt several times by the semicolon - usually I am copying and pasting from the Access SQL builder into VB (using Jet to access Access97 databases) and leave the semicolon on. In VB6/Jet it seems to frequently cause problems.

Any comment???

[JohnAtWork]

The semicolon functions as it does in C++ . . . it's the terminating character for a statement.
I.e. it functions as a carriage return.

Select BLAH
From BRAP
Where BOOF < 3;

is the same as

Select BLAH From BRAP Where BOOF < 3;

the semicolon indicates the end of the statement.


On occasion the semicolon raises errors for me as well, but it hasn't happened in a long time.