Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2001-09-06
6
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 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…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 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