×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

getting values in combobox from database without refreshing!

getting values in combobox from database without refreshing!

getting values in combobox from database without refreshing!

(OP)
Hi
I'm new to JSP. What i'm trying here is that to retrieve data from oracle database to my jsp combo boxes.

The problem is:
stage1:
I have 3 to 4 combo boxes and these combo boxes are linked with one another. for example:  
This is how it should happen:
selecting combo1...triggers a query and combo2 gets it values accordingy. same for combo2 &combo3 and so on.
stage2:
i also want to update my combo box without refreshing my page.

I think it is possible by combining javascript and jsp.

Here is my  jsp code for connecting to oracle

CODE

<%@ page import="java.sql.*" %>
<HTML>
<HEAD><TITLE>My App</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF">
<CENTER>
<B>connecting</B>
<BR><BR>

<%
   Connection conn = null;
   try
   {
      Class.forName("oracle.jdbc.driver.OracleDriver");

      conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.2:1521:orcl",
                "scott",
                "tiger");

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM Emp");

      //Print start of table and column headers
      out.println("<TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\">");
      out.println("<TR><TH>ID</TH><TH>NAME</TH></TR>");

      //Loop through results of query.
      while(rs.next())
      {
         out.println("<TR>");
         out.println("<TD>" + rs.getString("empno") + "</TD>");
         out.println("<TD>" + rs.getString("eName") + "</TD>");
         out.println("</TR>");
      }

      out.println("</TABLE>");
   }
   catch(SQLException e)
   {
      out.println("SQLException: " + e.getMessage() + "<BR>");
      while((e = e.getNextException()) != null)
         out.println(e.getMessage() + "<BR>");
   }
   catch(ClassNotFoundException e)
   {
      out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");
   }
   finally
   {
      //Clean up resources, close the connection.
      if(conn != null)
      {
         try
         {
            conn.close();
         }
         catch (Exception ignored) {}
      }
   }
%>

</CENTER>
</BODY>
</HTML>

Can somebody please help me on this
Thanx

RE: getting values in combobox from database without refreshing!

(OP)
Hi Venur
Thanks a lot for ur help. The code is running fine.

I need to access oracle database so i made some changes in
'ajaxOptions.jsp' and used 'division.jsp' instead of ajaxOptionResult.jsp.
Here r the changes that i made (I marked the changes in red for ur convenience.)

'ajaxOptions.jsp'

CODE

<%@ page contentType="text/html;charset=windows-1252"%>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
    <title>Address Form</title>
    <!-- include the AJAX JS File -->
    
    <script src="prototype.js" type="text/javascript"></script>
    <SCRIPT LANGUAGE="JavaScript">

/*
onChange event of the dropDownList will calls this function
which has AJAX call to Struts Action class
@param: dropDownList object (this)
@param: URL or Struts Action
*/
function depedentDropDown(obj, url) {
    var stateValue = $F(obj)
    var pars = "Circle=" + stateValue;
    //alert(stateValue);
    var myAjax = new Ajax.Request(url, {method: 'get', parameters: pars,
        onComplete: showResponse});
}

/*
Upon completing the request the AJAX will call this method
which is responsible for loading the depedent list from the XML
*/
function showResponse(originalRequest)
{
    var list = $('city');
    var xmlString = originalRequest.responseXML;
    var items = xmlString.getElementsByTagName('labelValueBean');
    clearList(list);
    if (items.length > 0)
    {
        for (var i = 0; i < items.length; i++)
        {
            var node = items[i];
            var value = "";
            var label = "";
            if (node.getElementsByTagName("label")[0].firstChild.nodeValue) {
                value = node.getElementsByTagName("label")[0].firstChild.nodeValue;
                label = node.getElementsByTagName("value")[0].firstChild.nodeValue;
            }
            addElementToList(list, value, label);
        }
    }
    else
    {
        addElementToList(list, "", "-- Select is Empty --");
    }
}

/**
remove the content of te list
*/
function clearList(list)
{
    while (list.length > 0)
    {
        list.remove(0);
    }
}

/**
Add a new element to a selection list
*/
function addElementToList(list, value, label)
{
    var option = document.createElement("option");
    option.value = value;
    var labelNode = document.createTextNode(label);
    option.appendChild(labelNode);
    list.appendChild(option);
}



</SCRIPT>
  </head>
  <body>
    <form method="POST" action="">
      <fieldset style="padding: 2">
        <legend>Address Form</legend>
        <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="58%">
          <tr>
            <td width="24%">Address</td>
            <td width="76%">
              <input type="text" name="address1" size="20"/>
            </td>
          </tr>
          <tr>
            <td width="24%">Apt#</td>
            <td width="76%">
              <input type="text" name="address2" size="20"/>
            </td>
          </tr>
          <tr>
            <td width="24%">State</td>
            <td width="76%">

              <select size="1" name="Circle" onChange="depedentDropDown(this,'Division.jsp')">
                <option>Select Circle</option>
                <option value="1">Circle I</option>
                <option value="2">Circle II</option>
        <option value="3">Circle III</option>
              </select>

            </td>
          </tr>
          <tr>
            <td width="24%">City</td>
            <td width="76%">
              <select size="1" name="city">
                <option>Select City</option>
              </select>
            </td>
          </tr>
          <tr>
            <td width="24%">Zip</td>
            <td width="76%">
              <input type="text" name="zip" size="20"/>
            </td>
          </tr>
        </table>
      </fieldset>
      <p>
        <input type="submit" value="Submit" name="B1"/>
        <input type="reset" value="Reset" name="B2"/>
      </p>
    </form>
  </body>
</html>


division.jsp

CODE

<%@ page import="java.sql.*" %>
<%
   String Par = request.getParameter("Circle");
   Connection conn = null;
   try
   {
      Class.forName("oracle.jdbc.driver.OracleDriver");

      conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.2:1521:orcl",
                "scott",
                "tiger");

      Statement stmt = conn.createStatement();
      String q = new String();
      q="SELECT * FROM division WHERE ccode= "+Par;
      ResultSet rs = stmt.executeQuery(q);
 StringBuffer buffer = new StringBuffer();
 buffer.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
  buffer.append("\n");
  buffer.append("<root>");
  buffer.append("\n");
  
 while(rs.next())
      {
      
       buffer.append("<labelValueBean>");
      buffer.append("\n");
        buffer.append("<label>" + rs.getString("ENAME") + "</label>");
        buffer.append("\n");
        buffer.append("<value>"+ rs.getString("ENAME")+"</value>");
      buffer.append("\n");
      buffer.append("</labelValueBean>");
        
      }
 buffer.append("\n");
  buffer.append("</root>");
  System.out.print(buffer.toString());
  response.addHeader("Content-Type", "text/xml");
  response.setContentType("text/xml; charset=windows-1252");
  out.write(buffer.toString());

}
   catch(SQLException e)
   {
      out.println("SQLException: " + e.getMessage() + "<BR>");
      while((e = e.getNextException()) != null)
         out.println(e.getMessage() + "<BR>");
   }
   catch(ClassNotFoundException e)
   {
      out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");
   }
   finally
   {
      //Clean up resources, close the connection.
      if(conn != null)
      {
         try
         {
            conn.close();
         }
         catch (Exception ignored) {}
      }
   }


%>


Now when i run the file 'ajaxOptions.jsp'after selecting the combo state no data comes in city combo.
connection with database is fine as running 'division.jsp' alone successfully creates an XML file.

what could be the possible problem

Please help!
SoftJack

RE: getting values in combobox from database without refreshing!

Hi,

Try to debug the response. Is it populating city dropdown with this value "-- Select is Empty --" onChange of state?

1. Is it division.jsp or Division.jsp ? it is case sensitive change it to what you jsp page name is.
2. In the showResponse function add an alert statement and see if it is displaying the response xml. later you can comment that alert statement.

CODE

/*
Upon completing the request the AJAX will call this method
which is responsible for loading the depedent list from the XML
*/
function showResponse(originalRequest)
{
    var list = $('city');
    var xmlString = originalRequest.responseXML;
    alert(originalRequest.responseText);
    var items = xmlString.getElementsByTagName('labelValueBean');
    clearList(list);
    if (items.length > 0)
    {
        for (var i = 0; i < items.length; i++)
        {
            var node = items[i];
            var value = "";
            var label = "";
            if (node.getElementsByTagName("label")[0].firstChild.nodeValue) {
                value = node.getElementsByTagName("label")[0].firstChild.nodeValue;
                label = node.getElementsByTagName("value")[0].firstChild.nodeValue;
            }
            addElementToList(list, value, label);
        }
    }
    else
    {
        addElementToList(list, "", "-- Select is Empty --");
    }
}
 

Cheers
Venu

RE: getting values in combobox from database without refreshing!

(OP)
Hi again!

yes i am getting this in city combo "-- Select is Empty --"

My file name is 'Division.jsp' same mentioned in 'ajaxOptions.jsp'

This is what i get as response xml after adding alert statement.

CODE

<?xml version="1.0" encoding="UTF-8" ?>
 <root>
 <labelValueBean>
  <label>SMITH</label>
  <value>SMITH</value>
  </labelValueBean>
  <labelValueBean>
  <label>ALLEN</label>
  <value>ALLEN</value>
  </labelValueBean>
  <labelValueBean>
  <label>WARD</label>
  <value>WARD</value>
  </labelValueBean>
  <labelValueBean>
  <label>JONES</label>
  <value>JONES</value>
  </labelValueBean>
  <labelValueBean>
  <label>MARTIN</label>
  <value>MARTIN</value>
  </labelValueBean>
  <labelValueBean>
  <label>BLAKE</label>
  <value>BLAKE</value>
  </labelValueBean>
  <labelValueBean>
  <label>CLARK</label>
  <value>CLARK</value>
  </labelValueBean>
  <labelValueBean>
  <label>SCOTT</label>
  <value>SCOTT</value>
  </labelValueBean>
  <labelValueBean>
  <label>KING</label>
  <value>KING</value>
  </labelValueBean>
  <labelValueBean>
  <label>TURNER</label>
  <value>TURNER</value>
  </labelValueBean>
  <labelValueBean>
  <label>ADAMS</label>
  <value>ADAMS</value>
  </labelValueBean>
  <labelValueBean>
  <label>JAMES</label>
  <value>JAMES</value>
  </labelValueBean>
  <labelValueBean>
  <label>FORD</label>
  <value>FORD</value>
  </labelValueBean>
  <labelValueBean>
  <label>MILLER</label>
  <value>MILLER</value>
  </labelValueBean>
  </root>

Something still missing! Don't know what?
rechecked many times.

Thanx for ur quick responses!
I think i need more help here!
Softjack

RE: getting values in combobox from database without refreshing!

Hi,

hmmm.. you are able to see the XML in the alert statement. Try to put the debug statements in showResponse function.

CODE

/*
Upon completing the request the AJAX will call this method
which is responsible for loading the depedent list from the XML
*/
function showResponse(originalRequest)
{
    var list = $('city');
    var xmlString = originalRequest.responseXML;
    alert(originalRequest.responseText);
    var items = xmlString.getElementsByTagName('labelValueBean');
    alert(items.length); // should alert lenght
    clearList(list);
    if (items.length > 0)
    {
        for (var i = 0; i < items.length; i++)
        {
            var node = items[i];
            var value = "";
            var label = "";
            alert(node); // should show [object]
            if (node.getElementsByTagName("label")[0].firstChild.nodeValue) {
                value = node.getElementsByTagName("label")[0].firstChild.nodeValue;
                label = node.getElementsByTagName("value")[0].firstChild.nodeValue;
            }
            addElementToList(list, value, label);
        }
    }
    else
    {
        addElementToList(list, "", "-- Select is Empty --");
    }
}

Cheers
Venu

RE: getting values in combobox from database without refreshing!

(OP)
Hi Venu,
I think nothing is returned in this line

var items = xmlString.getElementsByTagName('labelValueBean');

also this line

alert(items.length);

displays 0

regards
Softjack

RE: getting values in combobox from database without refreshing!

Hi,

Strange.... in the alert statement is shows the XML ie., alert(originalRequest.responseText); but it doesnot get the lableValeBean with xmlString.getElementsByTagName('labelValueBean');

The only reason I can see is that the response is not of type XML.

Can you run the division.jsp it should print the XML on the browser. Not print but load the XML on the browser.

Cheers
Venu

RE: getting values in combobox from database without refreshing!

(OP)
Dear Venu,
I already run my division.jsp alone and it prints the XML stuff on the browser.
Here is the output (you can see '-' signs for expansion)

CODE

<?xml version="1.0" encoding="UTF-8" ?>
- <root>
- <labelValueBean>
  <label>SMITH</label>
  <value>SMITH</value>
  </labelValueBean>
- <labelValueBean>
  <label>ALLEN</label>
  <value>ALLEN</value>
  </labelValueBean>
- <labelValueBean>
  <label>WARD</label>
  <value>WARD</value>
  </labelValueBean>
- <labelValueBean>
  <label>JONES</label>
  <value>JONES</value>
  </labelValueBean>
- <labelValueBean>
  <label>MARTIN</label>
  <value>MARTIN</value>
  </labelValueBean>
- <labelValueBean>
  <label>BLAKE</label>
  <value>BLAKE</value>
  </labelValueBean>
- <labelValueBean>
  <label>CLARK</label>
  <value>CLARK</value>
  </labelValueBean>
- <labelValueBean>
  <label>SCOTT</label>
  <value>SCOTT</value>
  </labelValueBean>
- <labelValueBean>
  <label>KING</label>
  <value>KING</value>
  </labelValueBean>
- <labelValueBean>
  <label>TURNER</label>
  <value>TURNER</value>
  </labelValueBean>
- <labelValueBean>
  <label>ADAMS</label>
  <value>ADAMS</value>
  </labelValueBean>
- <labelValueBean>
  <label>JAMES</label>
  <value>JAMES</value>
  </labelValueBean>
- <labelValueBean>
  <label>FORD</label>
  <value>FORD</value>
  </labelValueBean>
- <labelValueBean>
  <label>MILLER</label>
  <value>MILLER</value>
  </labelValueBean>
  </root>

what do you think??

regards,
Softjack

RE: getting values in combobox from database without refreshing!

(OP)
Hi Venu,
Thanks a lot for all your help. You're a real life saver !
Just wanted to tell u i finally solved the problem.

Here what i did

CODE

var xml_doc = new ActiveXObject("Microsoft.XMLDOM");
    xml_doc.async = false;
    xml_doc.loadXML(originalRequest.responseText);

Rest of the code is same. It is working fine!

Thanx once again!

Sofjack


RE: getting values in combobox from database without refreshing!

Softjack i have one question for you?
I`m trying also to make this ajax work so i ned to now
where did you put those three lines

var xml_doc = new ActiveXObject("Microsoft.XMLDOM");
    xml_doc.async = false;
    xml_doc.loadXML(originalRequest.responseText);


...in which code?

Can you show the whole code and put those three lines
in red to show the differnce aginst old code.

Can you do that and help me?
Thanks

RE: getting values in combobox from database without refreshing!

Hi,

You you need put those three lines of code in the JS function
showResponse. Now it will like this

CODE

/*
Upon completing the request the AJAX will call this method
which is responsible for loading the depedent list from the XML
*/
function showResponse(originalRequest)
{
    var list = $('city');
    var xml_doc = new ActiveXObject("Microsoft.XMLDOM");
    xml_doc.async = false;
    xml_doc.loadXML(originalRequest.responseText);
     var items = xml_doc.getElementsByTagName('labelValueBean');

    //var xmlString = originalRequest.responseXML;
    //alert(originalRequest.responseText);
   // alert(items.length); // should alert lenght
    clearList(list);
    if (items.length > 0)
    {
        for (var i = 0; i < items.length; i++)
        {
            var node = items[i];
            var value = "";
            var label = "";
            //alert(node); // should show [object]
            if (node.getElementsByTagName("label")[0].firstChild.nodeValue) {
                value = node.getElementsByTagName("label")[0].firstChild.nodeValue;
                label = node.getElementsByTagName("value")[0].firstChild.nodeValue;
            }
            addElementToList(list, value, label);
        }
    }
    else
    {
        addElementToList(list, "", "-- Select is Empty --");
    }
}

Cheers
Venu

RE: getting values in combobox from database without refreshing!

Well i put it but i have same problem like softjack
My code of that function is:


function showResponse(originalRequest)
            {
    var list = $('city');
           
    var xml_doc = new ActiveXObject("Microsoft.XMLDOM");
        xml_doc.async = false;
        xml_doc.loadXML(originalRequest.responseText);

   //This request i got OK
   alert("Request je"+originalRequest.responseText);

  var items = xml_doc.getElementsByTagName("labelValueBean");

            //Here i got length:0
            alert("length:"+items.length);
            
            
            clearList(list);
            if (items.length > 0)
            {
            for (var i = 0; i < items.length; i++)
            {
            var node = items[i];
            var value = "";
            var label = "";
            if (node.getElementsByTagName("label")[0].firstChild.nodeValue) {
            value = node.getElementsByTagName("label")[0].firstChild.nodeValue;
            label = node.getElementsByTagName("value")[0].firstChild.nodeValue;
            }
            alert(label);
            addElementToList(list, value, label);
            }
            }
            else
            {
            addElementToList(list, "", "-- Select is Empty --");
            }
            }


I got request OK but,
I got length:0
so after that all i can see i second list box
    "-- Select is Empty -"
why i got length:0 ?
What i am going wrong?
I am workin on win2000 and with IE 6.0 sp1.
Please help!

RE: getting values in combobox from database without refreshing!

Hi I finally find solution.
with this function shoeRespones() was everything OK,
i make some stupid mistake and forgot to move one line in jsp page.
Thanks for help guys ,see you!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close