How do I input data from java GUI into MS Access using UCanAccess

Question

I'm new to using UCanAccess and Microsoft Access as a database for java:

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.*;
import net.ucanaccess.jdbc.*;

public class Memo extends JFrame implements ActionListener {

    private JTextField textField;
    private JTextField textField_1;

    Connection cn = null;
    ResultSet rs = null;
    Statement s = null;

    public Memo() {

        getContentPane().setBackground(Color.DARK_GRAY);
        getContentPane().setLayout(null);

        textField = new JTextField();
        textField.setBounds(246, 0, 178, 50);
        getContentPane().add(textField);
        textField.setColumns(10);

        JLabel lblNewLabel = new JLabel("Enter bill amount: $");
        lblNewLabel.setFont(new Font("Arial Narrow", Font.BOLD, 11));
        lblNewLabel.setForeground(Color.WHITE);
        lblNewLabel.setBounds(10, 0, 237, 50);
        getContentPane().add(lblNewLabel);

        JLabel label = new JLabel("Enter water usage amount(l): ");
        label.setFont(new Font("Arial Narrow", Font.BOLD, 11));
        label.setForeground(Color.WHITE);
        label.setBounds(10, 49, 237, 50);
        getContentPane().add(label);

        textField_1 = new JTextField();
        textField_1.setColumns(10);
        textField_1.setBounds(246, 49, 178, 50);
        getContentPane().add(textField_1);

        JButton btnSubmit = new JButton("Submit");
        btnSubmit.addMouseListener(new MouseAdapter() {
            public void mouseClicked(MouseEvent e) {
                try {
                    Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                    Connection cn = DriverManager.getConnection("jdbc:ucanaccess://C:\\Users\\decx\\Desktop\\Db.accdb");
                    String sql = "insert into db ('ID', 'WaterUsage', 'Bill') + values ('1', '12', '12')";
                    s = cn.createStatement();
                    s.executeUpdate(sql);

                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null, ex);
                }
            }
        });
        btnSubmit.setFont(new Font("Arial Narrow", Font.BOLD, 11));
        btnSubmit.setBounds(272, 131, 141, 35);
        getContentPane().add(btnSubmit);

    }

    public static void main(String[] args) throws Exception {

        Memo qMemo = new Memo();
        qMemo.setSize(500, 350);
        qMemo.setVisible(true);
        qMemo.setTitle("Tips & Tricks");
        qMemo.setDefaultCloseOperation(EXIT_ON_CLOSE);
        qMemo.getContentPane().setLayout(null);

    }

    public void actionPerformed(ActionEvent e) {

    }
}

I need to get the code to send data when the submit button is clicked. This is a school project where I have to allow users to enter water usage and bill (water utility bill), so I can display it later.

I have ran the code previously and but errors like "unexpected token" or "user has no privilege or object not found".

Youcef LAIDANI · Accepted Answer · 2017-08-10 14:18:51Z

2

There are some notes :

You get this error (unexpected token) because the names of columns and table should not be between two quotes ''
The + operator is not allow in that position of query
Also, only the Strings can be between two quotes not the ints, make sure the type of ID for example is a String, if not you have to remove the two quotes
Read about Prepared Statement to avoid syntax error and to prevent SQL Injection

Look at :

String sql="insert into db ('ID', 'WaterUsage', 'Bill') + values ('1', '12', '12')";
//(1)-----------------------^--^--^----------^--^----^  ^         ^ ^
//(2)___________________________________________________|         | |
//(3)_____________________________________________________________| |

edited Aug 10, 2017 at 14:18

answered Aug 10, 2017 at 14:13

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

1 Comment

DarrenTJ Over a year ago

Great! Thanks a lot mate

DarrenTJ · Accepted Answer · 2017-08-11 07:01:11Z

That helped me a lot and with the help of my friends i completed my code, for anyone who might need this at any point, i'll be including my code.

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.*;
import net.ucanaccess.jdbc.*;

public class Memo extends JFrame implements ActionListener {

private JTextField textField;
private JTextField textField_1;

Connection cn = null;
ResultSet rs = null;
Statement s = null;

public Memo() {

    getContentPane().setBackground(Color.DARK_GRAY);
    getContentPane().setLayout(null);

    textField = new JTextField();
    textField.setBounds(246, 0, 178, 50);
    getContentPane().add(textField);
    textField.setColumns(10);

    JLabel lblNewLabel = new JLabel("Enter bill amount: $");
    lblNewLabel.setFont(new Font("Arial Narrow", Font.BOLD, 11));
    lblNewLabel.setForeground(Color.WHITE);
    lblNewLabel.setBounds(10, 0, 237, 50);
    getContentPane().add(lblNewLabel);

    JLabel label = new JLabel("Enter water usage amount(l): ");
    label.setFont(new Font("Arial Narrow", Font.BOLD, 11));
    label.setForeground(Color.WHITE);
    label.setBounds(10, 49, 237, 50);
    getContentPane().add(label);

    textField_1 = new JTextField();
    textField_1.setColumns(10);
    textField_1.setBounds(246, 49, 178, 50);
    getContentPane().add(textField_1);


    JButton btnSubmit = new JButton("Submit");
    btnSubmit.addMouseListener(new MouseAdapter() {
        public void mouseClicked(MouseEvent e) {
            try {
                int num = Integer.parseInt(textField.getText());
                int num1 = Integer.parseInt(textField_1.getText());

                textField.getText();
                textField_1.getText();


                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                Connection cn = DriverManager.getConnection("jdbc:ucanaccess://C:\\Users\\DECX\\Desktop\\Db.accdb");
                String sql = "insert into db (WaterUsage, Bill) values ('"+num+"', '"+num1+"')";
                s = cn.createStatement();
                s.executeUpdate(sql);

            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null, ex);
            }
        }
    });
    btnSubmit.setFont(new Font("Arial Narrow", Font.BOLD, 11));
    btnSubmit.setBounds(272, 131, 141, 35);
    getContentPane().add(btnSubmit);

}

public static void main(String[] args) throws Exception {

    Memo qMemo = new Memo();
    qMemo.setSize(500, 350);
    qMemo.setVisible(true);
    qMemo.setTitle("Memo");
    qMemo.setDefaultCloseOperation(EXIT_ON_CLOSE);
    qMemo.getContentPane().setLayout(null);

}

public void actionPerformed(ActionEvent e) {

}
}

Collectives™ on Stack Overflow

How do I input data from java GUI into MS Access using UCanAccess

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related