Can't insert java variable into ms access

[yeap]

Hi All,
I can't insert java variable into ms access database.
I'm using odbc connection to ms access.
Below are my coding.

 try 
        {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");        
        // set the connection for database path
        String Database_Path = "E:/Segi/Project/Coding/Car_Park_Storage.mdb";
        String Storage = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        Storage+= Database_Path.trim() + ";DriverID=22;READONLY=true}";          
        Connection Connect = DriverManager.getConnection( Storage ,"",""); 
        Statement Insert = Connect.createStatement();
        //int date_in = Integer.parseInt(Show_Date_In);
        //int times_in = Integer.parseInt(Show_Times_In);
        Insert.execute("insert into Daily_Details values(Car_Number,Parking_Lot,'13','14','345','345','456','3456');"); 
 
        Insert.close(); 
        Connect.close(); 
        }
 

When I change the variable to values like ('11','12','13' ,'14','345','34 5','456','3456' );");
It able to insert into access.
Kindly please help look into the problem.

Thanks and Regards,
Crispin Yeap

[Dököll]

Hey there good buddy!

Looks like a great written code but I cannot see what the problem is. The only solution I have for you is giving something that works for me:

JavaBean:

package foo;
 
/**
 * @author Dököll
 * 
 */
 
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
 
public class AddBean {
 
    private String EmployeeID;
    private String UserID;
 
    private String LastName;
    private String FirstName;
 
    private String Title;
    private String TitleCourtesy;
 
    private String BirthDate;
    private String HireDate;
 
    private String Address;
 
    private Connection connection = null;
    private ResultSet rs = null;
    private Statement st = null;
    String connectionURL = "jdbc:odbc:Northwind";
 
    public AddBean() {
        try {
            // Load the database driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            // Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, "root",
                    "root");
        } catch (Exception e) {
            System.out.println("Exception is ;" + e);
        }
 
    }
 
    public void setUserID(String UserID) {
        this.UserID = UserID;
    }
 
 
 
    public String getUserID() {
        return (this.UserID);
    }
 
 
    public void SendInTheClowns() {
 
        try {
            String sql = "insert into Employees(EmployeeID, UserID,LastName, FirstName, Title, TitleCourtesy,BirthDate, HireDate, Address )values('"
                    +EmployeeID + "','" + UserID + "','" + LastName + "','" + FirstName + "','" + Title + "','" + TitleCourtesy + "','" + BirthDate + "','" + HireDate + "','" + Address + "')";
            Statement s = connection.createStatement();
            s.executeUpdate(sql);
            s.close();
        } catch (Exception e) {
            System.out.println("Exception is ;" + e);
        }
    }
 
    public String getEmployeeID() {
        return EmployeeID;
    }
 
    public void setEmployeeID(String employeeID) {
        EmployeeID = employeeID;
    }
 
    public String getLastName() {
        return LastName;
    }
 
    public void setLastName(String lastName) {
        LastName = lastName;
    }
 
    public String getFirstName() {
        return FirstName;
    }
 
    public void setFirstName(String firstName) {
        FirstName = firstName;
    }
 
    public String getTitle() {
        return Title;
    }
 
    public void setTitle(String title) {
        Title = title;
    }
 
    public String getTitleCourtesy() {
        return TitleCourtesy;
    }
 
    public void setTitleCourtesy(String titleCourtesy) {
        TitleCourtesy = titleCourtesy;
    }
 
    public String getBirthDate() {
        return BirthDate;
    }
 
    public void setBirthDate(String birthDate) {
        BirthDate = birthDate;
    }
 
    public String getHireDate() {
        return HireDate;
    }
 
    public void setHireDate(String hireDate) {
        HireDate = hireDate;
    }
 
    public String getAddress() {
        return Address;
    }
 
    public void setAddress(String address) {
        Address = address;
    }
}
 

Your JSP

 
<%@ page language="java"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 
 
<%@ page import="java.sql.*"%>
<title>Submit form...</title>
</head>
 
<body bgcolor="#ffccff">
<h1>Submit form</h1>
<form name="form1" method="POST" action="">
 
EmployeeID:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br><input
    type="text" name="employeeID"> <br>
User ID:<br><input type="text" name="userID"> <br>
 
Last Name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br><input
    type="text" name="lastName"> <br>
First Name:<br><input type="text" name="firstName"> <br>
 
Title:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br><input
    type="text" name="title"> <br>
Title Courtesy:<br><input type="text" name="titleCourtesy"> <br>
 
Birth Date:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br><input
    type="text" name="birthDate"> <br>
Hire Date:<br><input type="text" name="hireDate"> <br>
 
Address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br><input
    type="text" name="address"> <br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="submit" value="Submit">
<jsp:useBean id="sample" class="foo.AddBean" scope="page">
    <jsp:setProperty name="sample" property="*" />
</jsp:useBean></form>
 
 
<%
    sample.SendInTheClowns();
%>
 
 
</body>
</html>
 

I'll tell ya, this works like waterfalls, sure you'll need to modify to your access database fields to relate to TEXT rather than Number, but, you should be good to go...

You'll need to creat an ODBC connection called Northwind or anything you want:

(1) Control Panel
(2) Administrative Tools
so on and so forth:-)

Gotta go now, hope this works:-)

[r035198x]

1.) You must wrap the values around single quotes when inserting data into character columns in the database. That's why ('11','12','13' ,'14','345','34 5','456','3456' ); works. Using a PreparedStateme nt takes care of the problem.

2.) What are Car_Number and Parking_Lot? Are they supposed to be variables holding the actual values?