asp call stored procedure with optional parameters 2

[lorca]

Hello

I have a stored procedure below, which i call from an asp.
all parameters are optional, so they may only fill out the varMake asp field for example.
I do have this working for a simpler example for one parameter that is = as opposed to like, but this returns nothing !

any help appreciated
tony

asp function function.asp

function checkAbanVehicleslucky(carregno1, varmake, varcolour, varlocation)

on error resume next

if len(request("txtCarReg")) = 0 then
carregno1 = "'%%'"
else
carregno1 = request("txtCarReg")
carregno1 = replace(carregno1," " ,"")
carregno1 = "'%" & carregno1 & "%'"
end if

if len(request("txtCarMake")) =0 then
varmake = "'%%'"
else
varmake = "'%" & request("txtCarMake") & "%'"
end if

if len(request("txtCarColour"))= 0 then
varcolour = "'%%'"
else
varcolour = "'%" & request("txtCarColour") & "%'"
end if

if len(request("txtlocation")) =0 then
varlocation = "'%%'"
else
varlocation = "'%" & request("txtlocation") & "%'"
end if

dim conn1, cmd1, rec1

set conn1 = server.createObject("ADODB.Connection")
conn1.open(application("CONN_I"))
set cmd1 = server.createObject("ADODB.Command")
Set cmd1.ActiveConnection = conn1

cmd1.CommandText = "usp_GetAbanVehicles_lucky"
cmd1.CommandType = adCmdStoredProc
cmd1.NamedParameters = true

cmd1.parameters.append cmd1.createparameter("@RegNo", adchar, adparaminput, 10, left(carregno1,10))
cmd1.parameters.append cmd1.createparameter("@Make", advarchar, adparaminput, 50, left(varmake,50))
cmd1.parameters.append cmd1.createparameter("@Colour", advarchar, adparaminput, 50, left(varcolour,50))
cmd1.parameters.append cmd1.createparameter("@Location", advarchar, adparaminput, 250, left(varlocation,250))

set checkAbanVehicleslucky = cmd1.execute
end function

and then in the main page i call it like

<!-- #include file="function.asp" -->

response.write("<tr><td align=""right""></td><td>")
showPreviousAbanVehicleslucky
response.write("</td></tr>") &vbcrlf

sub showPreviousAbanVehicleslucky

dim rec1, carregno1, varmake, varcolour, varlocation

if len(request("txtCarReg")) = 0 then
carregno1 = "'%%'"
else
carregno1 = request("txtCarReg")
carregno1 = replace(carregno1," " ,"")
carregno1 = "'%" & carregno1 & "%'"
end if

if len(request("txtCarMake")) =0 then
varmake = "'%%'"
else
varmake = "'%" & request("txtCarMake") & "%'"
end if

if len(request("txtCarColour"))= 0 then
varcolour = "'%%'"
else
varcolour = "'%" & request("txtCarColour") & "%'"
end if

if len(request("txtlocation")) =0 then
varlocation = "'%%'"
else
varlocation = "'%" & request("txtlocation") & "%'"
end if

set rec1 = checkAbanVehicleslucky(carregno1, varmake, varcolour, varlocation)

response.write("<p>Search results from ECS system:")
if(rec1.bof)then
response.write("  Nothing found.</p>")
else
response.write("<table border=""1"" style=""font-size:x-small;"" width=""80%"">")
response.write("<col width=""5%"" align=""center"" /><col width=""10%"" align=""center"" /><col width=""30%"" 
align=""center"" /><col width=""5%"" align=""center"" /><col width=""10%"" align=""center"" /><col width=""10%"" 
align=""center"" /><col width=""10%"" align=""center"" />")
response.write("<tr><thead style=""background:#bbb;""><th>Call ID</th><th>Sent 
Date</th><th>Location</th><th>ECS Ref.</th><th>Sent to Supervisor</th><th>Notice 
Served</th><th>Surrendered</th></thead></tr>")

do while not rec1.eof
response.write("<tr>")
response.write("<td align=""center"">" & server.htmlencode(rec1("CallID")&" ") & "</td>")
response.write("<td align=""center"">" & server.htmlencode(rec1("CRMSentDate")&" ") & "</td>")
response.write("<td align=""center"">" & server.htmlencode(rec1("location")&" ") & "</td>")

if isnull(rec1("refno")) then
response.write("<td align=""center"">N/A</td>")
else
response.write("<td align=""center"">" & server.htmlencode(rec1("refno")&" ") & "</td>")
end if

if isnull(rec1("dtToSupervisor")) then
response.write("<td align=""center"">N/A</td>")
else
response.write("<td align=""center"">" & server.htmlencode(rec1("dtToSupervisor")&" ") & "</td>")
end if

if isnull(rec1("dtNoticeServed")) then
response.write("<td align=""center"">N/A</td>")
else
response.write("<td align=""center"">" & server.htmlencode(rec1("dtNoticeServed")&" ") & "</td>")
end if

Select case rec1("surrendered")
case false			
response.write("<td align=""center"">No</td>")
case true
response.write("<td align=""center"">Yes</td>")
case else
response.write("<td align=""center"">Unknown</td>")
end select
response.write("</tr>")
rec1.movenext
loop
response.write("</table>")
end if

rec1.close
set rec1 = nothing
end sub

sp usp_GetAbanVehicles_lucky

CREATE PROCEDURE [dbo].[usp_GetAbanVehicles_lucky]

/* ALL parameters are optional */
@RegNo char(10),
@Make varchar(50),
@Colour varchar(50),
@Location varchar(250)
as
select tblAbandonVeh_ISB.CallID, registrationNo, VarMake, VarModel, VarColour, location, refno, dtToSupervisor, dtNoticeServed,dtCheck, tblComplaint_ISB.CRMSentDate, surrendered from dbo.tblAbandonVeh_ISB
INNER JOIN dbo.tblComplaint_ISB on tblComplaint_ISB.CallID = tblAbandonVeh_ISB.CallID
where registrationNo like @RegNo and varMake like @Make and varColour like @Colour and location like @Location
order by tblComplaint_ISB.CRMSentDate desc
GO

 

[onpnt]

dumb question but why are you not simply doing an Execute?

Dim sql : sql = "usp_GetAbanVehicles_lucky @RegNo ='" & & "', " _
					   @Make ='" & & "', " _
					   @Colour ='" & & "', " _
					   @Location ='" & & "'"
'Uncomment to Test
'Response.Write sql

set checkAbanVehicleslucky = conn1.Execute(sql)

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

or did not get the question correct? there's far too much code to read up there. strip it down and try to be more detail on the issue



General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

sorry I think I get it now.

if you want your parameters optional in your SP then you need to set them to a value in the SP.

something like

@RegNo char(10) = NULL,
@Make varchar(50) = NULL,
@Colour varchar(50) = NULL,
@Location varchar(250) = NULL

your query willnot work then though. you'll need to use brackets to make each LIKE clause a seperate check or you'll never return anything sense you'll return False unless all values are met

search google for stored procedures with optional parameters. lots of examples on this method

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[lorca]

thanks

i did originally have my sp, using optional parameters as below, still no joy, does this look about right ?
I will search google..........

thanks again

/* ALL parameters are optional */
@RegNo char(10) = null,
@Make varchar(50) = null,
@Colour varchar(50) = null,
@Location varchar(250) = null
as

IF @RegNo IS NULL
    BEGIN
    	SET @RegNo = '%'
END
ELSE

    BEGIN
    	SET @RegNo = @RegNo + '%'
END

IF @Make IS NULL
    BEGIN
    	SET @Make = '%'
END
ELSE

    BEGIN
    	SET @Make = @Make + '%'
END

IF @Colour IS NULL
    BEGIN
    	SET @Colour = '%'
END
ELSE

    BEGIN
    	SET @Colour = @Colour + '%'
END

IF @Location IS NULL
    BEGIN
    	SET @Location = '%'
END
ELSE

    BEGIN
    	SET @Location = @Location + '%'
END

 

[Sheco]

This isn't exactly relevant but it is a nice tip: You can use the .Refresh method of the ADO Command object's Parameters collection to get the exact parameters automatically so you dont have to append them.

In your example above you could replace this:[tt]
cmd1.parameters.append cmd1.createparameter("@RegNo", adchar, adparaminput, 10, left(carregno1,10))
cmd1.parameters.append cmd1.createparameter("@Make", advarchar, adparaminput, 50, left(varmake,50))
cmd1.parameters.append cmd1.createparameter("@Colour", advarchar, adparaminput, 50, left(varcolour,50))
cmd1.parameters.append cmd1.createparameter("@Location", advarchar, adparaminput, 250, left(varlocation,250))
[/tt]

With this:[tt]
cmd1.Parameters.Refresh[/tt]

 

[gmmastros]

Try this for your stored procedure instead.

CREATE PROCEDURE [dbo].[usp_GetAbanVehicles_lucky]

/* ALL parameters are optional */
@RegNo char(10) = NULL,
@Make varchar(50) = NULL,
@Colour varchar(50) = NULL,
@Location varchar(250) = NULL
as
select tblAbandonVeh_ISB.CallID, 
       registrationNo, 
       VarMake, 
       VarModel, 
       VarColour, 
       location, 
       refno, 
       dtToSupervisor, 
       dtNoticeServed,
       dtCheck, 
       tblComplaint_ISB.CRMSentDate, 
       surrendered 
from   dbo.tblAbandonVeh_ISB
       INNER JOIN dbo.tblComplaint_ISB 
         on tblComplaint_ISB.CallID = tblAbandonVeh_ISB.CallID
where  (IsNull(@RegNo, '') = '' or registrationNo like @RegNo + '%')
       and (IsNull(@Make, '') = '' or varMake like @Make + '%')
       and (IsNull(@Colour, '') = '' or varColour like @Colour + '%')
       and (IsNull(@location, '') = '' or location like @Location + '%')
order by tblComplaint_ISB.CRMSentDate desc
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Oh, here is another random tip for SQL Server stored procedures that return a resultset...

If you put the line:
[tt]SET NOCOUNT ON[/tt]
immediately after the [tt]As[/tt] it can save you some phantom problems with ADO recordset objects...

Sometimes the recordset gets confused by the (0 rows affected) output from SQL Server and this will prevent the problem.

 

[lorca]

thanks to everyone - it is now working ok, i relaised the char field was causing problems because it was padding out with spaces, so i changed it in the SP and parameters append to varchar.

thank you
tony