Excel VBA Set sql request with parameter

Question

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?

CDP1802 · Accepted Answer · 2022-09-20 10:25:26Z

1

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

answered Sep 20, 2022 at 10:25

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Set sql request with parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related