MySQL error: a column not found?

Question

So I have this code right here:

try {
            rs = stmt.executeQuery("SELECT * FROM systemaccount where ID = '"+getValue+"'");
            while(rs.next())
            {
                fname.setText(rs.getString("First_Name"));
                mname.setSelectedItem(rs.getObject("Middle_name"));
                lname.setText(rs.getString("Last_Name"));
                address.setText(rs.getString("address"));
                contact.setText(rs.getString("Contact_Number"));
                user.setText((String) getValue((Integer) rs.getObject("User"), "user"));
                usertype.setSelectedItem(getValue((Integer) rs.getObject("user_type"), "user_type")); //<-----THE ERROR IS HERE
            }
        } catch (SQLException ex) {
            Logger.getLogger(SystemAccount.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        }

So basically what my code does is that when I double clicked on the jTable it automatically populates the ComboBox and TextFields as well. But MySQL throws an error that it doesn't find any user_type column name.

As you can see below I have proof that it has a column name named user_type.

systemaccount table having user_type column

and I always get

java.sql.SQLException: Column 'user_type' not found. error on the line usertype.setSelectedItem(getValue((Integer) rs.getObject("user_type"), "user_type"));

any hint on what's going on?

I guess no one knows the solution. What cause it? and how to solve it? — rochiey, Oct 12 '15 at 11:59

score 0 · Answer 1 · answered Oct 12 '15 at 12:27

0

Try it with some different column name (other than "user_type").

answered Oct 12 '15 at 12:27

Sanjay

61
5

I though about this also as my worst-case-scenario/last resort option. :( – rochiey Oct 12 '15 at 12:28
Try your worst-case-scenario. If it works then problem is with mysql, else check complete exception stack trace. It may help. – Sanjay Oct 12 '15 at 12:39
I tried to change it into usertype. it still doesn't find the column but the resultsetmetadata recognizes it. :( – rochiey Oct 12 '15 at 12:41
Try your code without accessing a user_type field. Check if your code or column name in mysql table is having any leading or trailing space (s). – Sanjay Oct 12 '15 at 12:59
if ResultSetMetaData works, at the last you have to go with it. – Sanjay Oct 12 '15 at 13:01
You said in your comment "resultsetmetadata recognizes it" with usertype column. – Sanjay Oct 12 '15 at 13:12
do you mean i should run my code like this? `usertype.setSelectedItem(getValue((Integer) rs.getObject(meta.getColumnName(8)), "usertype"));` ? It said column 'usertype' not found :( – rochiey Oct 12 '15 at 13:15
Have you printed all column names using resultsetmetadata using code given by @karim mohsen. Check if it prints "usertype" column name. – Sanjay Oct 12 '15 at 13:22
YES that's why i told you ResultSetMetaData recognizes it, but when I tried to retrieve it alwas says 'usertype' column not found. – rochiey Oct 12 '15 at 13:27

Sanjay · Accepted Answer · 2015-10-12T14:06:50.600

0

Create array of Objects.

ResultSet results = myStatement.executeQuery("SELECT * FROM systemaccount where ID = "+getValue);

    ResultSetMetaData meta = results.getMetaData();
    while (results.next()) {
      Object[] data = new Object[meta.getColumnCount()];
      for (int index = 1; index <= data.length; index++) {
          data[index - 1] = results.getObject(meta.getColumnName(index));
          System.out.println("Column " + index + " is named " + meta.getColumnName(index) + " having value " + data[index - 1]);
      }
      // fname.setText(data[0].toString());
      ...
    }

edited Oct 12 '15 at 14:06

answered Oct 12 '15 at 13:35

Sanjay

61
5

Iterate over this array to save database values to variables. – Sanjay Oct 12 '15 at 13:41
Oops there's an error in your code: `java.sql.SQLException: Before start of result set` on the line `data[index - 1] = rs.getObject(meta.getColumnName(index));` – rochiey Oct 12 '15 at 13:44
Sorry by mistake I have used "rs" instead of "results" in a for loop. – Sanjay Oct 12 '15 at 13:48
your code still don't work cause `.next()` is required when using `.getObject() or .getString()` functions right? – rochiey Oct 12 '15 at 13:53
Yeah we ended up the same results. I posted my answer too. – rochiey Oct 12 '15 at 14:11
great relief. Take a break. – Sanjay Oct 12 '15 at 14:15

score 0 · Answer 3 · answered Oct 12 '15 at 14:10

So I have this answer NOW oh my god after having a feeling like of a century.

So with the help of @SanjayChavan's code snippet I finally get the code.

int row = tbl_data.getSelectedRow();
        Integer ID = (Integer) tbl_data.getModel().getValueAt(row, 0);
        try {
            rs = stmt.executeQuery("SELECT * FROM systemaccount where ID = "+ID);
            ResultSetMetaData meta = rs.getMetaData();
            Object[] data = new Object[meta.getColumnCount()];

            while(rs.next())
            {
                for (int index = 1; index <= meta.getColumnCount(); index++) {
                    System.out.println("Column " + index + " is named " + meta.getColumnName(index));
                    data[index - 1] = rs.getObject(meta.getColumnName(index));
                }

                fname.setText((String) data[1]);
                mname.setSelectedItem((String) data[2]);
                lname.setText((String) data[3]);
                address.setText((String) data[4]);
                contact.setText((String) data[5]);
                user.setText((String) getValue((Integer) data[6], "user"));
                usertype.setSelectedItem(getValue((Integer)data[7], "usertype"));
            }
        } 
        catch (SQLException ex) {
            Logger.getLogger(SystemAccount.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        }
    }

This is what it looks like now. after many hours of testing. At first, I wont accept his code snippet as an answer though it's a valid one but not acceptable due to the error of accessing function without the .next() function. So what I did was I put the populating data to array inside the while loop so that it wont be error like before start of ResultSet and it makes a valid one. Thank you ALL !!!!!

MySQL error: a column not found?

3 Answers3