Use set of stored array to fetch data through sql query

Question

Developing environment is VBA (AutoCAD)

I have Set of values stored in that array function BomLine(iCountItem).PartNo which I want to further use it fetch data into Excel through a specific SQL query.

Function gets its value from an AutoCAD drawing and stores the value into; that part is successful, but it's not fetching data into excel, maybe I am not correctly using the For Each loop?

This is what I am doing:

Dim OExcel As Object
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim xyz as Variant 

For x = 0 To UBound(DwgBlocks(i).AttributeName)
   Select Case DwgBlocks(i).AttributeName(x)
       Case OldDwgFmt.BOMPartNo
           BomLine(iCountItem).PartNo = DwgBlocks(i).AttributeVal(x)
   End Select
Next x

Exit For
Set OExcel = CreateObject("Excel.Application")

If OExcel Is Nothing Then
    Exit Sub
End If

With OExcel
    .Visible = True
    .Workbooks.Add
    .SheetsInNewWorkbook = 1
End With

With worksheet
    ConnectionString = "Provider=SQLOLEDB.1;Password=***;Persist Security Info=True;User ID=***;Data Source=***\***;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=***"
    cnn.Open ConnectionString
    cnn.CommandTimeout = 900

    List = "BomLine(iCountItem).PartNo"

    For Each xyz in List

        StrQuery = "SELECT * FROM [MY_Table] WHERE [MY_Column]='xyz'"    
        rst.Open StrQuery, cnn

        Set worksheet = OExcel.Worksheets(1).cells(1, 1).CopyFromRecordset rst
        Exit For
    Next xyz
End With

What exactly is `BomLine` ? Would help to show the declaration. — Tim Williams, Feb 18 '16 at 19:39
You need to stick `Option Explicit` at the top of your module, and declare the variables that you're using. There are a number of issues with this code, including `List` being assigned to a... string??? Then you're looping but `Exit For` would get out after the first iteration, so the whole loop construct is useless. And you're adding a workbook but not keeping its reference and then assuming the workbook you added contains `OExcel.Worksheets(1)` *which might not be the case*, and you're telling Excel you want 1 sheet in new workbook, but *after* you've created it... — Mathieu Guindon, Feb 18 '16 at 19:48
Bomline is a Block in my Autocad drawing which holds certain values. I have not mentioned all the values here which are in my actual code, because its irrelevant here, they are not my concern right now. List is my approach to take values out of "bomline" array. This is where I am wrong and no clue what to do. how to use that array for SQL. You are right about Exit For, it should not be there. Everything related to Excel seems to work fine. I have tested it without using array, it opens excel, open workbook and fetch required values exactly into the sheet. Excel part is fine. — Hamid Hemani, Feb 18 '16 at 20:03
Dim BomLine() As MyBOMLine Public Type MyBOMLine pnt As Variant ItemNo As String Quantity As String UOM As String PartNo As String Description As String Vendor As String VendorPart As String SubAssy As String End Type — Hamid Hemani, Feb 18 '16 at 20:08

score 0 · Accepted Answer · answered Feb 19 '16 at 00:12

0

    ConnectionString = "Provider=SQLOLEDB.1;Password=***;Persist Security Info=True;User ID=***;Data Source=***\***;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=***"
    cnn.Open ConnectionString
    cnn.CommandTimeout = 900

    List = "BomLine(iCountItem).PartNo"

    For Each xyz in BomLine

        StrQuery = "SELECT * FROM [MY_Table] WHERE [MY_Column]='" _
                     & xyz.PartNo & "'"    

        rst.Open StrQuery, cnn

        OExcel.Worksheets(1).cells(rows.count, _
                   1).end(xlUp).Offset(1,0).CopyFromRecordset rst

    Next xyz

answered Feb 19 '16 at 00:12

Tim Williams

154,628
8
97
125

StrQuery = "SELECT * FROM [MY_Table] WHERE [MY_Column]='" & BomLine(iCountItem).PARTNO & "'" 'Debug.Print StrQuery rst.Open StrQuery, cnn Set worksheet = OExcel.Worksheets(1) .Cells(1, 1).CopyFromRecordset rst so I am doing this, it works fine except I end up having the last value in the array of BomLine(iCountItem).PARTNO. However I want all values to be printed on excel or maybe some how if I could run it through the loop and one at a time each value prints on cell and autocad bomline populate it respectively until the last value in the array. – Hamid Hemani Feb 22 '16 at 23:13
I can't follow your code written in a comment like that: update your question if you have code you want to add. – Tim Williams Feb 22 '16 at 23:26
I tried but i dont see an option here to arrange the code. let me try again. – Hamid Hemani Feb 23 '16 at 00:19
You can edit your question and use the code-formatting button there. – Tim Williams Feb 23 '16 at 00:59

Use set of stored array to fetch data through sql query

1 Answers1