Retrieve data from mysql db to textfield

Question

I'm trying to implement a payroll system and got some issues with it. The program needs to be able retrieve data from mysql db to jtextfield.

when I try to search I get and error saying "com.mysql.jdbc.exception.jdbc4.MySQLSyntaxErrorException. You have an error in your SQL systax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1"

package AppPackage;

import java.sql.*;
import javax.swing.*;
import java.awt.event.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class AllowanceGUI extends javax.swing.JFrame {

Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;

public AllowanceGUI() {
    initComponents();
}

private void formWindowOpened(java.awt.event.WindowEvent evt) {                                  
    conn=MySQLConnect.ConnectDb();
}                                 

private void SearchButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             


    try {
        String sql = "SELECT EmployeeID,FirstName,LastName,Designation,BasicSalary FROM EmployeeInfo WHERE EmployeeID =?";
        pst=conn.prepareStatement(sql);
        pst.setString(1,EmployeeIDSearchField.getText());

        rs = pst.executeQuery(sql);
    while(rs.next()) { 
        EmployeeIDField.setText(rs.getString("EmployeeID"));
        FirstNameField.setText(rs.getString("FirstName"));
        LasNameField.setText(rs.getString("LastName"));
        DesignationField.setText(rs.getString("Designation"));
        BasicSalaryField.setText(rs.getString("BasicSalary"));



    }
    } catch (SQLException e ) {
    JOptionPane.showMessageDialog(null, e);

}}

my db connection is as below;

package AppPackage;

import java.sql.*;
import javax.swing.*;

public class MySQLConnect {
Connection conn = null;
public static Connection ConnectDb(){

    try{
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://Localhost/easypay","root","");
    return conn;
    }catch(Exception e){
    JOptionPane.showMessageDialog(null, e);
    return null;

    }
}
}

can anybody please help me with this code?

What is the specific problem? What have you tried? Is there an error? — MeanGreen
– MeanGreen, Commented May 11, 2015 at 7:40
when I try to search I get and error saying "com.mysql.jdbc.exception.jdbc4.MySQLSyntaxErrorException. You have an error in your SQL systax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1" — P. K. Tharindu
– P. K. Tharindu, Commented May 11, 2015 at 8:07

P. K. Tharindu · Accepted Answer · 2015-05-11 15:47:27Z

Finally found the error.

private void SearchButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             


    try {
        String sql = "SELECT EmployeeID,FirstName,LastName,Designation,BasicSalary FROM EmployeeInfo WHERE EmployeeID =?";
        pst=conn.prepareStatement(sql);
        pst.setString(1,EmployeeIDSearchField.getText());

        rs = pst.executeQuery();
    if(rs.next()) { 
        String ID = rs.getString("EmployeeID");
        EmployeeIDField.setText(ID);
        String FN = rs.getString("FirstName");
        FirstNameField.setText(FN);
        String LN = rs.getString("LastName");
        LasNameField.setText(LN);
        String Des = rs.getString("Designation");
        DesignationField.setText(Des);
        String BS = rs.getString("BasicSalary");
        BasicSalaryField.setText(BS);

    }
    } catch (SQLException e ) {
    JOptionPane.showMessageDialog(null, e);

}

}

Tschallacka · Accepted Answer · 2015-05-11 08:57:23Z

5

you are doing

   pst=conn.prepareStatement(sql);// Prepare this sql query for me
    pst.setString(1,EmployeeIDSearchField.getText());// Attach this value as first parameter
    rs = pst.executeQuery(sql);// IGNORE EVERYTHING, Execute this query

you should do pst.executeQuery(); on the third line instead.

EDIT

Totally read over the fact it was a select. you should just do executeQuery without the string query. P.s. for greater speed use column indexes(integers) instead of column names in your rs.next() loop.

edited May 11, 2015 at 8:57

answered May 11, 2015 at 8:31

Tschallacka

28.9k15 gold badges100 silver badges140 bronze badges

3 Comments

P. K. Tharindu Over a year ago

now it says can not issue executeUpdate() for SELECTs

P. K. Tharindu Over a year ago

made the edits but still not showing the outputs in the fields when I click search button. I don't see the error anymore thought.

Tschallacka Over a year ago

Does your query yield results? Can you print them to System.out? if your query returns no results, it won't do anything obviously

Collectives™ on Stack Overflow

Retrieve data from mysql db to textfield

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related