Blank page (no results) from my SQL query 1

bingbing · Jul 26, 2003

Hi all, I've got a problem with the following ASP code. I'm writing a search page (interface) using VBScript to connect to an Oracle 9i database. The problem is that I get a blank page (no results) from my SQL query.
Using Response.Write strSQL, I copied the string of SQL statement displayed and ran thru it in my database.Well, the SQL statement worked perfectly but when parsed in the ASP page no results displayed.
The following is my code:

<HTML>
<HEAD></HEAD>

<BODY>
<%
'Connection using execute method and a string variable
Dim connectionToDatabase, strconnection, recordset
Dim AD, KW, Pub, Cat, CNT
Dim strSQL

strDay = Request.Form("Day&quot

strMonth = Request.Form("lstMonths&quot

strYear = Request.Form("Year&quot

AD = "'" & strDay & "-" & strMonth & "-" & strYear & "'"
KW = "'" & Request.Form("Keyword&quot

& "'"
Pub = "'" & Request.Form("Publication&quot

& "'"
Cat = "'" & Request.Form("Category&quot

& "'"
CNT = "PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID"

strconnection = "PROVIDER=OraOLEDB.Oracle; Data Source=databasename; User ID=user; PASSWORD=password;"
strSQL="Select LINK.LINK, PUBLICATIONS.PUB_ID, CATEGORY.CAT_ID, ARTICLEDATE.DATE_ID, KEYWORD.KEYWORD_ID from LINK, PUBLICATIONS, CATEGORY, ARTICLEDATE, KEYWORD where PUBLICATIONS.PUBLICATIONS="&Pub&" and CATEGORY.CATEGORY="&Cat&" and ARTICLEDATE.ART_DATE="&AD&" and KEYWORD.KEYWORDS="&KW&" and LINK.AID="&CNT&""

Set connectionToDatabase=Server.CreateObject("ADODB.Connection&quot

connectionToDatabase.open strconnection

Set recordset=connectionToDatabase.Execute(strSQL)

Do While Not recordset.EOF

%><a href="<%=Response.Write(recordset("LINK&quot

)%>"><%=Response.Write(recordset("LINK&quot

)%></a><%
Response.Write("<BR>&quot

recordSetLINK.MoveNext

Loop

' Close our recordset and connection and dispose of the objects
connectionToDatabase.Close
Set connectionToDatabase = Nothing

%>

</BODY>
</HTML>

*I'm using a variable called CNT to replace the whole concatenation statement. Note that it's number data type so don't think single quotes are required here.

Anyone got an idea why i'm not displaying any results?

adroit · Jul 26, 2003

Hi ,
Every thing seems to be OK. But I would suggest you try

1.
Server.URLEncode(recordset("LINK&quot

) instead of
(recordset("LINK&quot

.

2.
While Not recordset.EOF
//code
recordset.MoveNext
Wend

3. Check for the spelling mistake in the record set name
recordSetLINK or recordset ?

Genimuse · Jul 26, 2003

#3 looks like a winner to me.

If that doesn't solve it, do you end up with a truly blank page or just one where no data appears? If you view source was any HTML generated?

Also note that the <%= code tells ASP to response.write, so this:

Code:

%><a href=&quot;<%=Response.Write(recordset(&quot;LINK&quot;))%>&quot;><%=Response.Write(recordset(&quot;LINK&quot;))%></a><%

could (should?) be simplified to this:

Code:

%><a href=&quot;<%=recordset(&quot;LINK&quot;)%>&quot;><%=recordset(&quot;LINK&quot;)%></a><%

If you'd really, really like to use the response.writes then you'd at least simplify it to this:

Code:

%><a href=&quot;<%Response.Write(recordset(&quot;LINK&quot;))%>&quot;><%Response.Write(recordset(&quot;LINK&quot;))%></a><%

but that seems kinda silly.

bingbing · Jul 26, 2003

Dear LV and anyone whose reading this,

So far LV's suggestions have been really helpful in debugging my code. However, I feel that it's appropriate to explain the logic of my code and what I actually intend to do with my SQL statement.

I have the following tables in my database:
Publications:
PUB_ID PUBLICATIONS
---------- ------------------
2 Star

Category:
CAT_ID CATEGORY
---------- ----------------
4 Business

ArticleDate:
DATE_ID ART_DATE
---------- ---------
1 10-JUL-03

Keyword:
KEYWORD_ID KEYWORDS
----------- ----------------
1 Bank,Merger

Link:
AID LINK
---------- -----------------------------------
2411 C:\Inetpub\

http://wwwroot\Oracle\file.mht

Hence, what I intend to achieve is that when a user selects Star, Business, 10-JUL-03, Bank,Merger from the interface, the ASP page would return the values
PUB_ID = 2
CAT_ID = 4
DATE_ID = 1
KEYWORD_ID = 1

From my ASP code below, I concatenate the four values to become 2411 which i put in a variable called CNT.
Hence, from the SQL statement, it would look up from table Link and find LINK.AID=2411. Finally I should be able to display the link to that file.

The following is my code (which doesn't return any results):

<HTML>
<HEAD></HEAD>

<BODY>
<%
'Connection using execute method and a string variable
Dim connectionToDatabase, strconnection, recordset
Dim AD, KW, Pub, Cat, CNT
Dim strSQL

strDay = Request.Form("Day&quot

strMonth = Request.Form("lstMonths&quot

strYear = Request.Form("Year&quot

AD = strDay & "-" & strMonth & "-" & strYear
KW = Request.Form("Keyword&quot

Pub = Request.Form("Publication&quot

Cat = Request.Form("Category&quot

CNT = "PUBLICATIONS.PUB_ID" & "||" & "CATEGORY.CAT_ID" & "||" & "ARTICLEDATE.DATE_ID" & "||" & "KEYWORD.KEYWORD_ID"

strconnection = "PROVIDER=OraOLEDB.Oracle; Data Source=databasename; User ID=user; PASSWORD=password;"
strSQL="Select LINK.LINK, PUBLICATIONS.PUB_ID, CATEGORY.CAT_ID, ARTICLEDATE.DATE_ID, KEYWORD.KEYWORD_ID from LINK, PUBLICATIONS, CATEGORY, ARTICLEDATE, KEYWORD where PUBLICATIONS.PUBLICATIONS='"&Pub&"' and CATEGORY.CATEGORY='"&Cat&"' and ARTICLEDATE.ART_DATE='"&AD&"'and KEYWORD.KEYWORDS='"&KW&"' and LINK.AID="&CNT&""

Set connectionToDatabase=Server.CreateObject("ADODB.Connection&quot

connectionToDatabase.open strconnection

Set recordset=connectionToDatabase.Execute(strSQL)

While Not recordset.EOF

%><a href="<%Response.Write(recordset("LINK&quot

)%>"><%Response.Write(recordset("LINK&quot

)%></a><%
Response.Write("<BR>&quot

recordSet.MoveNext

Wend

' Close our recordset and connection and dispose of the objects
connectionToDatabase.Close
Set connectionToDatabase = Nothing

%>

</BODY>
</HTML>

My goal's to display C:\Inetpub\

http://wwwroot\Oracle\file.mht

on my a web browser when I click the submit button.
Tried checking using Response.Write strSQL and copy&pasted
the string and ran it thru my database. It works and gives me the correct result.
I really need help on this. Been stuck at this problem (not able to display anything on my results page) for weeks...

If anyone out there knows the problem with my code do post a reply.
Thanks a lot of all your help!!!

bernlaw · Jul 26, 2003

Here's something you might try. I ran into this problem on my Oracle database and ASP. When running a SQL statement in Oracle's SQL Worksheet, dates are used as DD-MMM-YY, however when running the same query from an ASP page I have to use YYYYMMDD format for dates.
I can't remember if an error is generated if the format is wrong and I'm not at the office to check. But try changing the line;

AD = strDay & "-" & strMonth & "-" & strYear
to:
AD = strYear & strMonth & strDay

Make sure the year is a four digit year. In fact, just for testing, I'd manually put the date in to check it.

AD= "20030710"

Your database shows to have that date in it, from the list you posted. If hard coding the date works, then change the format to the variables.

I'm not sure if the quirk lies in Oracle or with the way ADO connects to Oracle. But this solved the problem for me.

Bernie

bingbing · Jul 27, 2003

Hi Bernie...mind explaining 'bout hard coding the dates?...in my database for ArticleDate table, the record states 10-JUL-03.Hence, that's why I have

AD = strDay & "-" & strMonth & "-" & strYear
to replace into ARTICLEDATE.ART_DATE='"&AD&"' which gives me ARTICLEDATE.ART_DATE='10-JUL-03'. Only in this date format DD-MMM-YY will it work in SQL*Plus Worksheet.

You mentioned that in ASP u had to use YYYYMMDD format...why? Is there a possibility that ASP isn't parsing the date as '10-JUL-03'? I've tested it using Response.Write strSQL to make sure that the SQL statement is the exact one that will run in the SQL*Plus Worksheet.
Thanks for you help btw...

bernlaw · Jul 27, 2003

While SQL *Plus Worksheet shows the date as DD-MMM-YY and you can actually use dates in SQL statements within SQL Worksheet in that format, I've found that it doesn't work when you use the dates in that format from within ASP pages. So that is why you may be having the problem.

What I mean when I say "hard code" the date, is just put the date into the variable and see if you get results.

So replace this line:
AD = strDay & "-" & strMonth & "-" & strYear
with this one:
AD= "20030710"

This "hard codes" a date into the variable AD in the format I was talking about. If the format change works, and you get results back, then re-write your code so that the date is formatted this way. Remember to make the year a four digit year. ie:
AD = strYear & strMonth & strDay

I realize that if you use Response.Write strSQL[/green] it will show the code with the date formatted the way you want. And that will run fine in SQL Worksheet. But I stumbled across this quirk the hard way when we upgraded our Oracle database to 8.0 and the date formats changed.

Give it a try and see if it works and then let me know.

Bernie

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!

Blank page (no results) from my SQL query 1

bingbing

Technical User

adroit

Programmer

Genimuse

Programmer

bingbing

Technical User

bernlaw

Programmer

bingbing

Technical User

bernlaw

Programmer

Similar threads

Part and Inventory Search

Sponsor