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?
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What I would suggest is instead of showing what your db is doing with the wild card like

 <option value="%" selected="SELECTED">All</option>

use this instead

 <option value="" selected="SELECTED">All</option>

or

 <option value="all" selected="SELECTED">All</option>

Then in the post on nc_reports.asp use code like this
if request.form("reference") <> "" then
   reference = request.form("reference")
   else
   reference = "%"
end if

Open in new window

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

Open in new window

Then pass that variable reference instead of rquest.form("reference").

If you need a wildcard after the request.form then
 
if request.form("reference") <> "all" then
   reference = request.form("reference")&"%"
   else
   reference = "%"
end if

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frankhelkCommented:
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 ?).
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

frankhelkCommented:
@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.  ;-)
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Notice in the above, if you choose all, http://mypadas.com/ee/princeservice/?reference=all then the value of the variable reference is "%"
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
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

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Once again Scott, you are the man. I hope one day I will be able to pass this information on to someone else.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.