Results 1 to 7 of 7
  1. #1
    Dumisan's Avatar
    Dumisan is offline Member
    Join Date
    Feb 2010
    Location
    Romania
    Posts
    30
    Rep Power
    0

    Default SQLException:java.sql.Exception:Data type mismatch in criteria expresion

    hi guys
    i'm working on a beginner datebase program that tackes date from an interface and add them or search in database. the adding part works but when i try to interogate the datebase i get this:
    SQLException:java.sql.SQLException:[Microsof][odbc Microsoft Access Driver] Data Type mismatch in criteria expresion

    i'm useing an Access datebase (i'm new and it is easy to set up the driver)
    the table Student has 3 fields : id_number(primary key): Number, name:Text,
    and credits:Number

    this is the cod
    Java Code:
     Statement stmt=dB.createStatement();
                results=stmt.executeQuery("SELECT name,credits FROM Studenti WHERE "
                        +"id_number='"+i+"'");
    the i variable :
    Java Code:
     String s1=jTextField1.getText();
     int i=Integer.parseInt(s1);
    i used the NetBeans design mod to create the frame
    hope is enought
    if someone know what's wrong help me please
    silence i'm trying to meditate:p

  2. #2
    FON
    FON is offline Senior Member
    Join Date
    Dec 2009
    Location
    Belgrade, Serbia
    Posts
    366
    Rep Power
    6

    Default

    Does this work:


    Java Code:
    int i = 3;
    		
    String sql = "SELECT name,credits FROM Studenti WHERE id_number = " + i;
    		
    System.out.println(sql);
    Maybe it just ' ' sufficient on i.

    Tip:
    Use java.sql.PreparedStatement class.
    When you set input param on '?' in prepared statement (yours int i ),
    do use debugger to see how actual SQL query looks like in runtime before execution, so you can copy this query directly in SQL editor in Access, Mysql...

    are u ok with this?

  3. #3
    Dumisan's Avatar
    Dumisan is offline Member
    Join Date
    Feb 2010
    Location
    Romania
    Posts
    30
    Rep Power
    0

    Default

    i'll work with it let's see what will happen
    thanks
    silence i'm trying to meditate:p

  4. #4
    Dumisan's Avatar
    Dumisan is offline Member
    Join Date
    Feb 2010
    Location
    Romania
    Posts
    30
    Rep Power
    0

    Default

    it seens i should put a short version of the program i'm working on, so here is the code:
    Java Code:
    mport java.sql.*;
    
    public class conectare_db {
    
        public static void main(String args[]) throws SQLException {
            int i;
            Connection con = null;
            System.out.println("Give the number:");
            java.util.Scanner in = new java.util.Scanner(System.in);
            i = in.nextInt();
            in.close();
            String url = "jdbc:odbc:Facultate";
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            } catch (ClassNotFoundException e) {
                System.out.println(e);
            }
            try {
                con = DriverManager.getConnection(url, "", "");
            } catch (SQLException e) {
                System.out.println(e);
            }
    
            String query = "SELECT Nume FROM Studenti WHERE nr_matricol=' i ' ";
    
            try {
                Statement stm = con.createStatement();
                ResultSet results = stm.executeQuery(query);
                while (results.next()) {
                    String nm = results.getString("Nume");
                    System.out.println(" name: " + nm);
                }
                stm.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }
    }
    this is the output:
    Give the number:
    13
    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in query. Incomplete query clause.
    BUILD SUCCESSFUL (total time: 7 seconds)


    here is the content of the Studenti table:

    nr_matricol // nume // credite
    12 // nume1 // 40
    13 // nume2 // 45
    .........................................
    i hope now will make more sense for you :cool:

    i have tryed some sql on a wampserver and i get to this new developement ....... but still an error
    Last edited by Dumisan; 02-20-2010 at 08:39 PM.
    silence i'm trying to meditate:p

  5. #5
    FON
    FON is offline Senior Member
    Join Date
    Dec 2009
    Location
    Belgrade, Serbia
    Posts
    366
    Rep Power
    6

    Default

    Well friend you have not done anything i suggested to you :)

    Java Code:
    String query = "SELECT Nume FROM Studenti WHERE nr_matricol=' i ' ";
    Try this:
    Java Code:
    String query = "SELECT Nume FROM Studenti WHERE nr_matricol = " + i;
    And go to that debugger!

    More tips:
    -Never ever write entire code in that main().
    Write you own methods and then create instance of your class in main()
    and let instance call them.

    - learn how to use finally{} to close your resources - like Connection, Statetments...

    - split that code in more methods that can be reusable : one method to get Connection, one method to to some work with it, and method to close resources.

    - don't write so many try catch blocks - write one bug try block with many catch blocks in order form more specific to less specific

    Now spend some time on this and post back improved version that really works

    come on Dracula we all know you can really bite ;)

  6. #6
    Dumisan's Avatar
    Dumisan is offline Member
    Join Date
    Feb 2010
    Location
    Romania
    Posts
    30
    Rep Power
    0

    Default

    thanks a lot actualy that was the problem still don;t understand why my query didn;t worked ....
    here is the code with only one try and 2 catchs it's working now
    Java Code:
    import java.sql.*;
    
    public class conectare_db {
    
        public static void main(String args[]) throws SQLException {
            int i;
            Connection con = null;
            Statement stm = null;
            System.out.println("Give the number:");
            java.util.Scanner in = new java.util.Scanner(System.in);
            i = in.nextInt();
            in.close();
            String url = "jdbc:odbc:Facultate";
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                con = DriverManager.getConnection(url, "", "");
                String query = "SELECT Nume, credite FROM `Studenti`WHERE nr_matricol = " + i;
                stm = con.createStatement();
                ResultSet results = stm.executeQuery(query);
                while (results.next()) {
                    String nm = results.getString("Nume");
                    String crd = results.getString("credite");
                    System.out.println(" name: " + nm + " credit: " + crd);
                }
    
            } catch (SQLException e) {
                System.out.println(e);
            } catch (ClassNotFoundException e) {
                System.out.println(e);
            } finally {
                stm.close();
                con.close();
            }
        }
    }
    about methods this is actualy 1 method on my initial project select some values form a table and add them on an other table :)

    P.S.
    The legend of Vlad Dracul(Dracla) comes from Transilvania somewhere in NV of the country and i'm staying on the S thou i'd love to stay there is more pacefull :D
    silence i'm trying to meditate:p

  7. #7
    Aseem is offline Senior Member
    Join Date
    Mar 2009
    Location
    USA
    Posts
    127
    Rep Power
    0

    Default

    when u get any errors/ exceptions...copy the error message and paste it in google. You will get the answer.

Similar Threads

  1. Unreported exception java.sql.SQLException
    By javamula in forum AWT / Swing
    Replies: 4
    Last Post: 09-29-2009, 03:32 PM
  2. Replies: 1
    Last Post: 10-16-2008, 03:09 PM
  3. Replies: 1
    Last Post: 03-08-2008, 07:41 PM
  4. Replies: 2
    Last Post: 11-16-2007, 02:24 PM
  5. Replies: 1
    Last Post: 07-24-2007, 12:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •