sql - Excel VBA 中的存储过程引发错误“无参数和参数”错误
问题描述
我有一个用 SQL Server Management Studio 编写的存储过程,我需要从 Excel 中的 VBA 内置工具运行它。当我运行代码时,它会返回此错误
运行时错误 - '2147217900 (80040e14): [Microsoft][ODBC SQL Server Drive][SQL Server]Procedure uspDailyUpdateHistoricalMSL 没有提供参数和参数。
下面是 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
解决方案
您没有在存储过程中正确创建参数。
而不是这个:
CREATE PROCEDURE [dbo].[uspDailyUpdateHistoricalMSL] AS
DECLARE @location as varchar
DECLARE @employeegroup as varchar
...
你要这个:
CREATE PROCEDURE [dbo].[uspDailyUpdateHistoricalMSL]
@location varchar,
@employeegroup varchar,
...
AS
您还需要为您的 varchar 参数提供长度值,尽管这不是此特定错误的原因。
@location varchar(31), --or whatever the length of the location column is.
推荐阅读
- mysql - 使用 SQL 查找每个索引的平均值
- php - 图像不替换为变体图像
- python - ModuleNotFoundError:没有名为“geoplot”的模块
- javascript - TypeError:无法读取机器人问题未定义头像的属性“url”
- swift - 无法使用 Mac Catalyst 获取共享菜单以处理 PDF 数据
- angular - Angular & NgRX - 从效果文件向组件发送反馈
- xslt - 将多个 xml 元素转换为单个复杂的 xml 元素
- sql - change the data format from individual rows over many columns to multiple rows over one column with a data id column
- windows - SVN: How to list author, date and comments from svn log
- mysql - 如何让两个表引用数据库中的同一个对象?