StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm not sure you should be enforcing the 'at least two conditions' criteria down in the database as you should probably never have knowledge of which two have been filled in. Perhaps instead this might work for you - it's a pattern I use quite often and should cope with any combination of criteria (I'm assuming this is inside a stored proc!): </p> <pre><code>DECLARE PROCEDURE PropertyList @StreetName NVARCHAR(50) = NULL, @Town NVARCHAR(50) = NULL, @Postcode NVARCHAR(10) = NULL AS SET NOCOUNT ON SELECT * FROM VWTenantPropertiesResults WHERE ContentBedrooms BETWEEN 1 AND 4 AND ContentPrice BETWEEN 50 AND 500 AND (@ContentStreet IS NULL OR ContentStreet = @ContentStreet) AND (@ContentTown IS NULL OR ContentTown = @ContentTown) AND (@ContentPostcode IS NULL OR ContentTown = @ContentTown) ORDER BY ContentPrice </code></pre> <p>To call this from your ASP page you'll want some code something like this (this <em>may</em> need a bit of debugging, my ADO & VBScript for ASP is pretty rusty!): </p> <pre><code>Dim cnn 'As ADODB.Connection Dim cmd 'As ADODB.Command Dim prmStreet 'As ADODB.Parameter Dim prmTown 'As ADODB.Parameter Dim prmPostcode 'As ADODB.Parameter Dim rstProperty 'As ADODB.RecordSet Dim i 'As Integer Set cnn = Server.CreateObject("ADODB.Connection") cnn.ConnectionString = MyConnectionString Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cnn 'Set the CommandText property to the name of the stored proc we want to call cmd.CommandText = "PropertyList" cmd.CommandType = 4 'or adCmdStoredProc if you're using ADOVBS.inc If Request.Form("StreetTextBox") = "" Then 'No street entered so don't pass it to the stored proc Else 'A street has been entered so create a parameter... Set prmStreet = cmd.CreateParameter("@StreetName", 203, 1, 50, Request.Form("StreetTextBox")) ' and add it to the Parameters collection of the Command object cmd.Parameters.Add(prmStreet) End If If Request.Form("TownTextBox") = "" Then 'No town entered so don't pass it to the stored proc Else 'A town has been entered so create a parameter... Set prmTown = cmd.CreateParameter("@Town", 203, 1, 50, Request.Form("TownTextBox")) ' and add it to the Parameters collection of the Command object cmd.Parameters.Add(prmTown) End If If Request.Form("PostcodeTextBox") = "" Then 'No postcode entered so don't pass it to the stored proc Else 'A postcode has been entered so create a parameter... Set prmPostcode = cmd.CreateParameter("@Postcode", 203, 1, 10, Request.Form("PostcodeTextBox")) ' and add it to the Parameters collection of the Command object cmd.Parameters.Add(prmPostcode) End If cnn.Open 'This is the line that'll actually call the stored procedure Set rstProperty = cmd.Execute() cnn.Close If rstProperty.BOF And rstProperty.EOF Then 'If BOF And EOF are true then this is an empty recordset - we got no records back Response.Write "No records returned" Else 'We have records so write them out into a table Response.Write "<table><tr>" For i = 0 To rstProperty.Fields.Count - 1 Response.Write "<td>" Response.Write rstProperty.Fields(i).Name Response.Write "</td>" Response.Write "<td>&nbsp;</td>" Next Response.Write "</tr>" Do While rstProperty.Eof = False Response.Write "<tr>" For i = 0 To rstProperty.Fields.Count - 1 Response.Write "<td>" Response.Write rstProperty.Fields(i).Value Response.Write "</td>" Next Response.Write "<td>" Response.Write "<a href='ViewDetails.asp?id='" & rstProperty.Fields("PropertyId").Value & "'>View Details for this property</a>" Response.Write "</td>" Response.Write "</tr>" rstProperty.MoveNext Loop Response.Write "</table>" End If </code></pre> <p>This <em>ought</em> to work for any combination of parameters, whether you enter none, some or all of them!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload