advance search with stored procedure
Hello, can somebody please help me here. I cannot figure it out or find any information on how to create an advanced search. I have a form with the 3 options of searching for a PrintBuyer, ProjectNumber, or ProjectTitle. I am getting the following error when I do not select the option of PrintBuyer.
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
searchresults.asp, line 51
If I understand what is happening here is that the the SP is grabbing the ProjectNumber instead, how can I resolve this? If I do test the strPrintBuyer with an IF statement I get, basically the same error.
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to int.
searchresults.asp, line 57
Now if I do select a PrintBuyer it will pull up all of my records, but if I add something to the ProjectNumber or ProjectTitle, it will not find any information.
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = strConnect
.CommandText = "sp_SelectProject"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@strPrintBuyerID", adInteger, adParamInput, , strPrintBuyer)
.Parameters.Append .CreateParameter("@strProjNo", adVarChar, adParamInput, 50, strProjNo)
.Parameters.Append .CreateParameter("@strProjTitle", adVarChar, adParamInput, 50, strProjTitle)
.Parameters.Append .CreateParameter("@strOrderBy", adVarChar, adParamInput, 50, strOrderBy)
Set objSelect = .Execute
End With
Set objCmd = Nothing
CREATE PROCEDURE sp_SelectProject
(
@strPrintBuyerID int,
@strProjNo varchar (50),
@strProjTitle varchar (50),
@strOrderBy varchar (50)
)
AS
SELECT ProjID, PrintBuyerID, ProjDate, ProjNo, ProjTitle, ProjOtherInfo
FROM Projects
WHERE PrintBuyerID = @strPrintBuyerID
AND ProjNo LIKE @strProjNo%
AND ProjTitle LIKE @strProjTitle%
ORDER BY
CASE WHEN @strOrderBy='ProjDate' THEN Proj_Date
WHEN @strOrderBy='ProjNo' THEN Proj_No
WHEN @strOrderBy='ProjTitle' THEN Proj_Title
END
Many Thanks
Mike Moore
__________________
Peace
Mike
http://www.eclecticpixel.com
|
