1

I'm running MySQL queries in Excel through an ODBC connection.

So far, no problem using parameters, putting an ? in my queries, and when prompted, selecting cells C2, and I get the result in A4 as expected:

SELECT id_product FROM ps_product WHERE id_product = ? LIMIT 100;

So far so good using id_product = ? and when prompted selection C2

Thing is, I want to be able to run the query using a cell value as a string as a parameter, so doing something like:

SELECT id_product FROM ps_product WHERE id_product IN (?) LIMIT 100;

When Excel prompts me which cell to use as a parameter, I select the cell C2 that I changed here, and it still only returns in A4 the first ID, not the second one:

Still first id only

Any idea of what I should do to make this work, so in A4 A5 I have both product IDs showing?

I've looked all around, used simple and double quotes in C2 or directly in the query, played with parenthesis, nothing seems to work...

Many thanks!

1 Answers1

1

in this situation you are attempting to pass a list into the cell to be read in your SQL query. Unfortunately, this is not possible by dynamically selecting the cell using the ? in your query in excel. There are a few posts about this and possible workarounds like this one involving VBA.

Parameterized query from Excel with IN clause

However, if you are able to work VBA, I would suggest creating a sub that would run your query and dynamically pick up ranges on a worksheet. For example,

Sub query()
    Sql = "SELECT id_product FROM ps_product WHERE id_product in '" & Range("C2") & "' AND '" & Range("D4") & "' "

    With ThisWorkbook.Connections("Your Connection Name Here").ODBCConnection
        .CommandText = Sql
        .Refresh
    End With
end sub

Of course, the idea extends if you have more IDs to enter.

G.Thinh
  • 26
  • 2