Solved

programming problems

Posted on 1998-07-30
14
208 Views
Last Modified: 2010-03-30
 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
Comment
Question by:CG
  • 6
  • 6
  • 2
14 Comments
 
LVL 2

Expert Comment

by:aziz061097
ID: 1229267
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
 

Author Comment

by:CG
ID: 1229268
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
 
LVL 2

Expert Comment

by:aziz061097
ID: 1229269
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
 

Author Comment

by:CG
ID: 1229270
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
 
LVL 2

Expert Comment

by:aziz061097
ID: 1229271
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
 
LVL 2

Expert Comment

by:aziz061097
ID: 1229272
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
 

Author Comment

by:CG
ID: 1229273
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Expert Comment

by:threshold
ID: 1229274
answer:
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

not
 Class.forName("sun.jdbc.JdbcOdbcDriver");
0
 
LVL 2

Expert Comment

by:threshold
ID: 1229275
Did you add DSN "Project" into ODBC?
0
 

Author Comment

by:CG
ID: 1229276
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
 

Author Comment

by:CG
ID: 1229277

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
 
LVL 2

Expert Comment

by:aziz061097
ID: 1229278
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
 

Author Comment

by:CG
ID: 1229279
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
 
LVL 2

Accepted Solution

by:
aziz061097 earned 60 total points
ID: 1229280
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now