Excel VBA Set sql request with parameter

Jasur1177 0
Viewed 48

How to set sql request with parameter?

Code:

Dim dbPath As String
dbPath = "C:\Users\User\Desktop\База.xlsx"
    
Dim ConnectionString As String
ConnectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & dbPath & ";HDR=Yes';"

Conn.Open ConnectionString
Cmd.ActiveConnection = Conn

Cmd.CommandText = "UPDATE [База$] SET Значение=@param WHERE Ид=" & Txt_Id.Text & ""
Cmd.Parameters.Append Cmd.CreateParameter("@param", adVarChar, adParamInput, 200, Txt_Name.Text)
Cmd.Execute

Error while executing code "Too few parameters. Requires 1." How to set parameters correctly?

vba excel
1 Answers
Sub demo()

    Dim dbPath As String, n As Long
    dbPath = "C:\Users\User\Desktop\????.xlsx"
        
    Dim Conn As ADODB.Connection, Cmd As ADODB.Command, ConnectionString As String
    ConnectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & dbPath & ";HDR=Yes';"
    
    Set Conn = New Connection
    Conn.Open ConnectionString
    
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = Conn
        .CommandText = "UPDATE [MYTABLE$] SET [COL_A] = ? WHERE [COL_B]= ?"
        .Parameters.Append Cmd.CreateParameter("p1", adVarChar, adParamInput, 200, "XYZ")
        .Parameters.Append Cmd.CreateParameter("p2", adVarChar, adParamInput, 200, "B1")
        .Execute n
    End With
    MsgBox n & " records updated", vbInformation
    
End Sub
CDP18020
Related