Using Variable in SQL Query Raises ADO Error

Question

I am trying to get a single itemcode from a SQL Server table (items) to be compared with an itemcode entered in an Excel sheet. To make this possible I have written the following VBA code in Excel 2019.

Function GetItemcodeFromSQLTable(sSQLArtikel As String) As String

    Dim connection As New ADODB.connection

    connection.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=SQL01;Initial Catalog=110"

    Dim query As String
    query = "select itemcode from items where itemcode = " & sSQLArtikel

    Dim rs As New ADODB.Recordset

    rs.Open query, connection

    connection.Close

End Function

I keep getting an error executing the line rs.open query, connection.

The purpose of this all is that I want to know if an itemcode already exists in the SQL table or not. If not, the rest of my VBA code wil create a XML file to import a new itemcode into the SQL table.

I have added a reference to "Microsoft Active X Data Objects 6.1 Library" in the VBA window.

Can anybody help me with this problem?

Many thanks.

The code I am using now is

Function CheckIfArticleCodeExistsInSQLDatabase(sSQLArtikel As String) As String

Dim query As String
Dim connection As ADODB.connection
Dim rs As ADODB.Record
Dim cmd As ADODB.Command


' PREPARED STATEMENT WITH PARAM PLACEHOLDERS
query = "select itemcode from items where itemcode = " & "'" & sSQLArtikel & "'"

' OPEN CONNECTION
Set connection = New ADODB.connection
connection.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
                   & "Data Source=SQL01;Initial Catalog=110"

' DEFINE COMMAND AND RECORDSET
Set cmd = New ADODB.Command
cmd.ActiveConnection = connection

Set rs = cmd.Execute(query, sSQLArtikel)    ' BIND PARAM VALUES

' ... DO SOMETHING WITH rs

rs.Close: connection.Close
Set cmd = Nothing: Set rs = Nothing: Set connection = Nothing

End Function

When executing the command "Set rs = cmd.Execute(query, sSQLArtikel)" an errormessage is displayed "the command text is not set for the command object".

I am doing something wrong but what?

Try change: query = "select itemcode from items where itemcode = " & sSQLArtikel to query = "select itemcode from items where itemcode = '" & sSQLArtikel & "'" — Zac
– Zac, Commented Jul 9, 2020 at 13:33
@Zac. I have tried the parenthesis in a number of ways. But always with the same result. "Invalid column name" — Eeko
– Eeko, Commented Jul 10, 2020 at 8:46
Obvious question but are you sure itemcode column exists in items table? — Zac
– Zac, Commented Jul 10, 2020 at 8:54

Parfait · Accepted Answer · 2020-07-14 15:30:49Z

1

Consider the industry best practice of parameterization whenever running SQL in application layer like VBA. Doing so, you avoid the need to concatenate and punctuate variables to an SQL string.

Specifically, the missing quotes around string literals (sSQLArtikel) is your issue. With ADO Command.Execute, you can define recordsets with binded parameters.

Dim query As String
Dim connection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

' PREPARED STATEMENT WITH PARAM PLACEHOLDERS
query = "select itemcode from items where itemcode = ?"

' OPEN CONNECTION
Set connection = New ADODB.Connection
connection.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
                   & "Data Source=SQL01;Initial Catalog=110"

' DEFINE COMMAND AND RECORDSET
Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = connection
    .CommandType = adCmdText
    .CommandText = query
    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, _
                                        Len(sSQLArtikel), sSQLArtikel)
    Set rs = .Execute
End With

' ... DO SOMETHING WITH rs

rs.Close: connection.Close
Set cmd = Nothing: Set rs = Nothing: Set connection = Nothing

edited Jul 14, 2020 at 15:30

answered Jul 9, 2020 at 14:20

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

4 Comments

Eeko Over a year ago

I have tried your code but it comes with an errormessage on the "set rs = " command. The error message is: "The connection cannot be used to perform this operation. The connection is closed or invalid in this context."

Eeko Over a year ago

I have updated my question with the complete function I am using. When executing this function the system still generates an errormessage.

Parfait Over a year ago

See edited version using a different approach to assign command parameters.

Eeko Over a year ago

This is the answer to my problem. Thank you very much for helping me out!!

Collectives™ on Stack Overflow

Using Variable in SQL Query Raises ADO Error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related