Databases
StackOverflow2013
Postsdbo
POHow to Update/Insert Into Oracle Database From Selected Option in HTML Dropdown?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Update/Insert Into Oracle Database From Selected Option in HTML Dropdown?
    primarykey
    data
    text
    <p>Okay, so first off, I'm fairly new to web design. But for a project of mine, I'd been asked to create a page that populates multiple drop downs based on the tables in a number of databases. And I believe I have gotten this part to work, a look at my code so far (a jsp page):</p> <p><strong>CodeSelector.jsp</strong></p> <pre><code>&lt;%@page import="java.sql.*"%&gt; &lt;!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"&gt; &lt;html&gt; &lt;head&gt; &lt;meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"&gt; &lt;title&gt;Codes Page&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;form name = "codes" method = "POST" action="....." target="_self"&gt; &lt;h1&gt;Please select the applicable codes:&lt;/h1&gt; &lt;select name='code1' onchange="showState(this.value)"&gt; &lt;option value="none"&gt;Code One: None&lt;/option&gt; &lt;% String debug = "ON"; if(debug.equals("ON")) { System.out.println("***DEBUGGING IS TURNED ON!!!***"); } //Pulls the ids and descriptions from the first codes table and stores them in the first drop down try { String caseId = request.getParameter("caseID"); //caseId = "30"; if (caseId == null) { //debug System.out.println("The caseID is NULL!"); Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@url:sid","username","password"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select id, descr from case_codes"); String tempString; while(rs.next()) { //If the code description is more than 125 characters long, truncate the string and append "..." to the end of it. if (rs.getString(2).length() &gt; 125) { tempString = rs.getString(2).substring(0, 125); %&gt; &lt;option value="&lt;%=rs.getString(1)%&gt;"&gt;&lt;%=rs.getString(1)%&gt; &lt;%=tempString%&gt;...&lt;/option&gt; &lt;% } //Else just insert the whole description into the option field. else { %&gt; &lt;option value="&lt;%=rs.getString(1)%&gt;"&gt;&lt;%=rs.getString(1)%&gt; &lt;%=rs.getString(2)%&gt;&lt;/option&gt; &lt;% } } //Closes the database connection stmt.close(); con.close(); } else if (caseId != null) { if(debug.equals("ON")) { System.out.println("The caseID is NOT NULL!"); } Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@url:sid","username","password"); Statement stmt = con.createStatement(); //Returns a list of all the tables and views in the database if(debug.equals("ON")) { DatabaseMetaData meta = con.getMetaData(); ResultSet res = meta.getTables(null, null, null, new String[] {"TABLE"}); while (res.next()) { System.out.println( " "+res.getString("TABLE_CAT") + ", "+res.getString("TABLE_SCHEM") + ", "+res.getString("TABLE_NAME") + ", "+res.getString("TABLE_TYPE") + ", "+res.getString("REMARKS")); } } if(debug.equals("ON")) { System.out.println("BEFORE SQL Statement: select id from cases"); } //Returns a result set of all the ids in the cases table ResultSet rs = stmt.executeQuery("select id from cases"); if(debug.equals("ON")) { System.out.println("AFTER SQL Statement: select id from cases"); } while(rs.next()) { if(debug.equals("ON")) { System.out.println("The rs is: " + rs.getString(1)); } if(rs.getString(1).equals(caseId)) { if(debug.equals("ON")) { System.out.println("Case ID Found!"); } ResultSet rs2 = stmt.executeQuery("select rlawcd_id, display_seq from cs_rlawcd where cs_id = " + caseId); while(rs2.next()) { if(debug.equals("ON")) { System.out.println("Inside rs2 while loop"); } //If no values are returned in the rlawcd table, populate the drop down as you normally would if (rs2 == null) { if(debug.equals("ON")) { System.out.println("Inside rs2 IF"); System.out.println("rs2 = null"); } ResultSet rs3 = stmt.executeQuery("select id, descr from case_codes"); String tempString; while(rs3.next()) { //If the code description is more than 125 characters long, truncate the string and append "..." to the end of it. if (rs3.getString(2).length() &gt; 125) { tempString = rs3.getString(2).substring(0, 125); %&gt; &lt;option value="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=tempString%&gt;...&lt;/option&gt; &lt;% } //Else just insert the whole description into the option field. else { %&gt; &lt;option value="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=rs3.getString(2)%&gt;&lt;/option&gt; &lt;% } } } //Else if the values are indeed returned and the display sequence equals 1 //populate the drop downs normally but with the returned values set as the selected/default items else if(rs2.getString(2).equals("1")) { if(debug.equals("ON")) { System.out.println("Inside rs2 ELSE IF"); System.out.println("The rs2 is NOT NULL!"); } String codeID = rs2.getString(1); ResultSet rs3 = stmt.executeQuery("select id, descr from case_codes"); String tempString; while(rs3.next()) { if(debug.equals("ON")) { System.out.println("Inside rs3 while loop"); } if (rs3.getString(1).equals(codeID)) { if(debug.equals("ON")) { System.out.println("Inside rs3 IF"); System.out.println("A matching law code was found!"); } //If the code description is more than 125 characters long, truncate the string and append "..." to the end of it. if (rs3.getString(2).length() &gt; 125) { tempString = rs3.getString(2).substring(0, 125); %&gt; &lt;option selected="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=tempString%&gt;...&lt;/option&gt; &lt;% } //Else just insert the whole description into the default/selected option field. else { %&gt; &lt;option selected="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=rs3.getString(2)%&gt;&lt;/option&gt; &lt;% } } else { //If the code description is more than 125 characters long, truncate the string and append "..." to the end of it. if (rs3.getString(2).length() &gt; 125) { tempString = rs3.getString(2).substring(0, 125); %&gt; &lt;option value="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=tempString%&gt;...&lt;/option&gt; &lt;% } //Else just insert the whole description into the option field. else { %&gt; &lt;option value="&lt;%=rs3.getString(1)%&gt;"&gt;&lt;%=rs3.getString(1)%&gt; &lt;%=rs3.getString(2)%&gt;&lt;/option&gt; &lt;% } } } } else { if(debug.equals("ON")) { System.out.println("Inside the rs2 ELSE"); System.out.println("Something must have gone wrong."); } } } } else { //do nothing... } } //Closes the database connection stmt.close(); con.close(); } else { //debug System.out.println("Something weird happened."); } } catch (ClassNotFoundException e) { System.err.println("ClassNotFoundException: " + e.getMessage()); } catch (SQLException e) { System.err.println("SQLException: " + e.getMessage()); } catch (Exception e) { System.err.println("Generic Exception: " + e.getMessage()); } %&gt; &lt;/select&gt; &lt;br&gt; &lt;br&gt; &lt;input type="submit" value="Submit"&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>However, now I need to add the ability to update the database with update and insert statements based upon what the user selects in the drop down boxes from above. Again, being fairly new to this, I'm not sure what the best way to go about doing this would be? A lot of what I've found on google suggests this functionality mainly involves this part of the code:</p> <p><code>&lt;form name = "codes" method = "POST" action="...." target="_self"&gt;</code></p> <p>And it seems a lot of the examples online suggest using a seperate php page? But I didn't really understand how the two linked to one another and how one pages contents get transferred between the other page and the database you want to update. Could anyone with experience in this offer some advice here or point me in the right direction as to what I might want to do next in order to be able to write to the database when the <code>submit</code> button is clicked?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThis 0ne Pr0grammer
    1. USThis 0ne Pr0grammer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CORead the [Servlet wiki page](http://stackoverflow.com/tags/servlets/info). It covers the basic concepts to handle this. Also, you can read the [JSP wiki page](http://stackoverflow.com/tags/jsp/info)to dive more in the JSP concepts, as not having scriptlets in your page.
      singulars
      1. POHow to Update/Insert Into Oracle Database From Selected Option in HTML Dropdown?
      1. USLuiggi Mendoza
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload