Update database from form

[timmbo]

Hi all,

Very new to Java and not sure how to do this.
Want to load my form's contents of select (AvailAccounts) to an array. Then for each element update my database. I have no idea how to accomplish this. ANY suggestions or assistance would be most appreciated.

Code so far...

import java.sql.*;
import java.util.*;

public class AccountUpdate implements java.io.Serializable {
private Vector accountUpdates = null;
private java.sql.Connection conn = null;
private static final String DBURL = "jdbcracle:thintsc:1521:tmsdb01p";
private static final String DBLogin = "tscadmin";
private static final String DBPassword = "e1cadm1n";
private static final String driverName = "oracle.jdbc.driver.OracleDriver";

for(int i=0;i<getParameter(AvailAccounts.size());i++) {
while(int j=0; j<i) {
try {
Class.forName(driverName);
conn = DriverManager.getConnection(DBURL, DBLogin, DBPassword);
PreparedStatement prepStmt = null;

prepStmt = conn.prepareStatement = &quot;UPDATE entitlement Set incustdashind = 'n', unitid = '&quot; & ()...&quot;
ResultSet resultSet = prepStmt.executeQuery();

j = j +1
}
}
}
}

 

[cwinans]

First off, your form is on a webpage?
AvailAccounts are what? Is that the form name?
The form contents are coming from a select box?

I'm eager to help but I need you to lay this out more.
I already have an idea but I don't want to go off on a tangent if I'm totally wrong. So just clarify and I'll see what I can do. Later. I hope this helped! ;-)
- Casey Winans

 

[timmbo]

Thanks for the reply Casey!

Yes, the form is a webpage.
AvailAccounts is the name for a select multiple.
Form contents are coming form a select box.

If you need more, please let me know.

Thanks again Casey.

 

[cwinans]

I wrote the code to go in a jsp page. If you're using a servlet I can help redo this code to make it work in a servlet too.

Is this basically what you wanted to do? I hope so.

<%@ page
	info   = &quot;A JSP page to update a db table with select (multi) box values&quot;
	import = &quot;java.sql.*&quot;
%>
<%

  String dbUrl = &quot;jdbc:oracle:thin:@tsc:1521:tmsdb01p&quot;;
  String dbUid = &quot;tscadmin&quot;;
  String dbPwd = &quot;e1cadm1n&quot;;
  
  try {
	  // loading Oracle jdbc driver
	  Class.forName(&quot;oracle.jdbc.driver.OracleDriver&quot;);
	  
	  // creating the database connection
	  Connection conn = DriverManager.getConnection(dbUrl, dbUid, dbPwd);
	  
	  PreparedStatement pstmt = null; // we'll use this in a bit
	  
	  String update_stmt = &quot;&quot;; // we'll use this in a bit
	  
	  // retrieving array of values from form's select box
	  String [] params = request.getParameterValues(&quot;AvailAccounts&quot;);
	  
	  if(!params == null) { // form was submitted properly and values were selected
	    for(int i = 0; i < params.length; i++) { // iterating thru select values
	    
	      update_stmt = &quot;UPDATE entitlement &quot; +
                        &quot;SET incustdashind = 'n', unitid = '&quot; + params[i] + &quot;'&quot;;
	      
	      pstmt = conn.prepareStatement(update_stmt);
	      pstmt.execute();
	    }
	    
	    // if the code got this far without throwing an exception it was a success
	    out.println(&quot;<H1><B>Hooray! The code executed successfully!</B></H1>&quot;);
	    out.println(&quot;Yippee!&quot;);
	  }
	  else { // tell client that db update failed because no data was submitted
	    out.println(&quot;<H1>Sorry, the update(s) did not occur!</H1>&quot;);
	    out.println(&quot;This happened because no form data was retrieved by this jsp page.<BR>&quot;);
	    out.println(&quot;<BR><B>Please try again, thanks.</B>&quot;);
	  }
  }
  catch(SQLException ex) {
	// so error messages are displayed as they are written to the client
	out.println(&quot;<PRE>&quot;);
	
	// give it the out writer so it can print to the client
	ex.printStackTrace(out);
	
	while(ex != null) {
	  out.println(&quot;SQLState: &quot; + ex.getSQLState());
	  out.println(&quot;Message:  &quot; + ex.getMessage());
	  out.println(&quot;Vendor:   &quot; + ex.getErrorCode());
	  out.println();
	  
	  ex = ex.getNextException();
    }
	
	out.println(&quot;</PRE>&quot;);
  }
  catch(ClassNotFoundException ex) {
	out.println(&quot;<H1><B>Oracle Driver not found</B></H1>&quot;);
  }
  catch(ArrayIndexOutOfBoundsException ex) {
	out.println(&quot;<H1><B>Form values array went out of bounds</B></H1>&quot;);
  }
  catch(Exception ex) {
	out.println(&quot;<H1><B>A general exception occurred</B></H1>&quot;);
	out.println(ex.toString());
  }
  
%>

I hope this helped! ;-)
- Casey Winans

 

[timmbo]

Thanks Casey!!! I appreciate the help.