Fetching data from database but not filling text boxes with correct data in Java
<p>I'm creating a bank application where I can use the search text field to find customers in the database(I'm using MySQL database). When I type in a letter to search a customer by its last name, the application does fetches the data from the database and shows the results in a JList. In my case, I have a total of 5 records in my database, which 4 last names starts with the letter "S" and one starts with the letter "M". Once the rows or results are in the JList, I can click on one row and automatically fills the first name, last name, city, and etc text filds with the proper information. However, there are two issues that I'm having:</p>

<ol>
<li><p>In my case, if I search a last name by the letter "S," my JList will show only 4 records from the database, which is correct. When I select one row from the JList, it should automatically fills all the text fields with the proper information. However, if I select the first row in the JList, my text fields will always fill with the information with the last row in the JList. It doesn't matter which row I select in the JList, the text fields will alway fills the information with the last row.</p></li>
<li><p>If I search the letter "S", I get 4 records, which is correct. However, when the application is still running, and I decided to search the letter "M," I get one record, which is also correct, but if I try to search the letter "S" again, I get only one record. Why? It should be 4.</p></li>
</ol>

<p>This what I'm trying to accomplish:
<a href="http://www.google.com/imgres?q=java%20bank%20application&amp;hl=en&amp;biw=1093&amp;bih=554&amp;gbv=2&amp;tbm=isch&amp;tbnid=EpMYIv_UsCFERM%3a&amp;imgrefurl=http://docs.oracle.com/javaee/5/tutorial/doc/bncna.html&amp;docid=twBiK2zlYRjZ1M&amp;imgurl=http://docs.oracle.com/javaee/5/tutorial/doc/figures/dukesbank-appClient.gif&amp;w=551&amp;h=642&amp;ei=XkO5T72vL4Si8gT95MCRCg&amp;zoom=1" rel="nofollow">Bank Application link</a></p>

<p>Here is part of my code:</p>

<pre><code>    // function that will search for records in the database
    private void searchRecord(){
        try {

            connect = DriverManager.getConnection(url,user,password);
            SQLStatement = connect.createStatement();
            Class.forName("com.mysql.jdbc.Driver");
            model.clear();
            String select = "SELECT * FROM customerinfo WHERE LastName LIKE '"+ txtSearch.getText().trim() +"%'";
            rows = SQLStatement.executeQuery(select);

            while(rows.next()){
                //model.addElement(rows.getString("LastName"));
                model.addElement(rows.getString("LastName") + ", " + rows.getString("FirstName") + " " + rows.getString("MiddleInitial") + ".");
            }

            rows.close();
            SQLStatement.close();
            connect.close();
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(this, "Where is your Mysql JDBC Driver?");
            e.printStackTrace();
        } catch (SQLException e){
            JOptionPane.showMessageDialog(this, "SQLException: " + e.getMessage());
            JOptionPane.showMessageDialog(this, "VendorError: " + e.getErrorCode());
        }

    }

    // Implement Action Listener
    private class handler implements ActionListener{

        @Override
        public void actionPerformed(ActionEvent event) {
            // if user clicks on New Customer Button, do the following...
            if(event.getSource() == newCustomer){
                checkForEmptyFields();
                insertDatabase();
                clearFields();
            } else if(event.getSource() == update){
                checkForEmptyFields();
                updateDatabase();
                clearFields();
            } else if(event.getSource() == remove){
                checkForEmptyFields();
                deleteRecord();
                clearFields();
            } else if(event.getSource() == cancel){
                clearFields();
            } else if(event.getSource() == open){

            } else if(event.getSource() == search){
                searchRecord();
            } 

        }
    }

    // function that will set the text fields
    private void setTextFields(int customerID, String firstName, String lastName, String middleInitial, String street, String city, String state, int zipCode, int phone, String email){
        String convertCustomerID = Integer.toString(customerID);
        txtCustomerID.setText(convertCustomerID);
        txtFirstName.setText(firstName);
        txtLastName.setText(lastName);
        txtMiddleInitial.setText(middleInitial);
        txtStreet.setText(street);
        txtCity.setText(city);
        txtState.setText(state);
        String convertZipCode = Integer.toString(zipCode);
        txtZip.setText(convertZipCode);
        String convertPhone = Integer.toString(phone);
        txtPhone.setText(convertPhone);
        txtEmail.setText(email);
    }

    // Implement List Selection Listener
    private class listener implements ListSelectionListener{
        @Override
        public void valueChanged(ListSelectionEvent event) {
                    try {
                        connect = DriverManager.getConnection(url,user,password);
                        SQLStatement = connect.createStatement();
                        String select = "SELECT * FROM customerinfo WHERE LastName LIKE '"+ txtSearch.getText().trim() + "%'";
                        rows = SQLStatement.executeQuery(select);

                        if(!event.getValueIsAdjusting()){

                                    while(rows.next()){
                                        setTextFields(rows.getInt("CustomerID"), rows.getString("FirstName"), rows.getString("LastName"), rows.getString("MiddleInitial"),
                                            rows.getString("Street"), rows.getString("City"), rows.getString("State"), rows.getInt("ZipCode"), rows.getInt("Phone"),
                                            rows.getString("Email"));
                                    }

                            }

                    } catch(Exception ex) {
                        JOptionPane.showMessageDialog(null, "Cannot get field values");
                    }
        }

    }
</code></pre>

<p>It has to be the rows.next() that's giving me issue number 1. The rows.next() goes through all the records in the database, and after it is done going through all the records, it seems it stays at the last record. Somehow, I need to reset the position, and when I click on a row in the JList, it should be equal to the row that has the same values and fill all the text fields with the proper information. </p>

<p>I have tried everything, and at this point I have given up! Please help me! Thank you!</p>
