Loop through range of cells and return price in SQL query

[Rödtjut]

Hi,

I use the following code to search for article numbers in a textfield on a form, in a sql database and return the price.
But what If I would like to search for serveral article numbers at the same time from a range of cells on the active sheet and return all prices and print them on the same row as each article number but in a different column?

Would much appreciate the help!

'Query
    SQLStr = "SELECT convert(money, poCost) FROM [dbo].[AVKParmenItemExportItemNoBid] WHERE MfrPartNbr LIKE '%" & txtProdDescr.Value & "%'"
     
    Set Cn = New ADODB.Connection
  
    With Cn
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    Set rs = .Execute(SQLStr)
    End With
     
    With rs
    
        Set .ActiveConnection = Nothing 
        K = .Fields.Count '
        r = .RecordCount
    
        vaData = .GetRows
    End With
  
    rs.Close
    Cn.Close

[dflak]

This seems to be more of a SQL statement, However, the attached shows a way to write the SQL dynamically once you get it figured out. I suggest returning the data to a "normalized form" of the format Article Number | Price and use a Pivot Table to format and collect the results.

Maybe this program can help you. It outlines a means for making SQL “on the fly.

I use this program to develop a list of models to track in another report. For example, there are numerous SKUs that roll up into the iPhone 7.

On the Model List sheet, I fill out column A and B and column C has a formula to produce the code snippet I want to use in my SQL statement.

The results of this query are on the Model D tab. I “zeroed” out some of the information to make the data non-sensitive. This table gets copied to a CSV file for use by other programs.

The part of interest to you is the SQL sheet. It contains three tables: Table_SQL, Table_SQL_Head and Table_SQL_End.

Table_SQL_Head and Table_SQL_End are static.

What the code does is clears out Table_SQL, copies in Table_SQL_Head, then appends the code snippets from the Model List and then appends Table_SQL_End.

Part of the code is a UDF called SuperCat which takes Table_SQL and makes it one big string. This string has a name, SQL_Model_D and is in cell A1 on the SQL Sheet. This is what you can set your SQLStr to in your code.

ModRefreshData has the main code. You will probably want to drop the part about writing to the CSV file. Also change the part that runs the query (SubSQL). Call your code here instead, just remember to set your SQL string to SQL_Model_D (or whatever you choose to call it).

You will also have to change the formulas in the table on the Model List sheet to produce the code snippets to meet your needs. In this case I am producing a bunch of case statements. In other programs I produce “IN” statements.

You will also have to change the contents of Table_SQL_Head and Table_SQL_End to reflect your query.

FYI: Cell E17 on the SQL sheet shows how to pass a parameter to the SQL code.