Java MySQL Select Statement Query

[Pita]

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

[Tolls]

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.

[Pita]

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?

[Tolls]

Then show what you did with the PreparedStatement (I suspect you still concatenated the SQL String).

[Pita]

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

[Tolls]

The '?' is the bind variable placeholder.
You only have 1.
SO why are you trying to bind 4 things?

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?

[Pita]

The '?' is the bind variable placeholder.
You only have 1.
SO why are you trying to bind 4 things?

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?

[Tolls]

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:

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.