Object variable or With block variable not set while running SQL dynamic statement in VBA

Question

On the userform there are a few textboxes. When an item in the combobox is chosen, only some of them stay visible. I want to step through visible textboxes at that time and fill them with data from SQL Server database. Items on the combobox list are identical with table names so it becomes a parameter in the SQL statement.

Debugging the code an error pops up while executing the statement (it runs fine in Management Studio). Maybe I'm missing some characters like ' or " or a whitespace (but the code isn't in red) or there is something wrong with the code and using recordset itself:

    Private Sub cboTyp_AfterUpdate()

Dim rs As ADODB.Recordset
Dim Nazwa As String
Dim Skr As String
Dim Kolumna As String
Dim c As Control

On Error Resume Next
For Each c In Me.Controls
    Select Case c.TabIndex
        Case 3 To 10:
            If c.Visible = True Then
            Nazwa = c.Name
            Skr = Right(Nazwa, 5)

            On Error GoTo Nazwa_Initialize_Err:
            Set rs = New ADODB.Recordset

                rs.Open "SELECT DISTINCT c.name AS NazwaKolumny FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id" & _
                            " WHERE c.name LIKE '%" & Skr & "';", _
                                con, adOpenStatic
                Kolumna = rs.Fields("NazwaKolumny")
                rs.Close
                Set rs = Nothing

                'Wyszukanie ostatniej wartości dla wybranej składowej
                    Set rs = New ADODB.Recordset
                    rs.Open "Declare @sqlCommand varchar(max)" & _
                                " Declare @columnName varchar(250)" & _
                                " Set @columnName = (SELECT DISTINCT c.name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id" & _
                                " WHERE c.name LIKE '%" & Skr & "')" & _
                                " Set @sqlCommand = 'SELECT [' + @columnName + ']' + " & _
                                " 'FROM [" & cboTyp.Value & "] WHERE ID = (SELECT MAX(ID) FROM [" & cboTyp.Value & "])'" & _
                                " Exec (@sqlCommand);", _
                                    con, adOpenStatic

                'Jeżeli brak wartości (zwróci NULL) oznacza to przejdź do normalnego wstawiania
                    If rs.RecordCount = 0 Then
                        c.SetFocus
                    'Jeśli znajdzie wartość to ją wstaw
                    Else
                        rs.MoveFirst
                            Do
                                Nazwa = rs.Fields(Kolumna)
                                rs.MoveNext
                            Loop Until rs.EOF
                    End If
            End If
    End Select
Next
On Error GoTo 0

'Opuszczanie pola - zamknięcie i wyczyszczenie Recordset
Nazwa_Initialize_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing

Exit Sub

debug.print the SQL then copy/paste into SSMS and check its validity (your sub-q will return > 1 row atm so it will fail — Alex K.
– Alex K., Commented Jul 20, 2015 at 11:20
This is bad idea to use diacritic characters in the names of variables, like: Dim Skrócona As String — mielk
– mielk, Commented Jul 20, 2015 at 11:32

debatanu · Accepted Answer · 2015-07-21 15:31:06Z

2

EDIT please assign a new recordset object to it before using, for eg

Removed the SET NOCOUNT ON. It is mostly required for stored procs.

    set rs= New ADODB.Recordset

   rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
   rs.Open "Declare @sqlCommand varchar(max)" & _
                        " Declare @columnName varchar(250)" & _
                        " Set @columnName = (SELECT DISTINCT c.name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id" & _
                        " WHERE c.name LIKE '%" & Skrócona & "')" & _
                        " Set @sqlCommand = 'SELECT ' + '[' + @columnName + ']' + " & _
                        "' FROM [" & cboTyp.Value & "] WHERE ID = (SELECT MAX(ID) FROM [" & cboTyp.Value & "])'" & _
                        " Exec (@sqlCommand);", _
                            con, adOpenStatic

NEW

Also added the rs.CursorLocation for proper recordcount.

edited Jul 21, 2015 at 15:31

answered Jul 20, 2015 at 11:14

debatanu

7765 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alex K. Over a year ago

SET NOCOUNT ON is a bit random but the missing New is the problem.

debatanu Over a year ago

Removed the NOCOUNT ON. It was not necessary. Thanks.

kkris77 Over a year ago

Yes, setting new recordset was the problem.

kkris77 Over a year ago

@ user3081756 I've edited the question because recordcount says -1 when there are no records in the database in current table. Any idea ?

debatanu Over a year ago

I think in case of ADODB.Recordset you also need to set the cursor location to get the proper recordcount. You can find some detail in here. Also I have added the code for it above

Cetin Basoz · Accepted Answer · 2015-07-20 11:20:11Z

0

You have extra single quotes at the beggining and end of:

"' FROM [" & cboTyp.Value & "] WHERE ID = (SELECT MAX(ID) FROM [" & cboTyp.Value & "])'"

I wouldn't suggest such a code that is prone to SQL injection attacks and use parameters instead. Check sp_ExecuteSQL in help for samples.

answered Jul 20, 2015 at 11:20

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

3 Comments

kkris77 Over a year ago

Would this really be prone to SQL injection ? Parameter is a table name from tables list. The source list can be placed in a protected worksheet to avoid any changes or extracted from database with a sql statement.

Cetin Basoz Over a year ago

Yes it would. The most obvious one is in the where clause. Probably you are not allowing your users to select the possible values from a list. No idea what the source of "Skrócona" is. If you are allowing its value to be set from an input then you are wide open to attack.

kkris77 Over a year ago

It's a few characters from control's name used to find the column name so no input here.

Collectives™ on Stack Overflow

Object variable or With block variable not set while running SQL dynamic statement in VBA

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related