Link to home
Start Free TrialLog in
Avatar of FRANKRE
FRANKRE

asked on

Can an OnClick event be used to query an Oracle db using vbScript?

I have created an ASP page with numerous list boxes. I would like to have the list boxes not appear except when the user has clicked a command button. The script has an SQL query statement and code to then list the output. Nothing happens when I click the button. I have also tried to use a sub-procedure. Is it that I MUST create a link to an external page to make this work?

Here is the code:

<HTML>
<HEAD><TITLE>Concordance Query</TITLE>
</HEAD>
<!BODY BACKGROUND="/samples/images/backgrnd.gif">
<!--#include file="adovbs.inc"-->
<!--#include File="dbconn.asp"-->

<BODY BGCOLOR="FFFFFF">

<TABLE WIDTH="640">
<TR>
<TD ALIGN="center">&nbsp; <FONT SIZE="5">"Trade Shifts" Concordance Reference Query by SubDigests:</td>
<TD>
<font size=4>
<IMG SRC="Eagle.gif" ALIGN="top" ALT="USITC Eagle" height="100">
</TD>
</tr>
<FORM METHOD="POST" ACTION="conc.idc">
<TR><TD>
Input a value for Year:<INPUT NAME="VAR_YEAR" VALUE="2000" SIZE="4" > 
</td></TR>
<TR><TD>
Input a value for Digest:<INPUT NAME="VAR_DIGEST" VALUE="AG002" SIZE="5" >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input
type="button" value="     Show Digests    " name="cmdDigest">
<SCRIPT FOR="cmdDigest" EVENT="ONCLICK" LANGUAGE="vbscript">
     Alert "you're on the right track BABY!!!"
     
</script>
</td></TR>
<TR><TD>
Input a value for Sub-Digest:<INPUT NAME="VAR_SUBDIGEST" VALUE="Z1" SIZE="5" >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input
type="button" value="Show Sub-Digests" name="btnSubDigest">
</td></tr>
<br>
<TR><TD WIDTH="100%"><FONT COLOR="Red" SIZE="2">
Note: For digests that do not have any sub-digests, enter "Z1" as the sub-digest value.
</TD></TR>
<TR>
<td ALIGN="left">
<INPUT TYPE="SUBMIT" VALUE="Run Query">
</TD></TR>
<TR>
<TD>
<%
    Server.ScriptTimeout=300
    Dim myRS, strDigest, strDtitle, myVar
    Set myRS = Server.CreateObject("ADODB.Recordset")
    myRS.Open "SELECT All Digest, Digest_Title FROM Digest_Titles_For_ID Order by Digest", myConn
%>

<Select NAME="Ctryname" SIZE="5">
<%  
    While Not myRS.EOF
    strDigest = myRS("Digest")
    strDtitle = myRS("Digest_Title")
%>
    <OPTION VALUE = "<%= strDigest %> "> 
<%      
    Response.write strDigest & " - " & strDtitle & "</option>"
     myRS.MoveNext
    Wend
%>
    </select>
</TD>

<TR>
<TD>
<%
    Server.ScriptTimeout=300
    Dim myRS2, strDigest2, strDtitle2,strSDigest2,strSDtitle2
    Set myRS2 = Server.CreateObject("ADODB.Recordset")
    myRS2.Open "SELECT ALL TRADE.DIGEST_TITLES_FOR_ID.DIGEST,TRADE.DIGEST_TITLES_FOR_ID.DIGEST_TITLE,TRADE.SUBDIGEST_TITLES_FOR_ID.SUBDIGEST,TRADE.SUBDIGEST_TITLES_FOR_ID.SUBDIGEST_TITLE
FROM TRADE.DIGEST_TITLES_FOR_ID, TRADE.SUBDIGEST_TITLES_FOR_ID WHERE (TRADE.DIGEST_TITLES_FOR_ID.DIGEST=TRADE.SUBDIGEST_TITLES_FOR_ID.DIGEST)",myConn
%>

<Select NAME="Ctryname2" SIZE="5">
<%  
    While Not myRS2.EOF
    strDigest2 = myRS2("Digest")
    strDtitle2 = myRS2("Digest_Title")
    strSDigest2 = myRS2("SubDigest")
    strSDtitle2 = myRS2("SubDigest_Title")
%>
    <OPTION VALUE = "<%= strSDigest2 %> "> 
<%      
    Response.write strDigest2 & " - " & strSDigest2 & " - " & strDtitle2 & " - " &  strSDtitle2 & "</option>"
     myRS2.MoveNext
    Wend
%>
    </select>
</TD>
</tr>
</FORM>
</font>
</BODY>
</HTML>
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image

One way around this is to create programmatically JavaScript arrays based on the SQL query. You can then set the onClick method of the command button to populate your list boxes. If you are interested, I could show you sample code. However, this does not solve the visibility issue.

Fritz the Blank
Post some code.
Avatar of NathanC
NathanC

The question is do you want this to occur on the server side or on the client side.
If server side- make your buttons "submit" the page to itself. then:
In HTML-
<input type=submit value=ShowList1 name=action>

In server side script on repost-
<%
'****************
function CreateSelect(rs,selectName)
dim strHTML
if not rs.eof or not rs.bof then
 strHTML = "<SELECT Name=" & selectname &">"
 do while not rs.eof
  strHTML=strHTML & "<OPTION Value=" & Chr(34) & rs(0) & Chr(34) & ">" & rs(1) & "</OPTION>"
  rs.movenext
 loop
 strHTML = strHTML & "</Select>"
else
strHTML = "&nbsp;"
end if
CreateSelect = strHTML
end function
'***************
sub ShowLists()
  dim conn
  dim rs
  set conn = server.createobject("adodb.connection")
  set rs = server.createobject("adodb.recordset")
  select case request("action")
   case "ShowList1"
    strsql = "Select ListVal,DisplayVal from SomeTable"
   case "ShowList2"
    strsql = "Select ListVal,DislayVal from SomeTable2"
   '.......

  end select
 if strsql <> "" then
   conn.open connectionstring
   rs.open strsql,conn
   response.write CreateSelect(rs,"list" & request("action"))
   rs.close
   set rs = nothing
   conn.close
   set conn = nothing
 end if
end sub

%>

Calling ShowLists() from embedded vbscript where you want the html to display
Cons: you can only have one list at a time
     round trip to server every time
pro: a lot less sent to the client on initial and subsequent loads of page


I didn't realize how late it was- I'll post the javascript/clientside solution if you are interested-
it entails building all the select boxes on the server associated to the buttons, but setting:

document.all.listboxname.style.display = "none"

during initial load of page for invisible and then the onclick for each button would pass it's associated list box to a function where:

document.all.listboxname.style.display = "" 

would make it visible- in a select case structure.
You can also create mutually exclusive lists by making one = "" and the other "none" on alternate clicks of buttons.  I've done this in both client side javascript and vbscript this deals with Visibility issue from fritz.
ASKER CERTIFIED SOLUTION
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FRANKRE

ASKER

Sorry, here is the code.

<HTML>
<HEAD><TITLE>Concordance Query</TITLE>
</HEAD>
<!BODY BACKGROUND="/samples/images/backgrnd.gif">
<!--#include file="adovbs.inc"-->
<!--#include File="dbconn.asp"-->

<BODY BGCOLOR="FFFFFF">

<TABLE WIDTH="640">
<TR>
<TD ALIGN="center">&nbsp; <FONT SIZE="5">"Trade Shifts" Concordance Reference Query by SubDigests:</td>
<TD>
<font size=4>
<IMG SRC="Eagle.gif" ALIGN="top" ALT="USITC Eagle" height="100">
</TD>
</tr>
<FORM METHOD="POST" ACTION="conc.idc">
<TR><TD>
Input a value for Year:<INPUT NAME="VAR_YEAR" VALUE="2000" SIZE="4" > 
</td></TR>
<TR><TD>
Input a value for Digest:<INPUT NAME="VAR_DIGEST" VALUE="AG002" SIZE="5" >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="button" value="     Show Digests    " name="cmdDigest">
<SCRIPT FOR="cmdDigest" EVENT="ONCLICK" LANGUAGE="vbscript">
      Alert "you're on the right track BABY!!!"
     
</script>
</td></TR>
<TR><TD>
Input a value for Sub-Digest:<INPUT NAME="VAR_SUBDIGEST" VALUE="Z1" SIZE="5" >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="button" value="Show Sub-Digests" name="btnSubDigest">
</td></tr>
<br>
<TR><TD WIDTH="100%"><FONT COLOR="Red" SIZE="2">
Note: For digests that do not have any sub-digests, enter "Z1" as the sub-digest value.
</TD></TR>
<TR>
<td ALIGN="left">
<INPUT TYPE="SUBMIT" VALUE="Run Query">
</TD></TR>
<TR>
<TD>
<%
     Server.ScriptTimeout=300
     Dim myRS, strDigest, strDtitle, myVar
     Set myRS = Server.CreateObject("ADODB.Recordset")
     myRS.Open "SELECT All Digest, Digest_Title FROM Digest_Titles_For_ID Order by Digest", myConn
%>
 
<Select NAME="Ctryname" SIZE="5">
<%  
     While Not myRS.EOF
     strDigest = myRS("Digest")
     strDtitle = myRS("Digest_Title")
%>
     <OPTION VALUE = "<%= strDigest %> "> 
<%      
     Response.write strDigest & " - " & strDtitle & "</option>"
      myRS.MoveNext
     Wend
%>
     </select>
</TD>

<TR>
<TD>
<%
     Server.ScriptTimeout=300
     Dim myRS2, strDigest2, strDtitle2,strSDigest2,strSDtitle2
     Set myRS2 = Server.CreateObject("ADODB.Recordset")
     myRS2.Open "SELECT ALL TRADE.DIGEST_TITLES_FOR_ID.DIGEST,TRADE.DIGEST_TITLES_FOR_ID.DIGEST_TITLE,TRADE.SUBDIGEST_TITLES_FOR_ID.SUBDIGEST,TRADE.SUBDIGEST_TITLES_FOR_ID.SUBDIGEST_TITLE FROM TRADE.DIGEST_TITLES_FOR_ID, TRADE.SUBDIGEST_TITLES_FOR_ID WHERE (TRADE.DIGEST_TITLES_FOR_ID.DIGEST=TRADE.SUBDIGEST_TITLES_FOR_ID.DIGEST)",myConn
%>
 
<Select NAME="Ctryname2" SIZE="5">
<%  
     While Not myRS2.EOF
     strDigest2 = myRS2("Digest")
     strDtitle2 = myRS2("Digest_Title")
     strSDigest2 = myRS2("SubDigest")
     strSDtitle2 = myRS2("SubDigest_Title")
%>
     <OPTION VALUE = "<%= strSDigest2 %> "> 
<%      
     Response.write strDigest2 & " - " & strSDigest2 & " - " & strDtitle2 & " - " &  strSDtitle2 & "</option>"
      myRS2.MoveNext
     Wend
%>
     </select>
</TD>
</tr>
</FORM>
</font>
</BODY>
</HTML>
So, where are we with this?

Please close this question out by accepting the comment that helped you the most as an answer.

Fritz the Blank