Accessing a Call Stored Procedure Using a Pass Through Parameter
I am using front-end Access 2010 connected to a SQL Server 2012 database database.
In the access interface, I have a general search screen consisting of a text box that displays the results in a list. I want the textbox to be able to search for multiple fields that should be empty as well.
So my stored procedure is like 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, now back to Access. On the search screen, I have a text box that includes a user, a button for search, and a list for results. In the button click event, I have the following code:
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 get the following error:
runtime error 3708 invalid object parameter
and highlights
.Parameters.Append prm
The lstJobQuickSearch string is also sourced through the query using the following:
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 did I go wrong? Any help would be greatly appreciated.
How do I just want to return values, is it more efficient to use pass through the request and encode the parameters? I'm not sure, I've been working on this for a few days: --(
Hello,
Michael
source to share
The problem is that the stored procedure declares an input parameter as nvarchar(50)
, but in VBA it was ADODB.Parameter
defined with adString
and without length. Problems:
-
nvarchar
on SQL Server maps onadVarWChar
ADO - String parameters should almost always have a specific (maximum) length
So the fix is to change the parameter declaration to
Set prm = .CreateParameter("@Search", adVarWChar, adParamInput, 50)
source to share
I would use the DAO skip though. Assuming you kept the pass-but request, then this code will work:
With CurrentDb.QueryDefs("qPass")
.SQL = "exec SalesGeneralSearch " & strSearch
Set Me.MyListBox.RowSource = .OpenRecordset
End If
There is really no need for these massive bumps of code to only be used as stealing company-paid watches by dishonest developers when a simple two lines of code as above will suffice.
source to share