Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

programming problems

 Could somebody help me fix the following program?
  Running the following program (payment.java), I got error messages:
           java.sql.SQLException: No Suitable driver
           at java.sql.DriverManager.getConnection(DriverManager.java:141)
           at java.sql.DriverManager.getConnection(DriverManager.java:164)
           at payment.initializeDatabase(payment.java:60)
           at payment.<init><payment.java:40)
           at payment.main(payment.java:18)

           There is nothing wrong with my system set up.
           In payment.mdb, the table "customer" includes fields:
           ID(Long)-primary key, lastName(char), and feePaid(Double).
           
                   

        //:payment.java
        import java.awt.*;
        import java.awt.event.*;
        import java.sql.*;

        public class payment extends Frame
        {
              //{{DECLARE_CONTROLS    
              Label  lbID, lbLname, lbFeePaid;
              TextField  txtID, txtLname, txtFeePaid;        

              static public void main(String args[])
              {      
             (new payment()).setVisible(true);
              }

              public payment()
              {
                    //{{INIT_CONTROLS
                    setLayout(new FlowLayout());
                    lbID = new Label("ID:");
                    add(lbID);
                    txtID = new TextField(10);
                    add(txtID);
                    lbLname = new Label("Last Name:");
                    add(lbLname);
                    txtLname = new TextField(10);
                    add(txtLname);
                    lbFeePaid = new Label("FeePaid");
                    add(lbFeePaid);    
                    txtFeePaid = new TextField(10);
                    add(txtFeePaid);
                    pack();

                    //Open up the database...
                    initializeDatabase();

                    //{{REGISTER_LISTENERS
                    this.addWindowListener(new WindowAdapter(){
                          public void windowClosing(WindowEvent e){
                            System.exit(0);}
                        });
               }

              public void initializeDatabase()
              {
                        ResultSet  rs;
                        String dbUrl = "jdbc:odbc:Project";
                        String user = "";
                        String password = "";

                        try{
                               Class.forName("sun.jdbc.JdbcOdbcDriver");
                        }
                        catch(java.lang.ClassNotFoundException e){
                              System.err.println( e.getMessage());
                        }
                        try
                       {
                            Connection c = DriverManager.getConnection(dbUrl, user, password);
                            Statement s = c.createStatement();
                            rs = s.executeQuery( "select * from customer " +
                                                 "where lastName = 'Challenger'" );
                            displayResults( rs );
                         }
                         catch ( Exception e )
                         {
                            e.printStackTrace ();
                        }
             }
                 
            public void displayResults( ResultSet rs )
                     throws SQLException
            {                
                  txtID.setText(rs.getLong( "ID" ) );  /**I'm not sure this code*/
                  txtLname.setText( rs.getString( "lastName" ) );
                  txtFeePaid.setText(rs.getDouble( "feePaid" ) ); /**I'm not sure this code*/
            }
        }
0
CG
Asked:
CG
  • 6
  • 6
  • 2
1 Solution
 
aziz061097Commented:
I got the same error when I tried a JDBC project in IE. I fixed it by using com.ms.jdbc.JdbcOdbcDriver instead of  sun.jdbc.JdbcOdbcDriver and also I made a cab of all files and signed the cab.
0
 
CGAuthor Commented:
aziz:
Sorry, your answer can not solve my problems. I don't understand your idea here
(I fixed it by using com.ms.jdbc.JdbcOdbcDriver instead of  sun.jdbc.JdbcOdbcDriver
and also I made a cab of all files and signed the cab. ).
Also, you did not tell me whether the following codes are right:
     txtID.setText(rs.getLong( "ID" ) );  /**ID data type is long*/
     txtFeePaid.setText(rs.getDouble( "feePaid" ) ); /**feePaid data type is double*/
0
 
aziz061097Commented:
The error

           java.sql.SQLException: No Suitable driver
           
seems to be a connection problem . I think your SQL is still not reached. I had a JDBC program that worked perfectly in appletviewer but when I tried to run it in IE4.0 it gave this same error. In that case I used com.ms.jdbc.JdbcOdbcDriver instead of  sun.jdbc.JdbcOdbcDriver  . Are you able to run in appletviewer at all ??
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CGAuthor Commented:
Hi, aziz:
Thanks for your quick response. Because I am beginner in java program, I don't know
what the IE4.0 is, and also don't know how to write appletviewer. I used to use sun.jdbc.
JdbcOdbcDriver. I don't know com.ms.jdbc.JdbcOdbcDriver. If you can rewrite my program
(run successfully) and tell me detail how I need do, I will give your extra points.
By the way, my pragram must run under DOS.
0
 
aziz061097Commented:
Your program looks perfect .... Although I did not have time to test it by creating a DSN in ODBC . I suspect that the creation of ODBC was not correct and hence you are getting a connection time error.

if you open 32 bit odbc you will find several tabs

DSN means Data Source Name , The User DSN means these will be specific to the user who has logged in on that machine. System DSN means these will appear if any user is logged in , so it is better to create System DSNs .

For creating a MS Access DSN follow the following steps
1. Select System DSN tab
2. Click on the add button
3. You will find a list of ODBC drivers(if installed )
4. Select Microsoft Access and click Finish
5. You will get a dialog , in the "Data Source Name" enter the name you want to call it (this is the name you will have to specify in your java program while doing a JDBC connection)
6. Click the Select button and you will get a File Dialog , select the .mdb file which has your Database. If you do not find the .mdb file you can do a find on Explorer to find *.mdb file.
7. Your DSN is now set and you can use this to connect to Access from a Java program. (Northwind.mdb is a sample installed by MS Access of Office 97)


Here is a complete JDBC program if you need , although yours seems to be perfect. (use it for reference)


/* JdbcSample.java
 * Illustrates an example of the JDBC.
 *
*/

import java.sql.*;
class JdbcSample {

    static Connection con; // database connection object

    /** Main routine */
    public static void main(String args[]) throws Exception {

        // Load the JDBC-ODBC bridge driver
        Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

        // Open the database and display all rows in a table
        open();
        select();

        // Add a new row and show the result insert();
        select("WHERE EmployeeId = 100");

        // Update a row and commit the result
        update(true);
        select("WHERE EmployeeId = 100");

        // Update a row and rollback the result
        update(false);
        select("WHERE EmployeeId = 100");

        // Delete a row and show the result
        delete();
        select("WHERE EmployeeId = 100");

        // Close the database
        close();

    }

    /** Open a database connection */
    static void open() throws SQLException {

        // ODBC data source name
        String dsn = "jdbc:odbc:Northwind";
        String user = "admin";
        String password = "";

        // Connect to the database
        con = DriverManager.getConnection(dsn, user, password);

        // Shut off autocommit
        con.setAutoCommit(false);

    }

    /** Commit all pending transactions and close the database connection */
    static void close() throws SQLException {

        con.commit();
        con.close();

    }

    /** Issue a SQL query with a WHERE clause */
    static void select(String whereClause) throws SQLException {

        Statement stmt; // SQL statement object
        String query;   // SQL select string
        ResultSet rs;   // SQL query results
        boolean more;   // "more rows found" switch

        query = "SELECT EmployeeID, LastName, FirstName, Title "
              + "FROM Employees "
              + whereClause;

        stmt = con.createStatement();
        rs = stmt.executeQuery(query);

        // Check to see if any rows were read
        more = rs.next();
        if (!more) {

            System.out.println("No rows found.");
            return;

        }

        // Loop through the rows retrieved from the query
        while (more) {

            System.out.println("ID: " + rs.getInt("EmployeeId"));
            System.out.println("Name: " + rs.getString("FirstName") + " " + rs.getString("LastName"));
            System.out.println("Title: " + rs.getString("Title"));
            System.out.println("");
            more = rs.next();

        }

        rs.close();
        stmt.close();

    }

    /** Issue a SQL query without a WHERE clause */
    static void select() throws SQLException {

        select("");

    }

    /** Insert a new row */
    static void insert() throws SQLException {

        Statement stmt; // SQL statement object
        String sql;     // SQL insert command
        int rows;       // Number of rows inserted

        sql = "INSERT INTO Employees " 
            + "VALUES (100, 'Alexander', 'Jason', 'Baseball Manager', " 
            + "NULL, NULL, NULL, NULL, NULL, "
            + "NULL, NULL, NULL, NULL, NULL, "
            + "NULL, NULL, NULL)";

        stmt = con.createStatement();
        rows = stmt.executeUpdate(sql);
        con.commit();
        stmt.close();

        System.out.println(rows + " row(s) added");

    }

    /** Update a new row */
    static void update(boolean commit) throws SQLException {

        Statement stmt; // SQL statement object
        String sql;     // SQL update command
        int rows;       // Number of rows inserted
        String title;   // New title for the update

        if (commit)
           title = "Goof Off";
        else
           title = "President";

        sql = "UPDATE Employees " 
            + "SET Title='" + title + "' " 
            + "WHERE EmployeeId = 100";

        stmt = con.createStatement();
        rows = stmt.executeUpdate(sql);

        if (commit)
           con.commit();
        else
           con.rollback();

        stmt.close();
        System.out.println(rows + " row(s) updated");

    }

    /** Delete a row */
    static void delete() throws SQLException {

        Statement stmt; // SQL statement object
        String sql;     // SQL delete command
        int rows;       // Number of rows inserted

        sql = "DELETE FROM Employees " 
            + "WHERE EmployeeId = 100";

        stmt = con.createStatement();
        rows = stmt.executeUpdate(sql);
        con.commit();
        stmt.close();

        System.out.println(rows + " row(s) deleted");

    }

}
0
 
aziz061097Commented:
One thing I forgot to tell you :

You will have to convert the Long and Double to String before you set them to TextFields . For doing that see the API for Double under java.lang .

for example:

     txtFeePaid.setText(Double.toString(rs.getDouble( "feePaid" )) );


Double.toString(double) returns a String that can be set to TextField. Similarly every datatype has a toString().
0
 
CGAuthor Commented:
aziz:
I'm very pleasure what you have done for me. Actually, I set up my system exactly
the same way as what you said. Also I find your program has the same method
to connect the database. I still can not figure out problems in my program.

0
 
thresholdCommented:
answer:
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

not
 Class.forName("sun.jdbc.JdbcOdbcDriver");
0
 
thresholdCommented:
Did you add DSN "Project" into ODBC?
0
 
CGAuthor Commented:
threshold:
I'm glad to get your help. I just added "Project" in System DSN. Now I add it to
User DSN too (If not necessary, how to delete it?). After I changed the code
1. from
        Class.forName("sun.jdbc.JdbcOdbcDriver");
    to
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2. from
          txtID.setText(rs.getLong( "ID" ) );  
          txtLname.setText( rs.getString( "lastName" ) );
          txtFeePaid.setText(rs.getDouble( "feePaid" ) );
    to
         txtID.setText(Integer.toString (rs.getInt( "ID" ) ));  
         txtLname.setText( rs.getString( "lastName" ) );
         txtFeePaid.setText(Double.toString(rs.getDouble( "feePaid" ) ) );  

I got another error messages in the run time:
    java.sql.SQLException:[Microsoft][ODBC Driver Manager] Invalid cursor state
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4095)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:252)
    at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(JdbcOdbc.java:1814)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(JdbcOdbcResultSet.java:1709)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:392)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:410)
    at payment.displayResults(payment.java:75)
    at payment.initializeDatabase(payment.java:64)
    at payment.<init>(payment.java:36)
    at payment.main(payment.java:14)
0
 
CGAuthor Commented:

aziz:
According on your program, I have changed my program (see following codes).
Unfortunately, I got compiler error messages:
    Can't make a static reference to nonstatic variable txtID in class payment
                txtId.setText(Integer.toString(rs.getInt("ID")));
Neither to txtLname and txtFeePaid.
So, I don't know whether it will run successfully.

//:payment.java
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class payment extends Frame
{
      //{{DECLARE_CONTROLS  
      Label  lbID, lbLname, lbFeePaid;
      TextField  txtID, txtLname, txtFeePaid;    
   
      static Connection c;

      static public void main(String args[])throws Exception
      {    
           (new payment()).setVisible(true);
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
             String dbUrl = "jdbc:odbc:Project";
             String user = "";
             String password = "";
             c = DriverManager.getConnection(
                     dbUrl, user, password);
             c.setAutoCommit(false);
             initializeDatabase("where ID= 10");
             close();
      }

      public payment()
      {      
            //{{INIT_CONTROLS
            setLayout(new FlowLayout());
            lbID = new Label("ID:");
            add(lbID);            
            txtID = new TextField(10);
            add(txtID);            
            lbLname = new Label("Last Name:");
            add(lbLname);            
            txtLname = new TextField(10);
            add(txtLname);            
            lbFeePaid = new Label("FeePaid");
            add(lbFeePaid);  
            txtFeePaid = new TextField(10);
            add(txtFeePaid);
            pack();
           
            //{{REGISTER_LISTENERS
            this.addWindowListener(new WindowAdapter(){
                  public void windowClosing(WindowEvent e){
                    System.exit(0);}
                });
      }
      
      static void initializeDatabase(String whereClause)throws SQLException
      {
        ResultSet  rs;
                Statement s;
                String query;
                             
        query="select * from customer " +
              whereClause;
               
        s=c.createStatement();
                rs=s.executeQuery(query);
                       
                txtID.setText( Integer.toString(rs.getInt("ID")) );
                txtLname.setText(rs.getString("lastName"));
                txtFeePaid.setText(Double.toString( rs.getDouble("feePaid")) );  
                 
                rs.close();
                s.close();
      }

     static close() throws SQLException{
                 c.commit();
                c.close();
    }
}
   
















0
 
aziz061097Commented:
Three modifications in your program

1. Make the TextFields static (because you are not accessing the instances fields)

 static TextField  txtID, txtLname, txtFeePaid;    
           
2.  Put rs.next() after rs.executeQuery()

   rs=s.executeQuery(query);
                     rs.next();          
                     
3. The close method declaration has an error make the return type void

static void close() throws SQLException{
0
 
CGAuthor Commented:
aziz, thank you for your big help. My points for you.
It will be nice if you can tell me why need put rs.next() after rs.executeQuery().
Do the following codes have the same affect?
   if(rs.next())
  {  txtID.setText( Integer.toString(rs.getInt("ID")) );
     txtLname.setText(rs.getString("lastName"));
     txtFeePaid.setText(Double.toString( rs.getDouble("feePaid")) );  
  }

0
 
aziz061097Commented:
The rs.next() will bring the next record from the result set that is obtained from the execution of the query. Yes you can check if rs.next() returns any values before assigning the values to the text fields .
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now