vba 使用传递参数访问调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26176938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Call Stored Procedure with Pass Through Parameter
提问by Michael
I'm using a Access 2010 front-end linked to a SQL Server 2012 database back-end.
我正在使用链接到 SQL Server 2012 数据库后端的 Access 2010 前端。
In the Access frontend I have a general search screen, consisting of a text box whereby the results are displayed in a listbox. I want the text box to be able to search multiple fields that will also need to be wild cards.
在 Access 前端，我有一个通用搜索屏幕，由一个文本框组成，结果显示在列表框中。我希望文本框能够搜索多个字段，这些字段也需要是通配符。
So, my stored procedure is this:
所以，我的存储过程是这样的：
ALTER PROCEDURE [dbo].[SalesGeneralSearch]
@Search nvarchar(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT
tblJobHead.JobNum,
tblCustomer.LastName,
tblCustomer.M3DealerCode,
tblCustomer.TradeRef,
tblCustomer.Postcode,
tblJobHead.[Item Number],
tblJobHead.Description,
tblStatus.[Desc] AS Status
FROM
tblCustomer
INNER JOIN
(tblJobHead
INNER JOIN
tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
WHERE
(((tblJobHead.JobNum) Like '%'+ @Search + '%'))
OR (((tblCustomer.LastName) Like '%' + @Search + '%'))
OR (((tblCustomer.M3DealerCode) Like '%' + @Search + '%'))
OR (((tblCustomer.TradeRef) Like '%' + @Search + '%'))
OR (((tblCustomer.Postcode) Like '%' + @Search + '%'))
ORDER BY
tblJobHead.JobNum DESC;
END
Ok, so now back to Access. On the search screen, I have textbox where the user inputs, a button to click to search and a listbox for the results. On the click event of the button I have the following code:
好的，现在回到 Access。在搜索屏幕上，我有用户输入的文本框、一个用于单击搜索的按钮和一个用于结果的列表框。在按钮的点击事件上，我有以下代码：
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim adString As Variant
Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER=SQL Server;SERVER=XXX;Database=XXX;Trusted_Connection=YES;"
cn.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "dbo.SalesGeneralSearch"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("@Search", adString, adParamInput)
.Parameters.Append prm
cmd.Execute
prm.Value = Me.Search.Text
End With
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open cmd
End With
Set Me!lstJobQuickSearch.Recordset = rs
Me.lstJobQuickSearch.Requery
Set prm = Nothing
Set cmd = Nothing
However, when I click on the search button I keep receiving the below error:
但是，当我单击搜索按钮时，我不断收到以下错误：
runtime error 3708 parameter object improperly defined
运行时错误 3708 参数对象定义不正确
and it highlights
它突出显示
.Parameters.Append prm
The row source of the lstJobQuickSearch is a pass through query as well using the following:
lstJobQuickSearch 的行源也是使用以下内容的传递查询：
SELECT tblJobHead.JobNum, tblCustomer.LastName, tblCustomer.M3DealerCode, tblCustomer.TradeRef, tblCustomer.Postcode, tblJobHead.[Item Number], tblJobHead.Description, tblJobHead.FN, tblStatus.[Desc] AS Status
FROM tblCustomer INNER JOIN (tblJobHead INNER JOIN tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
ORDER BY tblJobHead.JobNum DESC;
Where have I gone wrong? Any help would be greatly appreciated.
我哪里错了？任何帮助将不胜感激。
As I just want to return the values, is it more efficient just to use a pass through query and code the parameters? I'm not sure, I've been working on this for days : -(
由于我只想返回值，因此仅使用传递查询并对参数进行编码是否更有效？我不确定，我已经为此工作了好几天:-(
Regards,
问候，
Michael
迈克尔
采纳答案by Gord Thompson
The issue is that the stored procedure declares an input parameter as nvarchar(50)but in VBA the ADODB.Parameterwas being defined using adStringand no length. Problems:
问题是存储过程声明了一个输入参数，nvarchar(50)但是在 VBA 中，ADODB.Parameter它被定义为使用adString而没有长度。问题：
nvarcharon SQL Server maps toadVarWCharin ADO- string parameters almost always need to have a defined (maximum) length
nvarchar在 SQL Server 上映射到adVarWChar在 ADO- 字符串参数几乎总是需要有一个定义的（最大）长度
So the fix is to change the parameter declaration to
所以解决方法是将参数声明更改为
Set prm = .CreateParameter("@Search", adVarWChar, adParamInput, 50)
回答by Albert D. Kallal
I would use a DAO pass-though. Assuming you saved a pass-though query, then this code will work:
我会使用 DAO 传递。假设您保存了一个传递查询，那么此代码将起作用：
With CurrentDb.QueryDefs("qPass")
.SQL = "exec SalesGeneralSearch " & strSearch
Set Me.MyListBox.RowSource = .OpenRecordset
End If
There really no need for these massive whacks of code posted which only serves as theft of company billable hours by dishonest developers when a simple 2 lines as per above will suffice.
真的没有必要发布这些大量代码，这些代码只会被不诚实的开发人员窃取公司计费时间，而上面的简单 2 行就足够了。
