I am still having problems with my code. I want to be able to insert data from a file into a database but when i spell one of the column names wrong (on pupose), it inserts the data that is correct then thows the exception, I dont want this as I want the exception to be thown first. If all the names are correct then go ahead and insert. I ahve tried the commit() and rollback() methods but I am still getting the same responce.
I dont know what I am doing wrong so could someone please help me?
Thanks Loffty
here is my code:
public void jButton2_MouseClicked(java.awt.event.MouseEvent mouseEvent) {
String dsn = new String (ivjJTextField3.getText()); String username = new String (ivjJTextField2.getText()); String password = new String (ivjJTextField1.getText()); // gets the table and column names from a JTable. JTable table = (JTable)((JButton)mouseEvent.getSource()).getClientProperty("DATABASE"); TableModel model = ivjScrollPaneTable.getModel(); int count = model.getRowCount(); String[] firstnames = new String[count]; ArrayList arrColumnNames = new ArrayList(); for (int i = 0; i < count; i++) { firstnames[i] = (String)model.getValueAt(i, 0); arrColumnNames.add(model.getValueAt(i,1));
}
List myList1 = Arrays.asList(firstnames); ArrayList myList = new ArrayList(arrColumnNames);
Set uniqueItems = new HashSet(); Set duplicates = new HashSet(); for (Iterator i = myList.iterator(); i.hasNext();){ Object e = i.next(); if (uniqueItems.add(e) == false) duplicates.add(e); } if (v.size() <= 0){ java.awt.Toolkit.getDefaultToolkit().beep(); JOptionPane.showConfirmDialog(null,"Please read in a CSV file.\nPlease refer to the help page for support.", "CSV file", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE);
} else if (myList1.contains("")){ java.awt.Toolkit.getDefaultToolkit().beep(); JOptionPane.showConfirmDialog(null,"Please check that all spaces are filled in your Table Name column.\nPlease refer to the help page for support.", "Table Name", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE);
} else if (arrColumnNames.contains("")){ java.awt.Toolkit.getDefaultToolkit().beep(); JOptionPane.showConfirmDialog(null,"Please check that all spaces are filled in your Column Name column.\nPlease refer to the help page for support.", "Column Name", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE);
} else if (duplicates.size() > 0) { java.awt.Toolkit.getDefaultToolkit().beep(); JOptionPane.showConfirmDialog(null,"Please check that you do not have duplicate names in your Column Names column.\nPlease refer to the help page for help and support.", "Duplicates", JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE);
} else if(duplicates.size() == 0){
Hashtable ht = new Hashtable(); //Add vector to store results Vector results = new Vector(); Vector results1 = new Vector(); Object value = null; String[] numbers = new String[count]; for (int i = 0; i < count; i++) { numbers[i] = Integer.toString(i); }
String values[] = firstnames; // First, map numbers to values in a hashtable Hashtable mappedNumbers = new Hashtable(); // the code matches values one by one for each of the numbers for(int i = 0; i < numbers.length; i++) { mappedNumbers.put(new Integer(numbers[i]), values[i]);
}
// Then we find each value and remove it from the hashtable for(int j = 0; j < values.length; j++) { ht = (mappedNumbers); value = (values[j]); Vector v2 = new Vector(); Vector v3 = new Vector(); if( ht.containsValue( value )) { Enumeration e = ht.keys(); while (e.hasMoreElements()) { Integer tempkey = (Integer)e.nextElement(); String tempvalue = (String)ht.get(tempkey); if (tempvalue.equals(value)) { v2.add(tempkey); arr4.add(tempvalue); ht.remove(tempkey); }
} //adds the results that are mapped out and stored in the vector v2 and add that vector //to another vector so you have a vector of vectors. results.add(v2);
} }
Hashtable ht2 = new Hashtable(); //Add vector to store results Vector results2 = new Vector();
Object value2 = null;
Vector vTable = new Vector(); Vector vInterim; String sArrayEntry = new String(); int iArrayEntry = 0; //asking how many vectors containing the repeating table names we have via results.size() for(int i = 0; i < results.size();i++){ vInterim = new Vector(); vTable = (Vector)results.elementAt(i);
} // we then put the column names that we get from arrColumnNames and store each // result in vectors inside a vector results2.add(vInterim);
}
//looks to see if any FirstNames are the same and if they are then only take one copy of each name Vector bigVectorOStuff = new Vector(arr4); Enumeration enum = bigVectorOStuff.elements(); String entry = null; while (enum.hasMoreElements()){ entry = (String)enum.nextElement(); if (!results1.contains(entry)){ results1.add(entry); } }
iii= 0; for ( ij = 0;ij < results2.size(); ij++) { vv2 = (Vector)results2.elementAt(ij); if ( iii < results1.size() ) { TableNames = (String)results1.get(iii); iii++; }
// puts the arrCoulmnNames into a stringbuffer to take away the [] and , columnnameChanger = vv2.toString(); sb = new StringBuffer(columnnameChanger); quetionMarks = new StringBuffer(); int replace = sb.length(); int FirstBracket = replace-2; int lastBracket = replace-1; sb.replace(0,1,""); sb.replace(FirstBracket,lastBracket,""); //puts question marks and commars in. to set up the prepared statement. for(int i = 0; i < vv2.size(); i++){ quetionMarks.append(","); quetionMarks.append("?"); } quetionMarks.replace(0,1,""); vv = (Vector)results.get(i);
//pulls out data from the vector v and v holds a csv file, and stores each output in an arraylist arr3 for( j = 0; j < vv.size(); j++ ){ int arrvalue = ((Integer)vv.get(j)).intValue(); Enumeration e1 = v.elements(); for(int i = 0;e1.hasMoreElements();i++){ o1 = e1.nextElement(); //num3 is the size of the first line in the csv file so that in order to pull out //all the firstnames from the csv file you woul have to pull every 1st word from each //line so for example if the first line is 12 words long, it knows to pull out every 12 //word and store it. int remainder = i%num3; if (remainder == arrvalue){ DataHolder.add(o1); } } } String url = "jdbc:odbc:" + dsn ; sql2 = "INSERT INTO "+tableNames+" (" + columnNames + ") VALUES (" + questionMarks + ")"; Connection conn = null ; PreparedStatement pss ; int off = 0;
I have tried print out before the preparredStatement and the executeUpdate statmnet and this is the output:
prepare one two one two one two one two one two one two one two one tw o one two one two prepare one two one two one two one two one two one two one tw o one two one two one two prepare one two one two one two one two one two one tw o one two one two one two one two prepare one java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Sever] Invalid column name 'Postcod'.
Previous Topic
