Solved

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

Posted on 2001-09-06
6
267 Views
Last Modified: 2012-06-21
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>
0
Comment
Question by:FRANKRE
6 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6461938
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
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6462100
Post some code.
0
 
LVL 1

Expert Comment

by:NathanC
ID: 6463014
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 100 total points
ID: 6464203
I get the values for the array programmatically by iterating through tables server side and then printing them into a JavaScript using Response.write so that they are available on the client side:

<SCRIPT LANGUAGE=javascript>
<!--
certificates = new Array(5)
certificates [0] = new Array(1)
certificates [1]= new Array(1)
certificates [2] = new Array(4)
certificates [3] = new Array(3)
certificates [4] = new Array(6)


//Empty (0)
certificates [0][0] = " "

//AEG  (1)
certificates [1] [0] = "CAGS"

//DPH   (2)
certificates [2][0] = "CAGS"
certificates [2][1] = "MS"
certificates [2][2] = "MSD"
certificates [2][3] = "DScD"

//ENDO   (3)
certificates [3][0] = "CAGS"
certificates [3][1] = "MSD"
certificates [3][2] = "DScD"

//IMPLANTOLOGY Credential   (4)
certificates [4][0] = "Fellowship in  Implantology (12 Months)"
certificates [4][1] = "CAGS Implantology (24 Months)"
certificates [4][2] = "CAGS Perio & Implantology (48 Months)"
certificates [4][3] = "CAGS Pros & Implantology (48 Months)"
certificates [4][4] = "CAGS Implant/Perio/Pros (60 Months)"
certificates [4][5] = "MSD Implantology (Additional 12 Months w/ 4 or 5 Yr. Programs)"

//========================================================

This function is called by the onChange method of the first option box. Depending on the value selected, it passes a value of, in this case, a 0 through 4. The following JavaScript then populates the second option box.

function FillList(num)
{

var boxlength = 0

document.designate.degree.selectedIndex = 0
for ( ctr=0;ctr<certificates[num].length;ctr++)
     {
     boxlength++;
     document.designate.degree.options[ctr] = new Option(certificates[num] [ctr], certificates[num][ctr]);
     }

document.designate.degree.length = boxlength;
document.designate.degree.focus() ;
document.designate.savebutton.focus() ;
document.designate.degree.focus() ;
}

//-->
</SCRIPT>

The advantage here is that you can populate any option based on the selection of another all server side. The downside for your request, however, is that the second option is always visible.

I understand that there are ways to solve this visibility problem for IE, but it gets difficult if you want a solution for all browsers. The above code works for both IE and Netscape
0
 

Author Comment

by:FRANKRE
ID: 6465195
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>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 8579968
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
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now