Results 1 to 8 of 8
  1. #1
    Pita is offline Member
    Join Date
    Mar 2012
    Posts
    4
    Rep Power
    0

    Default Java MySQL Select Statement Query

    Hi, i have written a code, which is giving me an error message saying that: Unknown column 'B142' in 'where clause'. B142 is by the way a MockID. Basically what i am trying to do is, i have a java program and a database. when a mock id is intered into a field and submit button is pressed, some records from the datbaase related to the given mock id shall be retrieved and printed into a text arae. this is the code that i have written:



    JButton button = new JButton("Submit");
    button.addActionListener(new ActionListener() {
    @SuppressWarnings("unused")
    public void actionPerformed(ActionEvent arg0) {


    String mockId = textField.getText();


    try {

    Statement st = con.con.createStatement();
    ResultSet rs = st.executeQuery("SELECT MockID, Subject, Year, Date FROM mockexam WHERE MockID ="+textField.getText());

    int recordCount = 0;


    while ( rs.next() ) {
    String mockID = rs.getString("MockID");
    String subject = rs.getString("Subject");
    String year = rs.getString("Year");
    String date = rs.getString("Date");
    textArea.append("Mock ID: "+ " " + mockID + " - " + "Subject: " + " " + subject + " - " + "Year: " + " " + year + " - " + "Date: " + " " + date + "\n");
    recordCount++;
    }



    if (recordCount == 0) {
    JOptionPane.showMessageDialog(frmFindMock, "A record for the given Mock ID doesn't exist");
    }}

    catch (SQLException e) {
    JOptionPane.showMessageDialog(frmFindMock, "Mock Exam couldn't be found. Please try again!");
    e.printStackTrace();
    }
    }
    });
    button.setBounds(303, 60, 75, 23);
    panel_1.add(button);

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default Re: Java MySQL Select Statement Query

    Because you are making the classic mistake of not using a PreparedStatement and binding your variables in, resulting in your concatenation missing out on quotes and MySQL thinking your text is actually a column name.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    Pita is offline Member
    Join Date
    Mar 2012
    Posts
    4
    Rep Power
    0

    Default Re: Java MySQL Select Statement Query

    Quote Originally Posted by Tolls View Post
    Because you are making the classic mistake of not using a PreparedStatement and binding your variables in, resulting in your concatenation missing out on quotes and MySQL thinking your text is actually a column name.
    Where am I missing the Quotes? I have tried the PreparedStatement too, but it was giving me errors too?

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default Re: Java MySQL Select Statement Query

    Then show what you did with the PreparedStatement (I suspect you still concatenated the SQL String).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    Pita is offline Member
    Join Date
    Mar 2012
    Posts
    4
    Rep Power
    0

    Default Re: Java MySQL Select Statement Query

    Quote Originally Posted by Tolls View Post
    Then show what you did with the PreparedStatement (I suspect you still concatenated the SQL String).
    JButton button = new JButton("Submit");
    button.addActionListener(new ActionListener() {
    @SuppressWarnings("unused")
    public void actionPerformed(ActionEvent arg0) {


    String mockId = textField.getText();

    try {

    String sql = "SELECT MockID, Subject, Year, Date FROM mockexam WHERE MockID =?";

    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "MockID");
    prest.setString(2, "Subject");
    prest.setString(3, "Year");
    prest.setString(4, "Date");


    prest.executeQuery();
    textArea.append("Mock ID, Subject, Year, Date");
    JOptionPane.showMessageDialog(frmFindMock, "Record has been updated.");


    }

    catch (SQLException e) {
    //System.out.println("Record couldn't be added!");
    e.printStackTrace();
    JOptionPane.showMessageDialog(frmFindMock, "Record couldn't be updated. Please try again.");
    }
    }

    });

    button.setBounds(303, 60, 75, 23);
    panel_1.add(button);

    The error message is: Parameter index out of range (2 > number of parameters, which is 1).

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default Re: Java MySQL Select Statement Query

    The '?' is the bind variable placeholder.
    You only have 1.
    SO why are you trying to bind 4 things?
    Java Code:
    prest.setString(1, "MockID");
    prest.setString(2, "Subject");
    prest.setString(3, "Year");
    prest.setString(4, "Date");
    Surely you just want to bind the value of the textField into it?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    Pita is offline Member
    Join Date
    Mar 2012
    Posts
    4
    Rep Power
    0

    Default Re: Java MySQL Select Statement Query

    Quote Originally Posted by Tolls View Post
    The '?' is the bind variable placeholder.
    You only have 1.
    SO why are you trying to bind 4 things?
    Java Code:
    prest.setString(1, "MockID");
    prest.setString(2, "Subject");
    prest.setString(3, "Year");
    prest.setString(4, "Date");
    Surely you just want to bind the value of the textField into it?
    Well I want 4 specific information to be displayed? which part of the code would i have to change? because I need the Mock ID, Subject, Year and Date to the specific MockID entered in the text field to appear as results in a textArea?

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default Re: Java MySQL Select Statement Query

    If I have a query:
    SELECT * FROM sometable WHERE some_varchar_column = <some value>
    Then I would need to do this in JDBC, with a PreparedStatement:
    Java Code:
    String sql = "SELECT * FROM sometable WHERE some_varchar_column = ?";
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = conn.prepareStatement(sql);
        ps.setString(1, "value of some_varchar_column to search on");
        rs = ps.executeQuery();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
    // close ps and rs properly, and the connection as well if need be.
    }
    But I think you rea;;y ought to go through the tutorial.
    You shouldn't touch JDBC without having understood the tutorial.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Replies: 1
    Last Post: 06-21-2011, 04:55 PM
  2. Mysql dynamic query, Java and Jasper report
    By rajuchacha007 in forum Advanced Java
    Replies: 5
    Last Post: 06-28-2010, 10:32 AM
  3. return output from a non select query (java/access)
    By kitiaradl in forum New To Java
    Replies: 2
    Last Post: 04-21-2010, 12:04 AM
  4. return output from a non select query (java/access)
    By kitiaradl in forum New To Java
    Replies: 0
    Last Post: 04-15-2010, 09:01 PM
  5. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 05:29 PM

Tags for this Thread

Posting Permissions

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