asked on

Passing wild card variable in querystring

I have a page that has a dynamically populated pulldown menu with an option to select all. This works just fine. See code:

<form action="nc_reports.asp">
		<!-- add class="tcal" to your input field -->
        &nbsp;
		<div>Starting Date: <input type="text" name="SelectedDate1" class="tcal" value="" /></div>
      <div>Ending Date: <input type="text" name="SelectedDate2" class="tcal" value="" /></div>
        <div>Origin Reference: 
          <select name="reference" id="reference">
          <option value="%" selected="SELECTED">All</option>
		  <%Do until rs2.EOF %>
            <option><%Response.Write rs2("Origin_ref")%></option>
            <%rs2.MoveNext()
			Loop%>
            <%rs2.Close()
			Set rs2 = Nothing%>
          </select>
        </div>
  
        <input type="submit" name="submit" id="submit" value="Submit">
	</form>

Open in new window

I then have a link on the same page that links to a cleaner printable version of the same page. The link looks like this:

<a href = "http://10.10.0.2/portal/quality/nc_reports_printable.asp?
SelectedDate1=<%response.write Request.querystring("SelectedDate1")%>&
SelectedDate2=<%response.write Request.querystring("SelectedDate2")%>&
reference=<%response.write Request.querystring("reference")%>" 
target="_blank">Printable Page</a>

Open in new window

The printable page works if I choose an option other than "All". The link does not seem to pass the "%".

Any ideas?

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

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

Start Free Trial

Frank Helk

Merely an idea .... your problemis the link composition.

In HTML URLs special characters need a transfer encoding, which is done by translating i.e.

two words

into

two%20words

(the blank is replaced with the escape character % followed by the hex code of the blank (decimal 32 = hex 20). The web server translates that back for the server side application. I presume that the % caracter itself needs to be escaped, too, with %25.

You might check that by copying the link to the page from the displayed page and inspect it (maybe you could post it ?).

Scott Fell

I think what frank is referring to is urlencode http://www.w3schools.com/asp/met_urlencode.asp

<a href = "http://10.10.0.2/portal/quality/nc_reports_printable.asp?
SelectedDate1=<%response.write Request.querystring("SelectedDate1")%>&
SelectedDate2=<%response.write Request.querystring("SelectedDate2")%>&
reference=<%response.write Request.querystring("reference")%>" 
target="_blank">Printable Page</a>

Open in new window

<%
theLink = "http://10.10.0.2/portal/quality/nc_reports_printable.asp?
SelectedDate1="&response.write Request.querystring("SelectedDate1")&"&
SelectedDate2="&response.write Request.querystring("SelectedDate2")&"&
reference="&response.write Request.querystring("reference")

theLink = urlEncode(theLink)
%>

<a href = "<%=theLink%>" 
target="_blank">Printable Page</a>

Open in new window

Frank Helk

@Scott: Thanks for pointing out the easy way to fix it ... that part collected some dust in my memory, so I remembered the hard way. ;-)

Robert Francis

ASKER

Scott Fell,

I think I did what you asked correctly but it is still not working. It wont pass the % symbol. Also the request.form would not work. Still had to use request.querystring. Here is the code from the original page:

<%if request.querystring("reference") <> "All" then
   reference = request.querystring("reference")
   else reference = "%"
end if%>


<% mSQL1 = "SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference," &_
" PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType" &_
" FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID" &_
" WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '" & Request.querystring("SelectedDate1") & "', 102)" &_
" AND CONVERT(DATETIME, '" & Request.querystring("SelectedDate2") & "', 102)) AND (PT_NC.Origin_ref LIKE '" & reference & "')"

Open in new window

Here is the code from the printable page:

<%if request.querystring("reference") <> "All" then
   reference = request.querystring("reference")
   else reference = "%"
end if%>


<% mSQL1 = "SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference," &_
" PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType" &_
" FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID" &_
" WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '" & Request.querystring("SelectedDate1") & "', 102)" &_
" AND CONVERT(DATETIME, '" & Request.querystring("SelectedDate2") & "', 102)) AND (PT_NC.Origin_ref LIKE '" & reference & "')"

Set rs1 = connectstr.Execute(mSQL1)%>

Open in new window

Scott Fell

Sorry, my bad on request.form vs requesty.querystring.

On the printable page try this

<% mSQL1 = "SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference," &_
" PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType" &_
" FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID" &_
" WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '" & Request.querystring("SelectedDate1") & "', 102)" &_
" AND CONVERT(DATETIME, '" & Request.querystring("SelectedDate2") & "', 102)) AND (PT_NC.Origin_ref LIKE '" & reference & "')"

' --- for testing only --
response.write mSQL1
response.end
' ----------------------------

Set rs1 = connectstr.Execute(mSQL1)%>

Open in new window

That will show us what sql you are using. Try placing that into manager studio as a view and see what you get or into access if that is what you are using.

Also, I hope this is just for test and not production. If not, you should be using a parameterized query because this is open to sql injection.

Robert Francis

ASKER

I have been doing the response.write for testing and here is the result for you:

SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference, PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '02/17/2015', 102) AND CONVERT(DATETIME, '03/04/2015', 102)) AND (PT_NC.Origin_ref LIKE '')

The link that takes you to this page is:

<a href = "http://10.10.0.2/portal/quality/nc_reports_printable.asp?SelectedDate1=<%response.write Request.querystring("SelectedDate1")%>&SelectedDate2=<%response.write Request.querystring("SelectedDate2")%>&reference=<%response.write reference%>" target="_blank">Printable Page</a>

Open in new window

I went ahead and did a test on that page to make sure it was sending the correct information. See below:

<p><%response.write Request.querystring("SelectedDate1")%></p>
<p><%response.write Request.querystring("SelectedDate2")%></p>
<p><%response.write reference%></p>

Open in new window

The results were:

02/17/2015

03/04/2015

%

Not sure if it matters or not and you can let me know but this page is only internal to the company. Also the sql user account for the intranet pages only has read only capability on the tables. Am I being stupid in thinking this is enough.

Scott Fell

Looking at your rendered sql statement, the variable, "reference" is not being populated.

Make sure that variable is being populated. Is the spelling is consistent?

You may want to start your page off with setting the variable.

dim reference
reference = "%"

Open in new window

Then at some point below this add your code to catch the querystring and redefine reference by making it equal something else.

Robert Francis

ASKER

I would say that it is getting rendered because when I choose a specific reference and not "All" it works and is carried over. It simple will not carry over the % symbol. see example below where I choose "Drafting" instead of "All":

SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference, PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '03/02/2015', 102) AND CONVERT(DATETIME, '03/04/2015', 102)) AND (PT_NC.Origin_ref LIKE 'DRAFTING')

Open in new window

Scott Fell

Take a look at this simple example and see if this helps.
http://mypadas.com/ee/princeservice

<%

' -- 1: define  variable and default it to "%"
dim reference
reference = "%"

' -- 2: if form submitted, redefine the variable reference to the querystring named reference
if request.querystring("reference")<> "all" then
	reference = request.querystring("reference")
end if

%>
<!DOCTYPE html>
<html>
<body>
<h2>Select All Test</h2>
<%
' -- 3: if form submitted show the value of the variable reference
if request.querystring<>"" then
	response.write "You selected reference = "&reference
end if
%>

<!-- form action to current page -->
<form method="get" action="">
<select name="reference">
       <!-- option value set to all -->
	<option value="all">All</option>
	<option value="red">red</option>
	<option value="green">green</option>
	<option value="blue">blue</option>
</select>
<button type="submit">Submit</button>
</form>
</body>
</html>

Open in new window

Scott Fell

Notice in the above, if you choose all, http://mypadas.com/ee/princeservice/?reference=all then the value of the variable reference is "%"

Scott Fell

I think I see an issue. The two lines below are different. "all" vs "All". Try using all lower case for the value AND your test if exists.

request.querystring("reference") <> "all" 
request.querystring("reference") <> "All"

Open in new window

http://mypadas.com/ee/princeservice/sql.asp

<%
dim reference
'reference = "%"

if request.querystring("reference") <> "all" then
   reference = request.querystring("reference")
   else 
   reference = "%"
end if


mSQL1 = "SELECT PT_NC.NCR, PT_NC.NCR_Date, PT_NC.Origin, PT_NC.Origin_ref, PT_NC.Origin_cause, PT_NC.Reference," &_
" PT_NC.Qty_total, PT_NC.Qty_rejected, PT_NC.Job, PT_NC.Material, PT_NC.NC_type, PT_NC.Disposition, PT_Task.Notes, PT_Task.TaskSubType" &_
" FROM PT_NC INNER JOIN PT_Task ON PT_NC.NCR = PT_Task.TaskTypeID" &_
" WHERE (PT_Task.TaskSubType = 'Origination') AND (PT_NC.NCR_Date BETWEEN CONVERT(DATETIME, '" & Request.querystring("SelectedDate1") & "', 102)" &_
" AND CONVERT(DATETIME, '" & Request.querystring("SelectedDate2") & "', 102)) AND (PT_NC.Origin_ref LIKE '" & reference & "')"


%>
<!DOCTYPE html>
<html>
<body>
<h2>Select All Test</h2>
<%
if request.querystring<>"" then
	response.write "reference = "&reference&"<hr>"&mSQL1
end if
%>
<form method="get" action="">
<select name="reference">
	<option value="all">All</option>
	<option value="red">red</option>
	<option value="green">green</option>
	<option value="blue">blue</option>
	<input type="hidden" name="SelectedDate1" value = "1/1/2015">
	<input type="hidden" name="SelectedDate2" value = "2/1/2015">
</select>
<button type="submit">Submit</button>
</form>
</body>
</html>

Open in new window

Scott Fell

One last thing... check out using parameterized queries. https://www.experts-exchange.com/Programming/Languages/Scripting/ASP/A_3626-ASP-Classic-Using-Parameterized-Queries.html

Robert Francis

ASKER

I ended up creating two variables. One that I could use on the first page that used "%" and the other passed the word "All". Then on the second page I used the if statement to convert the "All" to "%"

First page

<%reference2 = request.querystring("reference")%>
<%if request.querystring("reference") <> "all" then
   reference = request.querystring("reference")
   else reference = "%"
end if%>

Open in new window

second page

<%if request.querystring("reference2") <> "all" then
   reference2 = request.querystring("reference2")
   else reference2 = "%"
end if%>

Open in new window

Robert Francis

ASKER

Once again Scott, you are the man. I hope one day I will be able to pass this information on to someone else.