sql with loop and var

[amanda6192]

Hi,

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

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

[szlamany]

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

Something like:

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

[amanda6192]

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:

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

Do you have any idea why this might be?

[szlamany]

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

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

[amanda6192]

thanks... it works now