VBA and SQL: String in WHERE Clause throwing error

Heder_Santos

New Member
Joined
Jun 5, 2015
Messages
12
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.

Code:
[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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Jun 5, 2015
Messages
12
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

Code:
[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
 
Last edited:

Forum statistics

Threads
1,136,864
Messages
5,678,212
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top