Hi there,
Im having a problem with an SQL query with regards to a LIKE operation in the statement in my Java program. Basically I have a search function in my stock control program to search for a part either by part number or description and output the results to a table. The SQL statement pulls in the data from two string variables. The trouble is that only the description part of the search works as searching for a part number just outputs the whole database content of all the parts. I just require that both searching by either part number OR description works. Ive tried for many hours on this now by adding in brackets and such and enclosing the OR part of the SQL statement but nothing works. I belive my syntax must be wrong or maybe I cant execute an SQL statement with WHERE, OR and LIKE in the one query. Any help would be much appreciated. Thank you.
Heres is the Search GUI to help you see what I mean:
Here is the code:
CODE
Connection conn = connection;
String Inumber = this.txtInumber.getText();
String Description= this.txtDescription.getText();
//create the table and data initialisation
try {
//read data from table this.connection = conn;
ResultSet res = executeQuery( "SELECT * FROM mainpage where partno = '"+Inumber+"' OR description LIKE '%"+Description+"%' ");
// count the number of records in the result set
int counter = 0;
//put data into object 2d array
while( res.next() ){
counter++;
}
data = new Object[ 120][ 6 ];
counter = 0;
res = executeQuery( "SELECT * FROM mainpage where partno = '"+Inumber+"' OR description LIKE '%"+Description+"%' ");
while( res.next() ){
data[ counter ][ 0 ] = res.getString( "partno" );
data[ counter ][ 1 ] = res.getString( "description" );
data[ counter ][ 2 ] = res.getInt( "minstocklevel" );
data[ counter ][ 3 ] = res.getInt( "count" );
data[ counter ][ 4 ] = res.getInt( "quantity" );
data[ counter ][ 5 ] = res.getInt( "onorder" );
counter++;
}
}
catch(Exception ex) {
System.err.println( "Error in constructor -> " + ex );
}
tabStockItem.setModel(new javax.swing.table.DefaultTableModel( data, columnNames ) );
}
I can see where Im going wrong , unfortunately Im out of time and cant add any extra lines of code, was hoping it was just my syntax that required an in-line fix. Thanks for the heads-up.
| 