VBA(AUTOCAD) SQL query to store String Values

Question

I have a small issue, I am trying to code a Macro for Autocad in VBA. I am trying to read a certain column value through sending a SQL native query that connects to the database server.
The problem is that my String variable descToReturn that's going to hold a value of that column is returning null. I can't seem to figure out where I am wrong. So if anyone can advise that'd be great.

Here's the code below:

Private Sub btnDuplicate_Click()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ConnectionString As String
    Dim StrQuery As String
    Dim BOMLineToCheck As AcadBlockReference
    Dim BOMAttributes As Variant
    Dim partNoToCheck As String
    Dim i As Integer
    Dim descToReturn As String


    ConnectionString = "Provider=SQLxxxxx.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Data Source=xx\xx;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=xxx_xxx"

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = ConnectionString
    cnn.Open
    'cnn.CommandTimeout = 900
    Set rst = New ADODB.Recordset
    StrQuery = "SELECT * FROM [My Table Name] WHERE [My Column]='partNoToCheck'"

    For Each BOMLineToCheck In ThisDrawing.ModelSpace
        If BOMLineToCheck.Name = "BOM3LINE_old" Then
            BOMAttributes = BOMLineToCheck.GetAttributes()
            For i = 0 To UBound(BOMAttributes)
                If BOMAttributes(i).TagString = "PART#" Then
                    partNoToCheck = BOMAttributes(i).TextString
                End If
            Next i
        End If

        rst.Open StrQuery, cnn, adOpenDynamic
        With rst
            If Not .EOF Then
                descToReturn = rst![My Coulmn]                   
            End If
        End With
        rst.Close

        MsgBox descToReturn
    Next

End Sub

score 0 · Answer 1 · answered Apr 27 '18 at 13:16

0

Look closely for a typo:

descToReturn = rst![My Coulmn]

You probably mean:

descToReturn = rst![My Column]

See the difference?

answered Apr 27 '18 at 13:16

braX

11,506
5
20
33

I'm guessing the column isn't actually called my column and that it's obfuscated. But you may be right. – Jacob H Apr 27 '18 at 13:21
Yes, what Jacob commented is true, My column is the hypothetical name of that actual column I am referring to. It doesn't matter if its a typo here. – Hamid Hemani Apr 27 '18 at 15:34

score 0 · Answer 2 · answered Apr 27 '18 at 22:37

0

So, I found the answer, because I am bringing SQL within VBA environment, the syntax was not right and its a weird syntax:

correct Syntax for SQL query:

StrQuery = "SELECT * FROM [My Table] WHERE [My Column]= '" & partNoToCheck & "'"

answered Apr 27 '18 at 22:37

Hamid Hemani

3
2

This is not "weird" syntax. This is how you concatenate strings in VB. – addohm Apr 27 '18 at 23:04
You may also want to re-evaluate your code. If you're only expecting one value to be returned, or only using one column, this code is not entirely suitable. It could be done much better. – addohm Apr 27 '18 at 23:07
Please see https://stackoverflow.com/q/332365/11683 for why you should not do this. – GSerg Apr 27 '18 at 23:10
Jaberwocky, I am more used to of C# & Java oriented languages therefore, concatenate using ' & ' instead of + I found it little harder to digest. – Hamid Hemani Apr 28 '18 at 11:12
Yes, I am justexpecting one column values each time to return. So this is suitable parameters for me and I structured my code differently than what I have posted here to shorten my growth functionality, but thanks for the suggesstion. – Hamid Hemani Apr 28 '18 at 11:14

VBA(AUTOCAD) SQL query to store String Values

2 Answers2