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

[Dumisan]

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

 Statement stmt=dB.createStatement();
            results=stmt.executeQuery("SELECT name,credits FROM Studenti WHERE "
                    +"id_number='"+i+"'");

the i variable :

 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

[FON]

Does this work:

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?

[Dumisan]

i'll work with it let's see what will happen
thanks

[Dumisan]

it seens i should put a short version of the program i'm working on, so here is the 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

[FON]

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

String query = "SELECT Nume FROM Studenti WHERE nr_matricol=' i ' ";

Try this:

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 ;)

[Dumisan]

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

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

[Aseem]

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