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

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

acslaterAsked:
Who is Participating?
 
bloodredsunConnect With a Mentor Commented:
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
 
KuldeepchaturvediCommented:
just putting my comments so I know whats going on this thread...
what bloddred is saying wd have been my first suggestions too..
0
 
bloodredsunCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
acslaterAuthor Commented:
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
 
bloodredsunCommented:
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
 
acslaterAuthor Commented:
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
 
bloodredsunCommented:
>>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
 
acslaterAuthor Commented:
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
 
bloodredsunCommented:
agreed
0
 
bloodredsunCommented:
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
 
VenabiliCommented:
I think he solved it mostly alone...
0
 
bloodredsunCommented:
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
 
bloodredsunCommented:
Anything going on here?
0
 
bloodredsunCommented:
agreed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.