Let's break down what's going wrong in both your code versions and fix them step by step.

First, the initial GetItemcodeFromSQLTable function issues: #

• If your itemcode column is a string type in SQL Server, your query is missing single quotes around sSQLArtikel—this causes a SQL syntax error when you run rs.Open.
• It’s also vulnerable to SQL injection if sSQLArtikel contains special characters like apostrophes.
• The function doesn’t return a meaningful value (you declared it as String but don’t set it) and doesn’t handle cases where no matching itemcode exists.

Why the modified CheckIfArticleCodeExistsInSQLDatabase throws the "command text not set" error: #

1. Typo: You declared rs As ADODB.Record instead of ADODB.Recordset—that’s an invalid object type for your result set.
2. Incorrect Command usage: When using ADODB.Command, you need to set the CommandText property first. You can’t pass the query string directly to cmd.Execute()—that method’s parameters are for tracking records affected and passing parameter values (not the query itself).
3. Wasted parameterization: You concatenated sSQLArtikel into the query anyway, which defeats the purpose of using a Command object for safe parameter handling.

Corrected, Secure Code (Parameterized Query) #

This version fixes all issues, uses proper ADODB.Command syntax, and eliminates SQL injection risks:

Function CheckIfArticleCodeExistsInSQLDatabase(sSQLArtikel As String) As Boolean
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    
    ' Initialize and open the SQL connection
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
        & "Data Source=SQL01;Initial Catalog=110"
    
    ' Set up the parameterized command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT itemcode FROM items WHERE itemcode = ?" ' Placeholder for parameter
    cmd.CommandType = adCmdText ' Explicitly set command type to text
    
    ' Add the parameter (handles string quoting and prevents injection)
    ' Adjust Size to match the length of your itemcode column in SQL (e.g., 50 if it's VARCHAR(50))
    cmd.Parameters.Append cmd.CreateParameter( _
        Name:="ItemCode", _
        Type:=adVarChar, _
        Direction:=adParamInput, _
        Size:=255, _
        Value:=sSQLArtikel)
    
    ' Execute the command and retrieve the recordset
    Set rs = cmd.Execute()
    
    ' Return True if a matching itemcode was found (recordset is not empty)
    CheckIfArticleCodeExistsInSQLDatabase = Not rs.EOF
    
    ' Clean up all ADODB objects to avoid memory leaks
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
End Function

Key Fixes & Improvements: #

• Changed the return type to Boolean (far more logical for an existence check than String)
• Fixed the Record → Recordset typo
• Properly sets cmd.CommandText with a parameter placeholder (?)
• Uses CreateParameter to safely pass the itemcode—no more manual string concatenation or quoting
• Checks rs.EOF to determine if any matching records exist
• Explicitly cleans up all ADODB objects to prevent memory leaks

Quick Fix for the Initial Recordset Approach (Not Recommended for Security) #

If you want to stick with a simpler Recordset.Open method (note: this still carries SQL injection risks if input is untrusted), fix the query string and add error handling:

Function GetItemcodeFromSQLTable(sSQLArtikel As String) As Boolean
    Dim conn As New ADODB.Connection
    Dim query As String
    Dim rs As New ADODB.Recordset
    
    conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=SQL01;Initial Catalog=110"
    
    ' Escape apostrophes to avoid syntax errors (still risky for injection)
    query = "SELECT itemcode FROM items WHERE itemcode = '" & Replace(sSQLArtikel, "'", "''") & "'"
    rs.Open query, conn
    
    ' Return True if a match exists
    GetItemcodeFromSQLTable = Not rs.EOF
    
    rs.Close
    conn.Close
End Function

Always prefer the parameterized ADODB.Command approach for security and reliability, especially if the input can come from users or untrusted sources.

内容的提问来源于stack exchange，提问作者Eeko