sql with loop and var

Hi,

I wish to loop around an array putting the values into a mysql table.

Code:

---

For y = LBound(arrquery) To UBound(arrquery)
 
    With rdoQry
    .SQL = "INSERT INTO `Query` ( `Query_ID` , `Query_text` , `Answered` )VALUES ('', 'arrquery(y)', '0')"
    Set .ActiveConnection = cnMySql
    .Execute
    End With
 
    cnMySql.Close
Next y

---

arrquery() is my array, but it doesnt like me putting it in the SQL like that, what do i need to put round the arrquery(y) to make sure that it knows thar it is a variable i am posting to it, not a string.

Thanks in advance for any help :)

First - not sure if MySql requires it - but single quotes around the table name is not standard SQL...

Something like:

Code:

---

For y = LBound(arrquery) To UBound(arrquery)
 
    With rdoQry
    .SQL = "INSERT INTO Query ( `Query_ID` , `Query_text` , `Answered` )VALUES ('', '" & arrquery(y) & "', '0')"
    Set .ActiveConnection = cnMySql
    .Execute
    End With
 
    cnMySql.Close
Next y

---

Thanks, that has half solved my problem.
The loop works once now, however when it loops the 2nd time i get an error:

"Object invalid or not set" on the following line of code:

Code:

---

.SQL = "INSERT INTO `Query` ( `Query_ID` , `Query_text` , `Answered` )VALUES ('', '" & arrquery(y) & "', '0')"

---

Do you have any idea why this might be?

Sorry about that - didn't notice you were closing the connection in the loop...

Remove that line - actually drop it below the Next statement...

thanks... it works now :)