Failing to find records even though query is correct.

RobColborne · Sep 23, 2004

Hi

Got an asp page that has a table which has hyperlinks to open a detailed info of the selected record. This is working in some instances.

Because I had problems I stripped down the ASP page to output the SQL query string and the number of records in the record set.

When I copy and paste the query into Query Analyser it works.

Because I have had problems previously, I think that the problem is related to the record ID having < > and other special characters.

I tried using Server.HTMLencode() but this did not work.

I have also outputted the record ID both with HTML encoding and as it is.

When the page works both of these values are the same because the ID doesn't contain any special characters.

But when it fails the output will be different:

With encoding: AGW7FVJ(<4[^\NZ
With out encodng: AGW7FVJ(<4[^\NZ

What has happened is clearly the < character has been converted to < which is probably causing the query to fail in finding any records.

But the SQL query string is correct. So I am stuck as to what to the problem is and what to do.

Thanks

Rob

Chopstik · Sep 23, 2004

Perhaps if you post some of the relevant code we can help you to spot the offending code. ;-)

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

RobColborne · Sep 23, 2004

No Probls, I didn't want to make my post so long that people ignore it!

Code:

sGoldMineRECID = Request.QueryString ("Recid")

StrSQL ="SELECT Rectype, userid, createby, ondate, ref, notes, actvcode, resultcode FROM CONTHIST WHERE CONTHIST.RECID ='" & sGoldMineRECID & "'"
rsGoldMine.Open strsql,cnngoldmine,adOpenStatic
%>
<HTML>
<HEAD>
<META http-equiv=Content-Type content="text/html; charset=unicode">
</head>
<BODY >
<INPUT name="Recid" value="<%=sGoldMineRecid%>">
<p><FONT face=Arial size="3" colour="Blue"><B>Count:</B><font color="#ff0000"><%Response.Write(rsGoldMine.recordcount)%></font></p>
<p><font face="Arial" size="1"><b>SQL Query: </b><font color="#ff0000"><%Response.Write(strSQL)%></font></p>
<p><font face="Arial" size="1"><b>No encoding: </b><font color="#ff0000"><%Response.Write(sGoldMineRECID)%></font></p>

<p><font face="Arial" size="1"><b>HTML Encode after Response: </b><font color="#ff0000"><%=Response.Write(server.htmlencode(sGoldMineRECID))%></font></p>
<p><font face="Arial" size="1"><b>Server Encode before Response: </b><font color="#ff0000"><%=server.HTMLEncode(response.Write(sGoldMineRECID))%></font></p>
<p><font face="Arial" size="1"><b>URL Encode: </b><font color="#ff0000"><%server.UrlEncode(response.Write(sGoldMineRECID))%></font></p></FONT></FONT></FONT></FONT></FONT></FONT>
</BODY>
</html>

Chopstik · Sep 23, 2004

So what exactly does your SQL string look like when you response.write it?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

RobColborne · Sep 23, 2004

The Output as viewed in a web browser is as follows. The SQL query is correct:

Count:0

SQL Query: SELECT Rectype, userid, createby, ondate, ref, notes, actvcode, resultcode FROM CONTHIST WHERE CONTHIST.RECID ='AGW7FVJ(<4[^\NZ'

No encoding: AGW7FVJ(<4[^\NZ

HTML Encode after Response: AGW7FVJ(<4[^\NZ

Server Encode before Response: AGW7FVJ(<4[^\NZ

URL Encode: AGW7FVJ(<4[^\NZ

mbiro · Sep 23, 2004

Not to ask a dumb question, but, if you are using GET for form action, does the string look correct in the url? If it is encoded there, you need to decode it before putting it into the sql statement.

Chopstik · Sep 23, 2004

Ok, maybe I'm just being thick headed, but from your above example, it appears that no encoding gives you what you need, yes? If so, then I'm not sure where your problem lies. Or perhaps I am not getting some simple concept in what is happening with your code...

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

Genimuse · Sep 23, 2004

The < character has nothing to do with it, I'm certain.

I think it's a problem with your connection to the database. One minor thing, in your .Open command you don't specify the type of lock. You might try:

Code:

rsGoldMine.Open strsql, cnngoldmine, adOpenStatic, adLockReadOnly

Does rsGoldMine.recordcount return a value in this situation? I seem to recall that recordcount only returns a usable number with certain open types and locks.

If you

Code:

Response.Write(rsGoldMine("createdby"))

for example, do you get anything? Perhaps your reliance on recordcount isn't giving you the real picture.

RobColborne · Sep 23, 2004

Cool, lots to reply to - thank you!

I have spent a fair amount of time trying to sort this out on my own already.

To answer Chopstik, that's what I initially couldn't work out. I am not using encoding, the SQL query that is outputted is correct but still it does not produce any records in the record set.

To answer Genimuse I have already tried to add a database field to the output using Response.write(rsgoldmine("fieldname")), but I get a recordset error:
"Item cannot be found in the collection corresponding to the requested name or ordinal"
Also, in some instances the record ID is correct and produces some records.

Finally to answer mbiro, I think this is where the problem lies, in the referring ASP page I am using:

Code:

<a title=Zoom href=zoomactivity.asp?recid=" & server.urlencode(sRecidArray(iCount)) &">

As the link, which then opens the page with the problem showing the URL in the browser as:

Code:

zoomactivity.asp?recid=AGW7FVJ%28%26lt%3B4%5B%5E%5CNZ

mbiro · Sep 23, 2004

Did you try:

sGoldMineRECID = Server.UrlDecode(Request.QueryString ("Recid"))

?

Chopstik · Sep 23, 2004

This:

Code:

zoomactivity.asp?recid=AGW7FVJ%28%26lt%3B4%5B%5E%5CNZ

reads like this decoded (at least if I didn't transcribe it incorrectly manually):

Code:

zoomactivity.asp?recid=AGW7FVJ<&lt;4[^\NZ

It looks like what it would read with the HTML encoding. I think that you are correct in that the problem is not on this page but the referring page. Are you certain of the value of the (sRecidArray(iCount)) value in that page being what you think it should be?

mbiro, is that functionality available in ASP or is that an ASP.NET functionality?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

RobColborne · Sep 23, 2004

The problem is now sorted (I hope

)

On the referring page I had used

Code:

(rsGoldMine(server.HTMLEncode("Recid")

I took this away and appears if this is now correct.

Thanks to all your replies.

Rob

mbiro · Sep 23, 2004

I use it in asp.net, not available in classic. Sorry. Shouldn't need it though.

The '<' sign encodes as %3C. I don't know where the 'lt' is coming from in your example. Are you copying and pasting the string from some Microsoft app (word, IE) and pasting it somewhere to get the variable?

Genimuse · Sep 23, 2004

The '<' sign URLEncodes as '%3C', true, but it HTMLEncodes as '<'.

Glad your problem is sorted.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Failing to find records even though query is correct.

RobColborne

MIS

Chopstik

Technical User

RobColborne

MIS

Chopstik

Technical User

RobColborne

MIS

mbiro

Programmer

Chopstik

Technical User

Genimuse

Programmer

RobColborne

MIS

mbiro

Programmer

Chopstik

Technical User

RobColborne

MIS

mbiro

Programmer

Genimuse

Programmer

Similar threads

Part and Inventory Search

Sponsor