asked on

OLE DB Provider for SQL Server error '80040e10'

Hik,

I've written an SP, which should return 1 of 3 recordsets --

CREATE PROCEDURE [dbo].[SupplierSearch]
(
@search nvarchar(500)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @search = RTRIM(@search) + '%';
SELECT @search = LTRIM(@search) + '%';
IF EXISTS (SELECT ID FROM dbo.Supplier WHERE Name LIKE @search)
BEGIN
SELECT 1 DView, ID, Name FROM dbo.Supplier WHERE Name LIKE @search
END

IF EXISTS (SELECT ID FROM dbo.Brand WHERE Name LIKE @search)
BEGIN
SELECT 2 DView, B.ID, B.Name, S.ID SupplierID, S.Name Supplier FROM dbo.Brand B inner join dbo.Supplier S on B.SupplierID = S.ID WHERE B.Name LIKE @search
END

IF EXISTS (SELECT ID FROM dbo.SupplierContact WHERE FirstName+ ' '+LastName LIKE @search)
BEGIN
SELECT 3 DView, C.ID ContactID,  
C.FirstName + ' ' + C.LastName Name, 
C.JobTitle JobTitle,   
B.ID BrandID,  
B.Name Brand,  
S.ID SupplierID,  
S.Name Supplier,  
LP.Phone Landline,  
E.Email  
FROM 
dbo.SupplierContact C  
LEFT JOIN dbo.Brand B on C.BrandID = B.ID  
LEFT JOIN dbo.Supplier S on C.SupplierID = S.ID  
LEFT JOIN (SELECT ContactID, Phone FROM dbo.SupplierContactPhone WHERE [Primary] = 'Y' AND [Type] = 1) LP on LP.ContactID = C.ID  
LEFT JOIN (SELECT ContactID, Email FROM dbo.SupplierContactEmail WHERE [Primary] = 'Y') E on E.ContactID = C.ID  
WHERE FirstName+ ' '+LastName LIKE @search
END
END
GO

Open in new window

When i run it, I'm getting the following ---

Microsoft OLE DB Provider for SQL Server error '80040e10'
Procedure or function 'SupplierSearch' expects parameter '@search', which was not supplied.
/suppliers/Default.asp, line 87

The recordset should return all results fot the first select statement only if @search is null..

Please advise.

Thank you

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Here's the Vbscript --

<%

Dim CMDRSSuppliers__search
CMDRSSuppliers__search = "%"
if(Request("search") <> "") then CMDRSSuppliers__search = Request("search")

set CMDRSSuppliers = Server.CreateObject("ADODB.Command")
CMDRSSuppliers.ActiveConnection = MM_GolfConnection_STRING
CMDRSSuppliers.CommandText = "dbo.SupplierSearch"
CMDRSSuppliers.CommandType = 4
CMDRSSuppliers.CommandTimeout = 0
CMDRSSuppliers.Prepared = true
CMDRSSuppliers.Parameters.Append CMDRSSuppliers.CreateParameter("@RETURN_VALUE", 3, 4)
CMDRSSuppliers.Parameters.Append CMDRSSuppliers.CreateParameter("@search", 200, 1,500,CMDRSSuppliers__search)
set RSSuppliers = CMDRSSuppliers.Execute
RSSuppliers_numRows = 0

%>

Open in new window

Should I rewrite the SP? IF @Search = ''?

Thanks

garethtnash

ASKER

Thanks Rimvis