how to set values from sql database to java textbox?

Question

When I try to access data from SQL Server, the data column is of type varchar. But I try to set it into textbox, I got these type of exception

Conversion failed when converting the varchar value '09ran01' to data type int

package src.ui;

import src.ui.ImageHandler;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;

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

import java.awt.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.text.DateFormat;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;

public class AddAccount extends JPanel implements ActionListener, DocumentListener, KeyListener {

    String imagefolder = ImageHandler.returnimagepath();
    Image bgImage = Toolkit.getDefaultToolkit().createImage(getClass().getResource("/src/ui/images/bgc.jpg"));
    private static final long serialVersionUID = 1L;
    public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
    String iden[] = {"Student","Others"};
    String type[] = {"Account User", "Walkthrough User"};
    JLabel userid = new JLabel("USER ID");
    JLabel name = new JLabel("FULL NAME");
    JLabel pswrd = new JLabel("PASSWORD");
    JLabel ident = new JLabel("IDENTIFICATION");
    JLabel idno = new JLabel("ID NUMBER");
    JLabel Add = new JLabel("ADDRESS");
    JLabel acctype = new JLabel("ACCOUNT TYPE");
    JTextField userFld = new JTextField(10);
    JTextField nameFld = new JTextField(10);
    JPasswordField pswrdFld = new JPasswordField(10);
    JComboBox identC = new JComboBox(iden);
    JComboBox actype = new JComboBox(type);
    JTextField idFld = new JTextField(10);
    JTextArea addArea = new JTextArea(10, 10);
    JButton okButton = new JButton("OK");
    JButton exitButton = new JButton("RESET");
    JButton b1 = new JButton("SEARCH");
    JScrollPane scroll = new JScrollPane(addArea);

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/cybman", "root", "");
        } catch (Exception e) {
            //System.out.println(e);
        }
        return conn;
    }
    public static Connection getConnection1() {
        Connection con = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           con = DriverManager.getConnection("jdbc:sqlserver://localhost\\BCA1:1433;databaseName=Old_Bhandarkars;user=sa;password=1234");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,"SERVER ERROR");
            //System.out.println(e);
        }
        return con;
    }

    public void changedUpdate(DocumentEvent ev) {
    }

    public void removeUpdate(DocumentEvent ev) {
    }

    public void insertUpdate(DocumentEvent ev) {
    }

    public void paintComponent(Graphics g) {
        g.drawImage(bgImage, 0, 0, this);
    }

    public void init() {
        setLayout(null);
        okButton.addActionListener(this);
        b1.addActionListener(this);
        exitButton.addActionListener(this);
        pswrdFld.addActionListener(this);
        pswrdFld.setEchoChar('*');
        addArea.getDocument().addDocumentListener(this);
        identC.addActionListener(this);
        actype.addActionListener(this);
        userid.setBounds(270, 20, 120, 20);
        userFld.setBounds(470, 20, 140, 20);
        b1.setBounds(670, 20, 100, 20);
        name.setBounds(270, 70, 120, 20);
        nameFld.setBounds(470, 70, 140, 20);
        pswrd.setBounds(270, 120, 120, 20);
        pswrdFld.setBounds(470, 120, 140, 20);
        ident.setBounds(270, 170, 120, 20);
        identC.setBounds(470, 170, 140, 20);
        idno.setBounds(270, 220, 120, 20);
        idFld.setBounds(470, 220, 140, 20);
        acctype.setBounds(270, 270, 120, 20);
        actype.setBounds(470, 270, 140, 20);
        Add.setBounds(270, 320, 320, 20);
        scroll.setBounds(470, 320, 140, 60);
        okButton.setBounds(270, 410, 100, 20);
        okButton.addKeyListener(this);
        exitButton.setBounds(510, 410, 100, 20);
    }

    public AddAccount() {
        init();
        add(userid);
        add(userFld);
        add(name);
        add(nameFld);
        add(pswrd);
        add(pswrdFld);
        add(ident);
        add(identC);
        add(idno);
        add(idFld);
        add(acctype);
        add(actype);
        add(Add);
        add(b1);
        add(scroll);
        add(okButton);
        add(exitButton);
    }

    public static String now() {
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
        return sdf.format(cal.getTime());

    }

    private String getDateTime() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }
    public class Function
    {
       Connection  con=getConnection1();
     ResultSet rs;  

       public ResultSet find(String s)
       {
          int a=Integer.parseInt(s);
           try{
               Statement sta = con.createStatement();
           String s1 = "select * from Members where Fld_Member_Id="+a+"";
         rs = sta.executeQuery(s1);


           }
           catch(Exception ex){
               JOptionPane.showMessageDialog(null,"No connection");
               //JOptionPane.showMessageDialog(null,ex.getMessage());
           }
           //JOptionPane.showMessageDialog(null,rs);

       return rs;

       }

    }

    public void actionPerformed(ActionEvent e) {
        String str = e.getActionCommand();
        char[] pass;
        String uid;
        String nam;
        String add;
        String id;
        String idnum;
        String actyp;
        int amt = 0;
        String datE;
        if (str.equals("SEARCH")) {
            Function f=new Function();
            ResultSet rs=null;
            String n1="Fld_First_Name";
            String i1="Fld_Member_Id";
            String address="Fld_PAddr";
            uid = userFld.getText();


            if(uid.equals(""))
            {
                JFrame msg=new JFrame();
                JOptionPane.showMessageDialog(msg, "Please enter the user id", "Error Message", JOptionPane.ERROR_MESSAGE);

            }
           // simple.main();
           else
            {
                 //idFld.setText(userFld.getText());
            rs=f.find(userFld.getText());
           // System.out.println("Fld_First_Name");
            try{
                if(rs.next()){
//                     int i = Integer.parseInt(rs.getString("Fld_First_Name"));
//                    int j = Integer.parseInt(rs.getString("Fld_Member_Id"));
//                    int k = Integer.parseInt(rs.getString("Fld_PAddr"));
                   nameFld.setText(rs.getString("Fld_First_Name"));
                   idFld.setText(rs.getString("Fld_Member_Id"));
                   addArea.setText(rs.getString("Fld_PAddr"));

                }
                else
                {
                    JOptionPane.showMessageDialog(null,"No data");
                }
            }
            catch(Exception ex)
            {
                 JOptionPane.showMessageDialog(null,ex.getMessage());
            }
            }
        }

        if (str.equals("OK")) {


            try {

                uid = userFld.getText();
                nam = nameFld.getText();
                pass = pswrdFld.getPassword();
                add = addArea.getText();
                idnum = idFld.getText();
                id = (String) identC.getSelectedItem();
                actyp = (String) actype.getSelectedItem();
                datE = getDateTime();
                String pwrd = new String(pass);

                Connection conn = getConnection();

                /*ERROR CHECKING*/
                boolean success = true;

                if (uid.equals("")) {
                    JFrame msg = new JFrame();
                    JOptionPane.showMessageDialog(msg, "Please enter the user id", "Error Message", JOptionPane.ERROR_MESSAGE);
                    success = false;
                } else if (nam.equals("")) {
                    JFrame msg = new JFrame();
                    JOptionPane.showMessageDialog(msg, "Please enter the name", "Error Message", JOptionPane.ERROR_MESSAGE);
                    success = false;
                } else if (pwrd.equals("")) {
                    JFrame msg = new JFrame();
                    JOptionPane.showMessageDialog(msg, "Please enter the password", "Error Message", JOptionPane.ERROR_MESSAGE);
                    success = false;
                } else if ((!id.equals("Student")) && (idnum.equals(""))) {
                    JFrame msg = new JFrame();
                    JOptionPane.showMessageDialog(msg, "Please enter the identification number", "Error Message", JOptionPane.ERROR_MESSAGE);
                    success = false;
                } else if (add.equals("")) {
                    JFrame msg = new JFrame();
                    JOptionPane.showMessageDialog(msg, "Please enter the address", "Error Message", JOptionPane.ERROR_MESSAGE);
                    success = false;
                }

                /*end of ERROR CHECKING*/

                if (success == true) {
                    ///////////ENSURE/////////
                    JFrame msg = new JFrame();
                    int choice;
                    choice = JOptionPane.showConfirmDialog(msg, "Are you sure?", "Select your choice", JOptionPane.OK_CANCEL_OPTION);
                    /////////////////////////

                    if (choice == JOptionPane.OK_OPTION) {
                        str = "insert into account(userid,name,password,address,identification,identificationnum,amount,dates,acctype" + ") values (?,?,?,?,?,?,?,?,?)";
                        PreparedStatement ps = conn.prepareStatement(str);

                        ps.setString(1, uid);
                        ps.setString(2, nam);
                        ps.setString(3, pwrd);
                        ps.setString(4, add);
                        ps.setString(5, id);
                        ps.setString(6, idnum);
                        ps.setInt(7, amt);
                        ps.setString(8, datE);
                        ps.setString(9, actyp);

                        int count;
                        count = ps.executeUpdate();
                        count++;
                        count--;

                        //////////////////////////////
                        ReportPanel.userC.addItem(uid);
                        ViewAccount.userC.addItem(uid);

                        if (actyp.equals("Account User")) {
                            RechargePanel.userC.addItem(uid);
                        }
                        //////////////////////////////

                        JFrame msg1 = new JFrame();
                        JOptionPane.showMessageDialog(msg1, "An account has been successfully created", "Information", JOptionPane.INFORMATION_MESSAGE);
                    } else if (choice == JOptionPane.CANCEL_OPTION) {
                        JOptionPane.showMessageDialog(msg, "Account Creation Aborted", "Information", JOptionPane.INFORMATION_MESSAGE);
                    }
                }
                //conn.close();


            } catch (SQLException e1) {
                String error;
                JFrame msg = new JFrame();
                //error=e1.toString();
                error = "Your entry for user id already exists";
                JOptionPane.showMessageDialog(msg, error, "Error Message", JOptionPane.ERROR_MESSAGE);
            } catch (Exception e1) {
                // TODO Auto-generated catch block
                String error;
                JFrame msg = new JFrame();
                error = e1.toString();
                JOptionPane.showMessageDialog(msg, error, "Error Message", JOptionPane.ERROR_MESSAGE);

            }

        }
///////////////////////////////////////////////////////////////////////////     
        if (str.equals("RESET")) {
            try {
                userFld.setText("");
                nameFld.setText("");
                pswrdFld.setText("");
                addArea.setText("");
                idFld.setText("");
            } catch (Exception e2) {
                String error;
                JFrame msg = new JFrame();
                error = e2.toString();
                JOptionPane.showMessageDialog(msg, error, "Error Message", JOptionPane.ERROR_MESSAGE);
            }
        }
    }
///////////////////////////////////////////////////////////

    public void keyPressed(KeyEvent key) {
        //
    }
/////////////////////////////////////////////////////   

    public void keyReleased(KeyEvent key) {
        if (key.getSource() == pswrdFld) {
            if (key.getKeyCode() == KeyEvent.VK_ENTER) {
            }
        }
    }

/////////////////////////////////////////////////
    public void keyTyped(KeyEvent key) {
       //
    }
//////////////////////////////////////////////

}

How to set all values to textbox?

            field name                    data type
---------------------------------------------------------------------
          Fld_First_Name                varchar
          Fld_Member_Id                 varchar
          Fld_PAddr                     nvarchar


SELECT  * FROM Members where Fld_Member_Id='14932';


_____________________________________________________________________________
Fld_Member_Id    Fld_First_Name       Fld_PAddr
______________________________________________________________________________
14932               abc               abc

Sajith Herath · Accepted Answer · 2017-04-22 07:19:23Z

4

In WHERE clause of a SQL query a varchar variables should be within a single quotes. Try String s1 = "select * from Members where Fld_Member_Id='"+a+"'"; add Single quotes into the statement.And i think it should work setText method that you mentioned if you pass a varchar value correctly.

answered Apr 22, 2017 at 7:19

Sajith Herath

1,0512 gold badges12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naresh · Accepted Answer · 2017-06-11 10:31:08Z

4

If you are not sure what is the type of memberID, then use

idFld.setText(String.valueOf("Your value"));

edited Jun 11, 2017 at 10:31

Naresh

3341 gold badge4 silver badges17 bronze badges

answered Apr 22, 2017 at 7:54

Architkumar Patel

715 bronze badges

Collectives™ on Stack Overflow

how to set values from sql database to java textbox?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related