Results 1 to 4 of 4
  1. #1
    ppreeti is offline Member
    Join Date
    Mar 2014
    Posts
    17
    Rep Power
    0

    Default error message: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data

    Hi !
    I'm getting the error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. the program is basically about dependent drop down box where one dropdown gets populated by the value given in the previous dropdown. however, the second dropdown doesn't gets populated from the database, instead generates the error message in Eclipse.
    This is my code:-

    country.jsp
    ---------------
    <%@page import="java.sql.*"%>
    <html>
    <head>
    <script language="javascript" type="text/javascript">
    var xmlHttp
    var xmlHttp
    function showState(str){
    if (typeof XMLHttpRequest != "undefined"){
    xmlHttp= new XMLHttpRequest();
    }
    else if (window.ActiveXObject){
    xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
    }
    if (xmlHttp==null){
    alert("Browser does not support XMLHTTP Request")
    return;
    }
    var url="state.jsp";
    url +="?count=" +str;
    xmlHttp.onreadystatechange = stateChange;
    xmlHttp.open("GET", url, true);
    xmlHttp.send(null);
    }

    function stateChange(){
    if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){
    document.getElementById("state").innerHTML=xmlHttp .responseText
    }
    }

    function showCity(str){
    if (typeof XMLHttpRequest != "undefined"){
    xmlHttp= new XMLHttpRequest();
    }
    else if (window.ActiveXObject){
    xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
    }
    if (xmlHttp==null){
    alert("Browser does not support XMLHTTP Request")
    return;
    }
    var url="city.jsp";
    url +="?count=" +str;
    xmlHttp.onreadystatechange = stateChange1;
    xmlHttp.open("GET", url, true);
    xmlHttp.send(null);
    }
    function stateChange1(){
    if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){
    document.getElementById("city").innerHTML=xmlHttp. responseText
    }
    }
    </script>
    </head>
    <body>
    <select name='country' onchange="showState(this.value)">
    <option value="none">Select</option>
    <%
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newI nstance();
    Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Mic rosoft Access Driver (*.mdb, *.accdb)};DBQ=" + "C:\\users\\ppreeti\\d.accdb");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("Select * from country");
    while(rs.next()){
    %>
    <option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
    <%
    }
    %>
    </select>
    <br>
    <div id='state'>
    <select name='state' >
    <option value='-1'></option>
    </select>
    </div>

    <div id='city'>
    <select name='city' >
    <option value='-1'></option>
    </select>
    </div>
    </body>
    </html>


    state.jsp
    ---------------
    <%@page import="java.sql.*"%>
    <%
    String country=request.getParameter("count");

    System.out.println(country);
    String buffer="<select name='state' onchange='showCity(this.value);'><option value='-1'>Select</option>";
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newI nstance();
    Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Mic rosoft Access Driver (*.mdb, *.accdb)};DBQ=" + "C:\\users\\ppreeti\\d.accdb");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("Select * from state where countryid='"+country+"' ");
    System.out.println("came here");
    /* ResultSet rs = stmt.executeQuery("Select * from state,country where state.countryid=country.countryid='"+country+"' "); */
    while(rs.next()){
    buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString(3)+"</option>";
    }
    buffer=buffer+"</select>";
    response.getWriter().println(buffer);
    }
    catch(Exception e){
    System.out.println(e);
    }

    %>


    city.jsp
    ----------------
    <%@page import="java.sql.*"%>
    <%
    String state=request.getParameter("count");
    String buffer="<select name='city'><option value='-1'>Select</option>";
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newI nstance();
    Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Mic rosoft Access Driver (*.mdb, *.accdb)};DBQ=" + "C:\\users\\ppreeti\\d.accdb");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("Select * from city where stateid='"+state+"' ");
    while(rs.next()){
    buffer=buffer+"<option value='"+rs.getString(2)+"'>"+rs.getString(3)+"</option>";
    }
    buffer=buffer+"</select>";
    response.getWriter().println(buffer);
    }
    catch(Exception e){
    System.out.println(e);
    }
    %>


    I have an Access database where all the tables are stored ! Kindly help me fix this ! Thanks in advance !

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    929
    Rep Power
    2

    Default Re: error message: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] D

    You are probably setting a number in the sql string with quotes, or vice versa. Create a habit of using PreparedStatements to avoid problems like this.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  3. #3
    ppreeti is offline Member
    Join Date
    Mar 2014
    Posts
    17
    Rep Power
    0

    Default Re: error message: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] D

    Thanks for pointing that out..Please help me know how can that be achieved with respect to my code !

  4. #4
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    929
    Rep Power
    2

    Default Re: error message: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] D

    Instead of this:
    Java Code:
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("Select * from city where stateid='"+state+"' ");
    you write this:

    Java Code:
    PreparedStatement stmt = con.prepareStatement( "Select * from city where stateid = ?" );
    stmt.setInt(1, state);
    ResultSet rs = stmt.executeQuery();
    As you can see, you don't have to worry about quotes and escaping special characters.

    See: Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

Similar Threads

  1. Replies: 1
    Last Post: 08-13-2013, 06:25 PM
  2. Replies: 3
    Last Post: 08-13-2013, 06:19 PM
  3. Replies: 2
    Last Post: 04-18-2012, 03:51 PM
  4. Replies: 5
    Last Post: 09-11-2011, 05:13 AM
  5. Replies: 3
    Last Post: 08-15-2011, 10:16 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
  •