CODEWITHSUNDEEP
sqlsql-serverexcelvba
Eeko
Eeko

Reputation: 13

Using Variable in SQL Query Raises ADO Error

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?

Upvotes: 1

Views: 174

Answers (1)

Parfait
Parfait

Reputation: 107652

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

Upvotes: 1

Related Questions