[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trying to get a drop down menu of users from Oracle Datababe

Posted on 2005-04-26
17
Medium Priority
?
191 Views
Last Modified: 2010-04-01
Hi Ive got the following code to try implement a drop down menu which selects all users from a table called Employees. And then I want to delete the member that is selected. Im getting a couple of errors and just wondering if anyone could help thanks. Code i have is ...

<%@ page session="true" %>
<%@ page language="java" contentType="text/html" import="java.sql.*"  %>
<html>
<body bgcolor="#FFFFFF">
<table width="770" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#000000">
  <tr>
    <td bgcolor="#FFFFFF"><table width="770" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr>
          <td><a href="index.jsp"><img src="mom_logo.gif" width="256" height="74" border="0"></a></td>
          <td width="411">&nbsp;</td>
        </tr>
        <tr>
          <td width="359"><img src="leftbar.gif" width="359" height="39"></td>
          <td align="right" bgcolor="336CA1">&nbsp;</td>
        </tr>
        <tr>
          <td colspan="2"><img src="topbar.gif" width="770" height="69"></td>
        </tr>
        <tr>
          <td colspan="2">
            <table width="100%" border="0" cellspacing="0" cellpadding="0" background="but_bg.gif">
              <tr>
                <td align="right"><img src="spacer.gif" width="15" height="23"><img src="spacer.gif" width="15"

height="23"><img src="spacer.gif" width="15" height="23"><img src="spacer.gif" width="15" height="23"><img src="spacer.gif"

width="15" height="23"><img src="spacer.gif" width="45" height="23"></td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
      <table width="770" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr>
          <td width="167" rowspan="3" valign="top" bgcolor="8FBEE8">&nbsp;          </td>
          <td colspan="2" align="center"><table width="95%" border="0" cellpadding="5" cellspacing="0" class="text">
              <tr>
                <td class="text"><font color="#000000"><br>
                  <br>
                </font></td>
              </tr>
            </table></td>
        </tr>
        <tr>
          <td width="588" align="center">
<p>
              <%
out.print("Administrator logged in: " + session.getValue("UserName")+ "<br>");
%>
            </p>
<p>
 
<%

    //Database connection
%>

              <%
    Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ID FROM Employees");

String name = null;
ArrayList list =  new ArrayList();
  while ( rs.next() ) {
name=rs.getString("UserName");
list.add(name);
}
%>
              <%
{
    connection.close();
}

%>
            </p>
<form name="frm1" id="frm1" method="post" action="deletedemp.jsp" onSubmit="return validateForm(frm1)">
              <p>&nbsp; </p>
              <table width="40%">
                <tr>
                  <td width="47%"><select name="name">
                      <%  
     if(list != null)
     {
       for(int i =0; i < list.size(); i++)
       {
           String value = (String) list.get(i);
  %>
                      <option value="<%=value%>"><%=value%></option>
                      <%}
  }
  else
  {
%>
                      <option value=dummy>None</option>
                      <%}%>
                    </select></td>
                  <td width="16%">&nbsp;</td>
                  <td width="37%"><input type="submit" name="Submit3" value="Delete"></td>
                </tr>
              </table>
              <p>&nbsp; </p>
              <p>
</form>
              </p>
          </form>
            </td>
          <td width="15" align="center" valign="top">&nbsp; </td>
        </tr>
        <tr>
          <td colspan="2" valign="bottom"><table width="82%" border="0" cellspacing="0" cellpadding="8">
              <tr>
                <td align="center" valign="bottom"><font color="#000000" size="1" face="Verdana, Arial, Helvetica,

sans-serif"><font></td>
              </tr>
            </table></td>
        </tr>
      </table></td>
  </tr>
</table>
</body>
</html>

//and errors are the following


An error occurred between lines: 95 and 105 in the jsp file: /momentum/deleteemp.jsp

Generated servlet error:
G:\Apache Tomcat 4.0\work\Standalone\localhost\_\momentum\deleteemp$jsp.java:106: Class org.apache.jsp.ArrayList not found.
                ArrayList list =  new ArrayList();
                ^
An error occurred between lines: 95 and 105 in the jsp file: /momentum/deleteemp.jsp

Generated servlet error:
G:\Apache Tomcat 4.0\work\Standalone\localhost\_\momentum\deleteemp$jsp.java:106: Class org.apache.jsp.ArrayList not found.
                ArrayList list =  new ArrayList();
                                      ^
An error occurred between lines: 118 and 124 in the jsp file: /momentum/deleteemp.jsp

Generated servlet error:
G:\Apache Tomcat 4.0\work\Standalone\localhost\_\momentum\deleteemp$jsp.java:129: Variable list may not have been initialized.
                     if(list != null)
                        ^
Note: G:\Apache Tomcat 4.0\work\Standalone\localhost\_\momentum\deleteemp$jsp.java uses or overrides a deprecated API.  Recompile with "-deprecation" for details.
3 errors, 2 warnings

0
Comment
Question by:acslater
14 Comments
 
LVL 29

Accepted Solution

by:
bloodredsun earned 2000 total points
ID: 13868321
You need to import the package that contains ArrayList

<%@ page language="java" contentType="text/html" import="java.sql.*, java.util.ArrayList"  %>

and it seems to be null or you haven't initialised it.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13870148
just putting my comments so I know whats going on this thread...
what bloddred is saying wd have been my first suggestions too..
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13870869
Good to know you're here, Kuli

Your thoughts on this (and any other matter) are always gratefully received... ;-)

and by the way this: session.getValue("UserName") has been depreciated for this: session.getAttribute("UserName")
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.

 

Author Comment

by:acslater
ID: 13874066
OK, I've included the above package, and I'm not getting the errors any more.
The one I'm getting now is:

javax.servlet.ServletException: ORA-00904: invalid column name

I've change:

              <%
    Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ID FROM Employees");

to what is correct in the database
              <%
    Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT Emp_ID FROM Employees");
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13874515
Here you want "SELECT <column> FROM <table>"

not knowing your db schema, I can't tell you but it should be easy to find out which is correct. if you do a "Select * from Employees" you can get the column names if you don't have a tool for that.
0
 

Author Comment

by:acslater
ID: 13876311
Ok, i have the drop down menu working.

Is it possible to have 3 or 4 drop down menus on the one page?
My problem is running a query where calls are between 1 - 100, between 101 - 200, 201 - 300 etc ..

Or is there a more convenient way of doing this?
For my drop down I have ....

<%
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT Call_Duration FROM Calls WHERE Call_Duration < 100");

String name = null;
ArrayList list =  new ArrayList();
  while ( rs.next() ) {
name=rs.getString("Call_Duration");
list.add(name);
}
%>

0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13877452
>>Is it possible to have 3 or 4 drop down menus on the one page?

Yes, you can have as many as you want. Just make sure that you close your resultset and statement before you re-use it (which I notice you're not doing at the moment).
0
 

Author Comment

by:acslater
ID: 13905621
i have the drop down menu on the page, but when i bring up the next page, it dosen't display the details or the table for some reason. I changed it to just display about 8 fields of a users details from the db, but its not bringing up anything. Is this because the closing form would be in the wrong place or something different?
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 15344163
agreed
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 15344192
Actually I disagree, (sorry, changed my mind).

The users original question was answered, he then went on to ask some 2 further questions: one was answered and one wasn't.

Original Q
" Trying to get a drop down menu of users from Oracle Datababe"

Later Post after help:
"Ok, i have the drop down menu working."

It's only later he asks about doing this across multiple select boxes that no one helped him further.
0
 
LVL 20

Expert Comment

by:Venabili
ID: 15345362
I think he solved it mostly alone...
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 15345891
The bits he solved appear to have been done at my suggestion.

He had issues with not importing java.util.ArrayList and then an incorrect column name. I gave one possible solution each to deal with those issues and then he says "Ok, i have the drop down menu working", so it would appear that my advice worked. I also gave advice to not use a deprecated method in HttpSession.

Notably he also didn't say anything similar to "actually I did this instead" to indicate that he did anything else by himself.
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 15534261
Anything going on here?
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 15557261
agreed
0

Featured Post

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.

Question has a verified solution.

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

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

873 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