Stored Procedure from Excel VBA throws error "no parameters and arguments" error

Question

I have a stored procedure written in SQL Server Management studio that I need to run from a tool built in VBA in Excel. When I run the code, it returns this error

Run time error - '2147217900 (80040e14): [Microsoft][ODBC SQL Server Drive][SQL Server]Procedure uspDailyUpdateHistoricalMSL has no parameters and arguments were supplied.

Here is the stored procedure in SQL Server:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspDailyUpdateHistoricalMSL] 
AS
    DECLARE @location AS VARCHAR
    DECLARE @employeegroup AS VARCHAR
    DECLARE @contractagency AS VARCHAR
    DECLARE @position AS VARCHAR
    DECLARE @ftpt AS VARCHAR
    DECLARE @userid AS VARCHAR
    DECLARE @bilingual as bit
    DECLARE @59email AS VARCHAR
    DECLARE @email AS VARCHAR
    DECLARE @59extension AS VARCHAR
    DECLARE @cca2 as bit
    DECLARE @giid AS VARCHAR
    DECLARE @guid AS VARCHAR
    DECLARE @cimid AS VARCHAR
    DECLARE @adminvisit AS VARCHAR
    DECLARE @firstname AS VARCHAR
    DECLARE @lastname AS VARCHAR
    DECLARE @agentname AS VARCHAR
    DECLARE @doh AS VARCHAR
    DECLARE @team AS VARCHAR
    DECLARE @title AS VARCHAR
    DECLARE @weekdayschedule AS VARCHAR
    DECLARE @weekendschedule AS VARCHAR
    DECLARE @manager AS VARCHAR
    DECLARE @supervisor AS VARCHAR


    -- Create New Hire
    INSERT INTO [PIA].[dbo].[HistoricalMasterStaffing] ([Year], NewHire, Location, EmployeeGroup, ContractAgency, Position, FTPT, 
                                                        WeekNumber, CreatedBy, CreatedDate, Bilingual, Five9email, Email, Five9extension, 
                                                        Cca2, Gicode, Staffguid, Staffcimid, Visit, Adminvisit, Firstname, Lastname, 
                                                        Agentname, DOH, Team, Title, Weekdayschedule, Weekendschedule, Manager, Supervisor)
    VALUES (DATEPART(YEAR, GETDATE()), 'TRUE', @location, @employeegroup, @contractagency, @position, @ftpt, 
            DATEPART(ISO_WEEK, GETDATE()), @userid, GETDATE(), @bilingual, @59email, @email, @59extension, 
            @cca2, @giid, @guid, @cimid, 'Admin Visit', @adminvisit, @firstname, @lastname, 
            @agentname, @doh, @team, @title, @weekdayschedule, @weekendschedule, @manager, @supervisor) 
GO 

Here is my VBA code:

  Sub updateemployee()
  Dim Cn As ADODB.Connection
  Dim Server_Name As String
  Dim Database_Name As String
  Dim CnCmd As ADODB.Command
  Server_Name = "SDL02-VM25"
  Database_Name = "PIA"
  Set CnCmd = New ADODB.Command
  Set Cn = New ADODB.Connection
  Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & _
         Database_Name & vbNullString
  With CnCmd
 .ActiveConnection = Cn
 .CommandText = "dbo.uspDailyUpdateHistoricalMSL"
 .CommandType = adCmdStoredProc
 .CommandTimeout = 0
 .Parameters.Append .CreateParameter("@modby", adVarChar, adParamInput, 200, getWinUser)
 .Parameters.Append .CreateParameter("@location", adVarChar, adParamInput, 200, EditStaff.Location.value)
 .Parameters.Append .CreateParameter("@employeegroup", adVarChar, adParamInput, 200, EditStaff.EmployGroup.value)
 .Parameters.Append .CreateParameter("@position", adVarChar, adParamInput, 200, EditStaff.Position.value)
 .Parameters.Append .CreateParameter("@contractagency", adVarChar, adParamInput, 200, EditStaff.Agency.value)
 .Parameters.Append .CreateParameter("@ftpt", adVarChar, adParamInput, 200, EditStaff.FTPT.value)
 .Parameters.Append .CreateParameter("@bilingual", adBoolean, adParamInput, , EditStaff.bilingual.value)
 .Parameters.Append .CreateParameter("@59email", adVarChar, adParamInput, 200, EditStaff.five9email.value)
 .Parameters.Append .CreateParameter("@email", adVarChar, adParamInput, 200, EditStaff.Email.value)
 .Parameters.Append .CreateParameter("@59extension", adVarChar, adParamInput, 200, EditStaff.Five9Extension.value)
 .Parameters.Append .CreateParameter("@cca2", adBoolean, adParamInput, , EditStaff.CCA2.value)
 .Parameters.Append .CreateParameter("@guid", adVarChar, adParamInput, 200, EditStaff.GUID.value)
 .Parameters.Append .CreateParameter("@cimid", adVarChar, adParamInput, 200, EditStaff.CIMID.value)
 .Parameters.Append .CreateParameter("@adminvisit", adVarChar, adParamInput, 200, EditStaff.adminvisit.value)
 .Parameters.Append .CreateParameter("@firstname", adVarChar, adParamInput, 200, EditStaff.firstname.value)
 .Parameters.Append .CreateParameter("@lastname", adVarChar, adParamInput, 200, EditStaff.lastname.value)
 .Parameters.Append .CreateParameter("@agentname", adVarChar, adParamInput, 200, "EditStaff.firstname.value & Space(2) &            EditStaff.lastname.value")
 .Parameters.Append .CreateParameter("@doh", adVarChar, adParamInput, 200, EditStaff.DOH.value)
 .Parameters.Append .CreateParameter("@team", adVarChar, adParamInput, 200, EditStaff.Team.value)
 .Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 200, EditStaff.Title.value)
 .Parameters.Append .CreateParameter("@weekdayschedule", adVarChar, adParamInput, 200, EditStaff.weekdayschedule)
 .Parameters.Append .CreateParameter("@weekendschedule", adVarChar, adParamInput, 200, EditStaff.weekendschedule.value)
 .Parameters.Append .CreateParameter("@manager", adVarChar, adParamInput, 200, EditStaff.Manager.value)
 .Parameters.Append .CreateParameter("@supervisor", adVarChar, adParamInput, 200, EditStaff.supervisor.value)
 .Execute

  End With
  Application.StatusBar = "Updating Employee Information..."

  End Sub

Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. If you declare a variable AS VARCHAR - then you get a variable than can hold exactly ONE character - which is typically NOT what you want.... — marc_s
– marc_s, Commented Jun 22, 2018 at 18:33
You also are not handling your connection or command objects correctly. The easiest solution to that is wrapping them both in a USING statement. As posted your connection object is not closed or disposed and will consume a new connection from the pool each time this runs until the pool is consumed and you get errors that it can't get a connection from the pool. — Sean Lange
– Sean Lange, Commented Jun 22, 2018 at 18:39

Tab Alleman · Accepted Answer · 2018-06-22 17:39:33Z

3

You didn't create the parameters in your stored procedure correctly.

Instead of this:

CREATE PROCEDURE [dbo].[uspDailyUpdateHistoricalMSL] AS
DECLARE @location as varchar
DECLARE @employeegroup as varchar
...

You want this:

CREATE PROCEDURE [dbo].[uspDailyUpdateHistoricalMSL] 
 @location varchar,
 @employeegroup  varchar,
...
AS

Also you need to supply length values for your varchar parameters, though that isn't the reason for this particular error.

 @location varchar(31),  --or whatever the length of the location column is.

answered Jun 22, 2018 at 17:39

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

2 Comments

Jb83 Over a year ago

So the length needs to be declared in both SQL and in VBA? I'm assuming the two values need to match?

Tab Alleman Over a year ago

It's best if they match, but the only thing absolutely necessary is that you never try to pass a value to the parameter that is longer than the Length property of the parameter.

Collectives™ on Stack Overflow

Stored Procedure from Excel VBA throws error "no parameters and arguments" error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related