Iterate (Loop) through String Array and generate MySql IN condition in VBScript

comunidadmexicana
 
on Dec 07, 2020 10:34 PM
2306 Views

Hi,

I have this array "31A", "31C", "32B", "32D", "52G"

I need iterate this array and using a query for extract rows from the table of a mysql database for each array single value.

My code as follow but in the query return instead of having

SELECT * FROM myTable WHERE xCOD IN ('31A'); 
SELECT * FROM myTable WHERE xCOD IN ('31C');
SELECT * FROM myTable WHERE xCOD IN ('32B');
SELECT * FROM myTable WHERE xCOD IN ('32D'); 
SELECT * FROM myTable WHERE xCOD IN ('52G');

I have SELECT * FROM myTable WHERE xCOD IN ("31A", "31C", "32B", "32D", "52G");

Obviously my code is not doing that.

Thoughts?

**VBSCript code**

Arr = Array("31A", "31C", "32B", "32D", "52G")
For I = 0 To UBound(Arr) 
    SQL = " SELECT * FROM myTable WHERE xCOD IN (" & Arr(I) & "); "
    Set Rs = CreateObject("ADODB.Recordset")
    Rs.Open SQL, cn

    If not Rs.eof then
        Do While Not Rs.EOF
            ...  
            Rs.MoveNext()
        Loop 
    Rs.Close()
    Set Rs = Nothing 
Next 
cn.Close()
Set cn = Nothing
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 08, 2020 03:44 AM

Check with below code.

Arr = Array("31A", "31C", "32B", "32D", "52G")
Sql = "SELECT * FROM myTable WHERE xCOD IN ("
For I = 0 To UBound(Arr)
    Sql = Sql & "'" & Arr(I) & "',"
Next
Sql = Sql & ");"
Sql = Replace(Sql, "',);", "');")
Set Rs = CreateObject("ADODB.Recordset")
Rs.Open Sql, cn

If Not Rs.eof Then
    Do While Not Rs.EOF
        ...
Rs.MoveNext()
    Loop

    Rs.Close()
Set Rs = Nothing
cn.Close()
Set cn = Nothing