Can't insert java variable into ms access

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • yeap
    New Member
    • Feb 2008
    • 1
    #1

    Can't insert java variable into ms access

    Hi All,
    I can't insert java variable into ms access database.
    I'm using odbc connection to ms access.
    Below are my coding.
    Code:
     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
    Last edited by Nepomuk; Dec 11 '08, 11:17 PM. Reason: Please use [CODE] tags!
    Tags: None
    • Dököll
      Recognized Expert Top Contributor
      • Nov 2006
      • 2379
      #2
      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:

      Code:
      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

      Code:
      <%@ 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:-)
      Last edited by Dököll; Dec 11 '08, 10:58 PM. Reason: added remark...

        Comment

        • r035198x
          MVP
          • Sep 2006
          • 13225
          #3
          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?

            Comment

            Previous template Next
            Working...