|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 27th, 2003, 02:33 AM
|
|||
|
|||
|
SQL statement parsing problem with Oracle
To anyone whose reading this,
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\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") strMonth = Request.Form("lstMonths") strYear = Request.Form("Year") AD = strDay & "-" & strMonth & "-" & strYear KW = Request.Form("Keyword") Pub = Request.Form("Publication") Cat = Request.Form("Category") 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") connectionToDatabase.open strconnection Set recordset=connectionToDatabase.Execute(strSQL) While Not recordset.EOF %><a href="<%Response.Write(recordset("LINK"))%>"><%Response.Write(recordset("LINK"))%></a><% Response.Write("<BR>") 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\wwwroot\Oracle\file.mht on my web browser (IE 6 or Netscape)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 or email me at hcbing@yahoo.com Thanks a lot of all your help!!!
|
|
#2
July 27th, 2003, 03:00 PM
|
|||
|
|||
|
Suggestion: Use the Oracle profiler tools to record what statement is being sent to the database.
Question: Does the user running the query have SELECT access to the target tables? Are you trapping all SQL errors?
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > SQL statement parsing problem with Oracle |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
