VBA and SQL: String in WHERE Clause throwing error

[Heder_Santos]

Hello All,

I'm using a connection to query an external database. Basically, I'm selecting a few columns and filtering them by a list of contracts. This list will always vary in length, so I'm iterating through a single column in a worksheet and then concatenating each row in a single string variable (examples below), which I will later use in the SQL WHERE Clause (WHERE contract_number IN "concatenated_list").

When I concatenate up to 4 contracts, everything works and the SQL query runs perfectly, but when I concatenate more than that, it throws an error: "Run Time Error '5': Invalid Procedure Call or Argument".

Does anyone know why it's happening and how can I solve this? I'd really appreciate your help =)

Here are the procedures I'm using to generate the string and then passing it to the query in the next procedure.

[COLOR="#0000CD"]Sub[/COLOR] CreateTargetContractsList()

    [COLOR="#0000CD"]Dim[/COLOR] strContractList [COLOR="#0000CD"]As String[/COLOR]
    [COLOR="#0000CD"]Dim[/COLOR] strActualContract [COLOR="#0000CD"]As String[/COLOR]
    
    [COLOR="#0000CD"]Dim[/COLOR] dblLastUsedRowIndex [COLOR="#0000CD"]As Double[/COLOR]
    [COLOR="#0000CD"]Dim[/COLOR] dblActualIterationRow [COLOR="#0000CD"]As Double[/COLOR]
    
    dblActualIterationRow = 4
        
    dblLastUsedRowIndex = wksLendingSQL.Cells(Rows.Count, 1).End(xlUp).Row
    
    [COLOR="#0000CD"]Do While[/COLOR] wksLendingSQL.Cells(dblActualIterationRow, 1) <> [COLOR="#0000CD"]Empty[/COLOR]
    
        strActualContract = "'" & wksLendingSQL.Cells(dblActualIterationRow, 1).Value & "'"
        
        dblActualIterationRow = dblActualIterationRow + 1

        [COLOR="#0000CD"]If Not[/COLOR] dblActualIterationRow > dblLastUsedRowIndex [COLOR="#0000CD"]Then[/COLOR]

            strActualContract = strActualContract & ", " & Chr(13) & Chr(10)

        [COLOR="#0000CD"]End If[/COLOR]

        strContractList = strContractList & strActualContract
        
    [COLOR="#0000CD"]Loop[/COLOR]
    
    [COLOR="#0000CD"]Call [/COLOR]ConsultaLending(strContractList)

[COLOR="#0000CD"]End Sub[/COLOR]

[COLOR="#0000CD"]Sub[/COLOR] ConsultaLending(strContractList [COLOR="#0000CD"]As String[/COLOR])

    [COLOR="#0000CD"]With[/COLOR] ActiveWorkbook.Connections("Consulta de DW").ODBCConnection
    
        .BackgroundQuery = [COLOR="#0000CD"]True[/COLOR]
        .CommandText = Array( _
        "SELECT " & _
            "nr_contrato, " & _
            "parcela, " & _
            "taxa_ap_dia, " & _
            "taxa_cliente_ao_mes, " & _
            "data_originacao, " & _
            "data_vencimento, " & _
            "data_pagto_cont, " & _
            "valor_cedido, " & _
            "valor as valor_pago " & _
        "FROM " & _
            "lending " & _
        "WHERE " & _
            [COLOR="#00FF00"]'Problem arises here... (in fact, when running the entire "CommandText"property)[/COLOR]
            "nr_contrato IN (" & strContractList & ")")
            
        .CommandType = xlCmdSql
        .Connection = "ODBC;DSN=GBCerebro32Bits;"
        .RefreshOnFileOpen = [COLOR="#0000CD"]False[/COLOR]
        .SavePassword = [COLOR="#0000CD"]False[/COLOR]
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = [COLOR="#0000CD"]False[/COLOR]
        
    [COLOR="#0000CD"]End With[/COLOR]

    ActiveWorkbook.Connections("Consulta de DW").Refresh
    
[COLOR="#0000CD"]End Sub[/COLOR]

Example of a String contained in "strContractList" who works fine:

'27-98477/17',
'27-05165/17',
'27-00427987/18',
'27-00430901/18'

Example of a String contained in "strContractList" who throws an error (just one more contract added...):

'27-98477/17',
'27-05165/17',
'27-00427987/18',
'27-00430901/18',
'27-19088/17'

Thanks!

Heder D. Santos

 

[Fazza]

Maybe there is a 255 character string length within that array?

There is no need for an array: it is just how the macro recorder presents it. Any way to get the simple text string will do.

Can you re-write the creation of the text that is assigned to the commandtext.
What I'm thinking is a different way to make the SQL string without an array. Any string manipulation that suits you. And then have the .commandtext = that_string_you_make

 

[Heder_Santos]

Thank you so much Fazza!

You were right about the character limit within the array.

I simply removed the array "wrap" from the .CommandText string and it worked like a charm... solution below

[COLOR=#0000cd]Sub[/COLOR] ConsultaLending(strContractList [COLOR=#0000cd]As String[/COLOR])

    [COLOR=#0000cd]With[/COLOR] ActiveWorkbook.Connections("Consulta de DW").ODBCConnection
    
        .BackgroundQuery = [COLOR=#0000cd]True[/COLOR]
        [COLOR=#008000]'"Array" wrapping removed from .CommandText property: code now works perfectly regardless of string length[/COLOR]
        .CommandText = _
        "SELECT " & _
            "nr_contrato, " & _
            "parcela, " & _
            "taxa_ap_dia, " & _
            "taxa_cliente_ao_mes, " & _
            "data_originacao, " & _
            "data_vencimento, " & _
            "data_pagto_cont, " & _
            "valor_cedido, " & _
            "valor as valor_pago " & _
        "FROM " & _
            "lending " & _
        "WHERE " & _
            "nr_contrato IN (" & strContractList & ")"
            
        .CommandType = xlCmdSql
        .Connection = "ODBC;DSN=GBCerebro32Bits;"
        .RefreshOnFileOpen = [COLOR=#0000cd]False[/COLOR]
        .SavePassword =[COLOR=#0000cd] False[/COLOR]
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile =[COLOR=#0000cd] False[/COLOR]
        
    [COLOR=#0000cd]End With[/COLOR]

    ActiveWorkbook.Connections("Consulta de DW").Refresh
    
[COLOR=#0000cd]End Sub[/COLOR]

Thanks a lot!

Best regards from Brazil,

Heder Santos

 

[Fazza]

well done, Heder
regards, Fazza