SQL query in VBA is not returning Distinct result

Question

In my database, One itemNo can have multiple ItemID. I had a list of ItemNo in Excel sheet. I wrote a VB Macro to retrieve the ItemIDs for all the ItemNo. The issue is one ItemNo can have multiple ItemID. So I got CntItemID. I then wrote another macro to Copy multiple rows based on X CntItemID and inserted all the rows.

'Dim gcn As ADODB.connection
Set gcn = New ADODB.Connection
gcn.ConnectionTimeout = 30
gcn.CommandTimeout = 1000
Dim rst As New ADODB.Recordset
Dim strConnection As String

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("sheet2")

strConnection = "some SSMS server"
gcn.Open strConnection

CreateSQLConnection = True

Dim sqlStr As String
Dim row As Integer
Dim rowNum As String
Dim colNum As String
Dim strValue As String
Dim LastRow As Long
With ws
    LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).row
End With

For row = 2 To 50
'For Each cell In rng.Cells

    rowNum = "A" & CStr(row)
    colNum = "B" & CStr(row)

    'Set rg = ws.Range("rowNum").Value
    'strValue = rg


    strValue = ws.Range(rowNum).Value
    'strValue = ws.Range("A2").Value

    sqlStr = "SELECT DISTINCt ITEMS.ITEMID From dbo.Items Where ITEMS.ITEMNO = '" & strValue & "'"
    rst.Open sqlStr, gcn
    ws.Range(colNum).CopyFromRecordset rst
    rst.Close
Next



gcn.Close
Set gcn = Nothing

Now my table looks like this.

ItemNO    ItemID       CntItemID
1           1             1
2           2             3
2           2             3
2           2             3

What i want is

ItemNO    ItemID       
1           1             
2           2             
2           3             
2           4

I want to retrieve unique ItemIDs. I know SSMS automatically does that. But VBA is automatically retrieving first ItemID and repeating it.

If you run a query in VB and SSMS, the final result set won't differ, if you;re querying the same objects (and data). SSMS won't be doing anything "automatically" — Thom A, Aug 19 '19 at 15:22
**Always use parameterized sql and avoid string concatenation** to add values to sql statements. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). *Depending on the value you are using this could also create an unexpected result set in your code if you include single quotes in your value.* — Igor, Aug 19 '19 at 15:24

Alberto Isoyi Ito · Answer 1 · 2019-08-23T21:11:14.973

0

You can use parameterized SQL for "strValue" and group by "ITEMID" in sql

Dim sql = "SELECT ITEMS.ITEMID From dbo.Items Where ITEMS.ITEMNO = @strValue GROUP BY ITEMS.ITEMID;"

Using cmd As New SqlCommand(sql, strConnection)
    cmd.Parameters.Add("@strValue", strValue)
    Using reader = cmd.ExecuteReader()
        ...
    End Using
End Using

edited Aug 23 '19 at 21:11

answered Aug 19 '19 at 15:42

Alberto Isoyi Ito

1
1

1

You should really be using `Parameters.Add`, not `AddWithValue` – Thom A Aug 19 '19 at 15:50

SQL query in VBA is not returning Distinct result

1 Answers1