MS ACCESS SQL statement help!

[low1988]

i am creating a bank application with connection of MS ACCESS Database which is already construct a table with the following statement

Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE ACCOUNT " +
"(AccName VARCHAR(32), AccNum INTEGER, Deposit FLOAT, " +
"Balance FLOAT, Withdraw FLOAT)");

So everything works fine ,the table was constructed successfully and i can create a record in the table ,but the problem is i cannot validate the record that exist in the table.The following code state the matter :

else if(source == signinButton)
                        {
                         String accname = JOptionPane.showInputDialog(null,"Please enter the Account Username"); 
                         String accnum = JOptionPane.showInputDialog(null,"Please enter the Account Number");
                         int number = Integer.parseInt(accnum);
                        try
				{
					st1.close();                                                             //close connection with Database				
					st1=con.createStatement();                                               //open connection with database
					rs1 =st1.executeQuery("Select * from ACCOUNT where AccNum like '"+number+"'");  //SQL statement
					//rs1.next(); //moves the cursor forward one row
					//txtA1.setText(rs1.getString("AccName"));
					//txtA2.setText(rs1.getString("AccNum"));
                                       // txtTextArea.append("Account Holder's Name :" + rs1.getString("AccName") + "\n");
                                       // txtTextArea.append("Account Number :" + rs1.getString("AccNum") + "\n");
				}
			catch(Exception e1)
				{
					JOptionPane.showMessageDialog(null,"Invalid Account Number");
				}			

                         }

It keep prompt out the" Invalid Account Number" even i insert the data correctly.i might be some problem within the line
(rs1 =st1.executeQuery("Select * from ACCOUNT where AccNum like '"+number+"'");)

[javaAddict]

First of all first print the query that you run:

String query = "Select * from ACCOUNT where AccNum like '"+number+"'";
System.out.println(query);

rs1 =st1.executeQuery(query);  //SQL statement

It will print:
Select * from ACCOUNT where AccNum like '5'

But AccNum is declared as number, but when you write '5' it treats it as char.

So you need to adjust your query to be like this:
Select * from ACCOUNT where AccNum like 5

[low1988]

i still cannot solve the problem ,i tweak some of the code now the exception of Invalid "Account Number" wont occur anymore but it seems that it also accept the data that does not exist in the database.

Please have a look on my code with a database inside the rar file

[javaAddict]

First of all next time use separate classes for database transactions:

class DBManager {
 public static int insert(..... arguments) throws SQLException {
    // open new connection
   // execute query
   // close connection
 }
}

Never do that:

Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE ACCOUNT " +
"(AccName VARCHAR(32), AccNum INTEGER, Deposit FLOAT, " +
"Balance FLOAT, Withdraw FLOAT)");

You never create the database using java code. I saw that you placed that code in the main class. Does this mean that whenever you start your application a new table will be created?
The database has to pre-exist. Create it by running the query at an sql command prompt.

This is wrong:

rs1.next(); //moves the cursor forward one row
					//txtA1.setText(rs1.getString("AccName"));
					//txtA2.setText(rs1.getString("AccNum"));
                                       //txtTextArea.append("Account Holder's Name :"+rs1.getString("AccName"));
                                       // txtTextArea.append("Account Number :" + rs1.getString("AccNum") + "\n");
                                          st1.close();

rs.next returns true or false depending if the query returned a row:

if (rs.next()) {
 // use rs.get... methods to get the info.
} else {
  // no rows returned
}

If the query returns more than one row:

while (rs.next()) {
 // use rs.get... methods to get the info.
 // put the results into a Vector
}

Also I would suggest to change this: Select * from ACCOUNT where AccName Like '"+accname+"'" to this: Select * from ACCOUNT where AccName = '"+accname+"'" Don't forget when you open a connection to close it after the transaction. From what I see you open the connection at the main and then you leave it open. Use at least separate methods where you open and close the connection immediately like at the beginning of the post

Make your changes and next time post relevant code