VBA Excel上传SQL报错‘object variable or with block variable not set’求助

Hey there, let's break down why you're hitting that frustrating error on the cmd.CommandText = strSQL line—and fix it up.

The Core Issue #

You declared the cmd variable as Dim cmd As ADODB.Command, but you never created an instance of it (i.e., you didn't assign it to a new command object). That means cmd is still Nothing when you try to set its CommandText property, which throws the "object variable not set" error.

Plus, you need to link the command to your open database connection so it knows where to run the SQL.

Step-by-Step Fixes #

Let's go through the key changes needed:

1. Instantiate the Command Object
   After opening your connection (cn.Open strConn), add a line to create the command instance:

   Set cmd = New ADODB.Command
   

2. Link the Command to Your Connection
   Right after creating the command, set its ActiveConnection property to your open cn connection:

   cmd.ActiveConnection = cn
   

3. Fix the Trailing Comma Handling
   Your current line Mid(strSQL, Len(strSQL), 1) = ";" might cause issues if LastRow is 0 (no data). A safer way to remove the trailing comma is to check if strSQL2 isn't empty before trimming it:

   If Len(strSQL2) > 0 Then
       strSQL2 = Left(strSQL2, Len(strSQL2) - 1) ' Remove the last comma
   End If
   strSQL = strSQL & strSQL2 & ";"
   

4. Optional: Handle Single Quotes in Data
   If any of your Player values have single quotes (e.g., "O'Neil"), your SQL will fail. Add a quick replace to escape those:

   strSQL2 = strSQL2 & "('" & Replace(sTroksheet.Cells(excel_row, 1).Value, "'", "''") & "'),"
   

Corrected Full Code #

Here's your code with all the fixes applied:

Dim cn As ADODB.Connection
Set sTroksheet = ThisWorkbook.Sheets("Mlist")
Set cn = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strConn As String
Dim SQLstr As String
Dim SQLstrl As String
Dim Password As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Dim Port_Name As String
Dim strTable As String
Dim excel_row As Long
Dim cmd As ADODB.Command
Dim rst_recordset As ADODB.Recordset
Dim LastRow As Long ' Added declaration for LastRow

If ThisWorkbook.Sheets("Tournament Settings").Range("D4") = vbNullString Then
    MsgBox "Please setup database connection first in (DB Setup) in top menu"
    Exit Sub
Else
    Server_Name = Sheets("Software_Setup").Range("c3").Value
    Database_Name = Sheets("Software_Setup").Range("c4").Value
    User_ID = Sheets("Software_Setup").Range("c5").Value 'id user or username
    Password = Sheets("Software_Setup").Range("c6").Value 'Password
    Port_Name = Sheets("Software_Setup").Range("c7").Value 'Password
    
    strConn = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=" & _
              Server_Name & ";Database=" & Database_Name & _
              ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    
    cn.Open strConn
    
    LastRow = sTroksheet.Range("A65536").End(xlUp).Row
    strTable = Database_Name & ".TLHMember_List"
    strSQL = "INSERT INTO " & strTable & " (Player) VALUES "
    strSQL2 = ""
    
    For excel_row = 1 To LastRow
        ' Escape single quotes in player names to avoid SQL errors
        strSQL2 = strSQL2 & "('" & Replace(sTroksheet.Cells(excel_row, 1).Value, "'", "''") & "'),"
    Next excel_row
    
    ' Safely remove trailing comma and add semicolon
    If Len(strSQL2) > 0 Then
        strSQL2 = Left(strSQL2, Len(strSQL2) - 1)
    End If
    strSQL = strSQL & strSQL2 & ";"
    
    ' Fix: Instantiate command and link to connection
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    
    cmd.CommandText = strSQL
    cmd.Execute
    
    cn.Close
End If

Quick Notes #

• I also added a declaration for LastRow (it was being used but not declared, which can cause issues with Option Explicit enabled).
• If you have Option Explicit turned on (which you should, to catch undeclared variables), make sure all variables are declared properly.

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