SQL Server stored procedure with parameters invoked from Excel event

Question

I'm using Excel 2007 and SQL Server 2008 R2 Express.

I'm trying to invoke my SQL Server stored procedure from Excel event Worksheet.Change in VBA. I have one sheet and I'm invoking the stored procedure with 3 parameters.

While invoking this stored procedure I'm getting error :

Run-time error '-2147217900 (80040e14)'
Syntax error or access violation

My Excel script code so far :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$4" Then

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    conn.ConnectionString = "Provider='SQLOLEDB'; " & _
                            "Data Source=compname\sqlservername; " & _
                            "Initial Catalog=databasename; " & _
                            "User Id= sa; " & _
                            "Password=****"


    conn.Open

    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Test1 @zmienna1= " & CStr(Range("D4").Value) & _
                            " @zmienna2= " & CStr(Range("D4").Value) & _
                            " @zmienna3= " & CStr(Range("D4").Value)



    cmd.Execute 'error
    conn.Close

    Set conn = Nothing
    Set cmd = Nothing

End If
End Sub

I'm getting error on the cmd.Execute line when I'm changing D4 cell.

What am I missing ? Do I have to use Parameters collection of the ADODB.Command object when I'm using a stored procedure with parameters?

If yes - can anyone provide code sample? Could anyone help please ? Thanks!

Andomar · Accepted Answer · 2014-02-12 09:44:46Z

1

For a stored procedure, the CommandText should be just the name of the stored procedure. Use the Command.Parameters collection to add parameters.

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@par1", adChar, adParamInput, 255, _
    CStr(Range("D4").Value)
cmd.Parameters.Append prm

answered Feb 12, 2014 at 9:44

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

SQL Server stored procedure with parameters invoked from Excel event

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