Grabbing external variables into SQL statement (create view) 1

bingbing · Jul 22, 2003

Hi, I'm having a problem with inputting values of external variables into an SQL statement which functions to create a view.
FYI, I'm using Oracle 9i Database Enterprise Edition. The following is my code:

<%
Dim recordSetLING
Dim connectionToDatabase
Dim KW

'In my webpage, I have a combobox named Keyword. Values chosen in combobox will be stored in the external variable KW
KW = " ' " & Request.Form("Keyword&quot

& " ' "

%>
'To check whether KW contains the correct value from combobox
 You have chosen
 Keyword: <%=KW%>

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

connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "PROVIDER=OraOLEDB.Oracle; Data Source=databasename; User ID=user; PASSWORD=password;"

Set recordSetLING=Server.CreateObject("ADODB.recordSet&quot

recordSetLING.Open "Create view LING as Keyword.Keyword_ID from Keyword where Keyword.Keywords=KW", connectionToDatabase
.
.
.
.
.
.
The above syntax outputs an error that states KW=invalid identifier. From my understanding, the values in KW was not input into the SQL statement before execution. I am certain that KW contains the correct value before insertion.

The problem is to input the value of KW into the SQL statement
"Create view...". I have referred to several books on SQL and creating views...so far I haven't encountered any syntax that allows the user to create view by grabbing values from external variables and then executing the statement. Your help is greatly appreciated.Thanks!!!

LV · Jul 22, 2003

Assuming that KW variable is string:

<%
.....
recordSetLING.Open "Create view LING as select Keyword.Keyword_ID from Keyword where Keyword.Keywords='" & KW & "'", connectionToDatabase
.....

%>

bingbing · Jul 22, 2003

Thanks for the info. Now i've managed to avoid the problem of KW being an invalid identifier. However, referring to my code above,

'To check whether KW contains the correct value from combobox
 You have chosen
 Keyword: <%=KW%>

This correctly shows me the value of KW that the user has chosen using a combobox in the interface. The problem is that in my results page I only see the check routine above and nothing else. By right it should display something from the database. I've run thru the SQL statements many times manually into the database and it works. Only in ASP it doesn't display it out.

Could it be that the value in KW was not inserted into the SQL statement?

What could be the problem if the value of KW was successfully inserted into the SQL statement?

Thanks for your help.

LV · Jul 22, 2003

"By right it should display something from the database"
Did you mean that you need to create a view, then reteieve from it and display the result on your web page? If so, then:
1. I'm not sure if this statement:
recordSetLING.Open "Create view LING as Keyword.Keyword_ID from Keyword where Keyword.Keywords=KW", connectionToDatabase"
will create a view and populate a recordset from it, most likely not, rather it'll just create the view but not retrieve from it, so your recordset is empty - that's why nothing's displayed on the page.
2. Why do you need to create a view? If just for retreiving a recordset from it, then simple Select will do the same, just faster. Also you won't need to worry about dropping the view afterwords.
<%
Dim recordSetLING
Dim connectionToDatabase
Dim KW

'In my webpage, I have a combobox named Keyword. Values chosen in combobox will be stored in the external variable KW
KW = " ' " & Request.Form("Keyword&quot

& " ' "

%>
'To check whether KW contains the correct value from combobox
 You have chosen
 Keyword: <%=KW%>

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

connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "PROVIDER=OraOLEDB.Oracle; Data Source=databasename; User ID=user; PASSWORD=password;"

Set recordSetLING=Server.CreateObject("ADODB.recordSet&quot

Set recordSetLING=connectionToDatabase.Execute("SELECT Keyword.Keyword_ID from Keyword where Keyword.Keywords='" & KW & "'&quot

'loop through the recordset...
while not recordSetLING.EOF
'work with records
recordSetLING.MoveNext
wend

recordSetLING.Close()
set recordSetLING = nothing
set connectionToDatabase = nothing

%>

bingbing · Jul 22, 2003

Hi,
To LV, I am really glad that your idea really helped me a lot. I've abandoned the idea of creating views and using Select statements all the way. I still have a small problem though...the following is my code:

Set recordSetLING=Server.CreateObject("ADODB.recordSet&quot

Set recordSetLING=connectionToDatabase.Execute("Select PUBLICATIONS.PUB_ID, CATEGORY.CAT_ID, ARTICLEDATE.DATE_ID, KEYWORD.KEYWORD_ID, PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID as AID from PUBLICATIONS, CATEGORY, ARTICLEDATE, KEYWORD where PUBLICATIONS.PUBLICATIONS="&Pub&" and CATEGORY.CATEGORY="&Cat&" and ARTICLEDATE.ART_DATE="&AD&" and KEYWORD.KEYWORDS="&KW&"&quot

Set recordSetLINK=Server.CreateObject("ADODB.recordSet&quot

recordSetLINK.Open "Select LINK from LINK where LINK.ARTICLE_ID=AID" ,connectionToDatabase

For recordSetLING, i would like to concatanate all those ids and name it as AID. Next I would like to place the value of AID in the recordSetLINK's select statement. AID would be a string. How do I do this?

bingbing · Jul 22, 2003

This is my latest revision of my code...ignore previous posts...

Set recordSetLINK=Server.CreateObject("ADODB.recordSet&quot

recordSetLINK.Open "Select 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=PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID" ,connectionToDatabase

Do While Not recordSetLINK.EOF

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

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

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

recordSetLINK.MoveNext

Loop

My problem is I don't get any display at my results page using the above SQL statement. However, running it thru the database I can get the wanted results. So my guess is it's syntax problem.

How do I set LINK.AID=PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID

where the concatenation part to become a string? Meaning the results should be LINK.AID='2222'. Thanks a lot for your help!!!

LV · Jul 22, 2003

First of all, I still don't see the surrounding single quotes around your string variables, i.e. "and KEYWORD.KEYWORDS=" & KW, should be "and KEYWORD.KEYWORDS='"& KW & "'", in order to include a string value into the WHERE portion of your statement. It looks like you use the biwise "OR" operator in your statement, it's hard to say without knowing what's your logic is. Try doing domething like this:

<%
...
sql = "Select 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=PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID"
Response.Write sql
Response.End
..
%>
Copy the written string from the page and execute it against the database, see if you get any results. It will help you correct syntax errors. And in order for a concatinated part to become a string, you need to surround it by single quotes, like in this sample:

<%
linkId = Request.QueryString("LinkId&quot

sql = "select * from LINK where LINK.LINKID='" & llinkId & "'"
%>
So if linkId = 2222, your sql variable will look like this:
select * from LINK where LINK.LINKID='2222'

bingbing · Jul 25, 2003

Hi again...based on your suggestion in the previous thread, I've tried using a variable to replace my entire SQL statement as a string. It works but currently I'm facing a problem with OraOLEDB (Oracle's Provider) since I'm using Oracle 9i for the database. My code looks like this now:

<HTML>
<HEAD></HEAD>

<BODY>
<%
'Connection using execute method and a string variable
Dim connectionToDatabase, strconnection, recordset
Dim AD, KW, Pub, Cat
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

& "'"

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=PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID"

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(" &quot

recordSetLINK.MoveNext

Loop

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

%>

</BODY>
</HTML>

I've tried using Response.Write strSQL and copied the string displayed and ran it thru my database. It works.
However, when I run this ASP page, it gives me OraOLEDB
"Missing Expression" error at the following line:

Set recordset=connectionToDatabase.Execute(strSQL)

Any mistakes with my syntax here?
Any other comments for my code?
Thanks a lot of your time...

bingbing · Jul 25, 2003

Hi again...okay so I rebooted my comp and ran the code again and this time there's no error except that I get a blank page after I ran the ASP file. I figured it could be my SQL part and following LV's previous suggestion on concatenation string...I've made the following changes...

<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 = Request.QueryString("PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID&quot

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(" &quot

recordSetLINK.MoveNext

Loop

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

%>

</BODY>
</HTML>

*I've replaced the concatenation statement using a variable called CNT. Single quotes has been included in the SQL statement too to parse it as a string. However, I'm not too sure on the correct syntax for the variable declaration:

CNT = Request.QueryString("PUBLICATIONS.PUB_ID||CATEGORY.CAT_ID||ARTICLEDATE.DATE_ID||KEYWORD.KEYWORD_ID&quot

I ran the code again and I still get a blank page.

LV · Jul 26, 2003

If you Response.Write strSql, copy it and execute against the database, do you get some results back?
Regarding the synatx: do you pass all those variables (PUBLICATIONS.PUB_ID, CATEGORY.CAT_ID, ARTICLEDATE.DATE_ID, KEYWORD.KEYWORD_ID) separately int the query string?
Does it looks like this:
myPage.asp?PUBLICATIONS.PUB_ID=1&CATEGORY.CAT_ID=2&ARTICLEDATE.DATE_ID=3&KEYWORD.KEYWORD_ID=4
If so then in order to build CNT varable you need to extract then separately:
CNT = Request.QueryString("PUBLICATIONS.PUB_ID&quot

& "||" & Request.QueryString("CATEGORY.CAT_ID&quot

& "||" & Request.QueryString("ARTICLEDATE.DATE_ID&quot

& "||" & Request.QueryString("KEYWORD.KEYWORD_ID&quot

and concatinate CNT to strSQL.

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(" &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 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!!!

LV · Jul 27, 2003

Just by looking at your code, you are missing a declaration of the Recordset object:
Set recordset = Server.CreateObject("ADODB.Recordset&quot

Set recordset=connectionToDatabase.Execute(strSQL)

Then you'll need to close and kill it as well:
recordset.Close()
set recordset = nothing

This might be a part of a problem, if the query executed directly against the database does bring you results back. Also you'll probably need to restructure your database, looks like it's just a flat set of tables with no relations between them.

shaddow · Jul 28, 2003

You dont need to define the RS object if you are using it like this Set recordset=connectionToDatabase.Execute(strSQL)
The object that a conexion returns it's an recordset.
If your code doesnt work you might need to reconsider 1 thing
the whole problem is this code

While Not recordset.EOF

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

Wend

As far as i can see this makes a link that points to the C:\Inetpub\

http://wwwroot\Oracle\file.mht

file but the problem is that C:\Inetpub\

http://wwwroot\Oracle\file.mht

it's a absolute path not a relative web path so that wont work. Instead you have to use a web relative path or absolute on that file.
But to achieve this you have to tell me if you can access using a browser this link

http://yourserveradress/Oracle/file.mht

If not there is an alternative solution.

________
George, M

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!

Grabbing external variables into SQL statement (create view) 1

bingbing

Technical User

LV

Programmer

bingbing

Technical User

LV

Programmer

bingbing

Technical User

bingbing

Technical User

LV

Programmer

bingbing

Technical User

bingbing

Technical User

LV

Programmer

bingbing

Technical User

LV

Programmer

shaddow

Programmer

Similar threads

Part and Inventory Search

Sponsor