access line breaks in query string

[briancostea]

i am trying to pass a variable via query string. the problem, however is that the variable contains line breaks. i tried to replace them, but no luck.

 

[DotNetGnat]

try:

replace(myvar,vbcrlf,"")

or

replace(myvar,chr(10),"")

or

replace(myvar,chr(13),"")

can you show us what you tried and what is not working...

-DNG

 

[briancostea]

<a href="prepare.asp?sub=<%=replace(strOver, vbCrLf, "+")%></a>

in prepare.asp:
session("psub") = replace(Request.QueryString("sub"), "+", vbCrLf)
Response.Redirect("up3.asp")

in up3.asp:
strSQL = "SELECT * FROM tblExperts WHERE tblExperts.Subject_Area = '" & session("psub") & "';"

the record is not found in the access database.

 

[DotNetGnat]

how about this:
strSQL = "SELECT * FROM tblExperts WHERE tblExperts.Subject_Area = '" & Server.HtmlEncode(session("psub") )& "';"

response.write strSQL

-DNG

 

[briancostea]

same result

 

[DotNetGnat]

can you show the actual output of response.write statement...

-DNG

 

[briancostea]

SELECT * FROM tblExperts WHERE tblExperts.Expertise = 'Branding' AND tblExperts.Subject_Area = 'Brand Voice' AND tblExperts.Client = 'Sears Roebuck & Co.' AND tblExperts.Descr LIKE '%Defined brand voice for family focused campaign Rock out! Big Time!%' AND tblExperts.Agency = 'HLB Communications, Inc.';

when i look at the table in ms access, there is a line break after the word campaign. there is also one after rock out!

 

[damber]

replace:

<a href="prepare.asp?sub=<%=replace(strOver, vbCrLf, "+")%></a>

with:

<a href="prepare.asp?sub=<%=server.URLEncode(strOver)%></a>

replace:

session("psub") = replace(Request.QueryString("sub"), "+", vbCrLf)

with:

session("psub") = Request.QueryString("sub")

or for a more secure application:

session("psub") = replace(replace(replace(Request.QueryString("sub"),"\'","'") , "'", "''"), "--", "")

(these are not the only troublesome strings that may invade your SQL statement - so check this out in more detail)


then replace this:

strSQL = "SELECT * FROM tblExperts WHERE tblExperts.Subject_Area = '" & Server.HtmlEncode(session("psub") )& "';"

with:

strSQL = "SELECT * FROM tblExperts WHERE tblExperts.Subject_Area = '" & session("psub") & "';"

(we've taken care of some of the unlikeable values whilst reading the raw user input)

This does rely on the fact that the value being passed is actually the same as something in the database. Which means it has to have been INSERTed to the DB with a CRLF... Why would you have a subject area with a CRLF in it ? Of course, it's possible - just seems a little unusual to me.

As per DNG's suggestion - do a: response.write strSQL
and look at the result - check if the value in the WHERE clause is in the database field you expect.

Also, not sure why you have the intermediary page 'prepare.asp' - do you need it ? if you do, then for better performance, you may want to try server.transfer, unless prepare.asp has something to send to the user.

Also, the where clause is looking for a long description as part of the search,... why? This seems terribly inefficient? If it is just user input then it is unlikely to work for that many keywords, if it is automatic from another page, why not just use the ID for the record ? or just the other elements, which are similarly interesting, yet much shorter..

whatever generated that string needs to keep the crlf - if you want to add them you can do so using the Chr(13) & chr(10) to add in the string. If they were there originally then you are probably stripping them out somewhere..

A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

so you have line breaks in your access database and want to match them up??

and thats a big search string...

-DNG

 

[briancostea]

this is an update function. the sql string needs description because there can be multiple descriptions per subject area. i have replace the line breaks with a "+" in order to pass via query string. if i don't replace them, it will only take up to the first line break. everything works fine if i remove the line breaks from the access table.