Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-09-06
6
Medium Priority
?
286 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 300 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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