Hello EveryBody !
I am using MS Access 2000 as database. And I am using DSN Less Connecton for the same. The Problem that I am facing is that My Insert and update Query gave the result false when i fire the st.execute(query).
However , when i use the select or create query to read or create. it works fine.
Please suggest the solution.
I also want to know whether the error is from MS Access side due to some security restricion or not.
Below is the code i m using..
import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;
import java.io.FileWriter;
import java.io.BufferedWriter;
import java.util.StringTokenizer;
import java.util.ArrayList;
import java.awt.event.*;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DriverManager;
import java.util.Vector;
import java.util.Iterator;
import java.util.Enumeration;
import java.util.*;
import java.text.DateFormat;
public class MyFileAccessor
{
String url = "jdbc
dbc
river={Microsoft Access Driver (*.mdb)};DBQ= "+
System.getProperty ("user.dir")+System.getProperty ("file.separator")+"gaurav.mdb;" +
"DefaultDir= "+ System.getProperty("user.dir") + " ; " +
" uid=Admin;pwd=gaurav;";
//"UID=Admin;PWD=;";
String Statement1 = null;
Connection con = null ;
ResultSet rs = null ;
Statement st = null ;
Vector rows = null ;
Vector columns = null ;
public MyFileAccessor()
{
try{
getConnected();
//Vector rowData = getData();
}catch(Exception e){System.out.println("FILE ACCESS ERROR :" + e);}
}
private Connection getConnected() //throws Exception
{
try{
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
//String url = "jdbc
dbc
river={Microsoft Access Driver (*.mdb)};DBQ= gaurav.mdb";
//System.out.println(url);
con = DriverManager.getConnection (url);
}catch(Exception e){System.out.println("connect error " +e);}
return con ;
}
public Vector getColumnNames()
{
Vector columnNames = new Vector (8);
columnNames.add("NAME");columnNames.add("DATE");columnNames.add("TIME");
columnNames.add("VISITING TO");columnNames.add("PURPOSE OF VISIT");columnNames.add("COMPANY NAME");
columnNames.add("PASS NO");columnNames.add("SERIAL NO");
return columnNames ;
}
public Vector getData()
{
con = getConnected();
rows = new Vector();
columns = new Vector(8);
try{
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from mytable");
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(" TABLE NAME "+rsmd.getTableName(1));
System.out.println(" TOTAL COLUMN "+ rsmd.getColumnCount());
for(int k = 1 ; k <= rsmd.getColumnCount(); k++ )
System.out.println(" COLUMN NAME " +rsmd.getColumnName(k));
while(rs.next())
{
for(int i=1;i<9;i++)
{
//System.out.println(rs.getString(i)); // NAME , DATE , TIME , VISITING TO , PURPOSE OF VISIT ,
columns.add(rs.getString(i));// COMPANY NAME , PASS NO , SERIAL NO ,
}
rows.add(columns);
}
rs.close();
st.close();
con.close();
}catch(Exception e){System.out.println("myerror2 " + e);}
return rows ;
}
public void writeData(String[] data )
{
int num = 1 , num1 = 10;
String query = "INSERT INTO mytable (SerialNo,Name,Visitingto,Purpose_of_Visit,Company_Name) VALUES (" +
num + ",'"+ data[0] + "','" + data[1] + "','" + data[2] +
"','" + data[3] + "');";
query = "UPDATE mytable set Name = 'abc' where PassNo = 1";
// query = "insert into myabc set name = 'abc' ";
// query = "Create table myabc (name text, age text)";
try{
con = getConnected();
st = con.createStatement();
System.out.println(query);
boolean result = st.execute(query);
System.out.println("Result : INSERT QUERY : " + result );
}catch(Exception e)
{
System.out.println("Error in Writing Data " + e);
e.printStackTrace();
}
}
public static void main(String arg[])
{
MyFileAccessor mfa = new MyFileAccessor();
Vector vt = mfa.getData();
/* Enumeration en_row = vt.elements();
System.out.println("1");
int i = 0 ;
while(en_row.hasMoreElements())
{
Vector vn_col = (Vector) en_row.nextElement();
int j = 0 ;
System.out.println("2");
Enumeration en_col = vn_col.elements();
while(en_col.hasMoreElements())
{
System.out.println("3");
String str = (String)en_col.nextElement();
System.out.println(str);
}
*/
mfa.writeData(arg);
/* Date date = new Date();
System.out.println(date);
String datestr = (DateFormat.getDateInstance()).format(date);
System.out.println(datestr);
datestr = (DateFormat.getTimeInstance()).format(date);
System.out.println(datestr);
*/
}
// System.out.println("PROPERTY IS "+System.getProperty ("user.dir"));
// System.out.println(" file separator is "+System.getProperty ("file.separator"));
//String url = "jdbc
dbc
river={Microsoft Access Driver (*.mdb)};DBQ=" +
//System.getProperty ("user.dir") + System.getProperty ("file.separator") +
//"data" + System.getProperty ("file.separator") + "icudb.mdb";
//"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/websites/folder/mydatabase.mdb;Uid=;Pwd=";
// String data[][]= mfa.readFile();
// mfa.writeFile(data[0]);
// System.out.println("data length " +data.length);
// for(int i=0;i<data.length;i++)
// for(int j=0;j<7;j++)
//System.out.println(data[i][j]);
/*
File file = new File("store.txt");
public MyFileAccessor()
{}
public String[][] readFile() throws java.io.FileNotFoundException,java.io.IOException
{
String row;
String data[][] = new String[50][8];
FileReader fr = new FileReader(file);
BufferedReader br= new BufferedReader(fr);
int i=0,j=0;
while((row=br.readLine())!=null)
{
System.out.println(" Raw Data is "+row);
StringTokenizer separator = new StringTokenizer(row,",");
// System.out.println(" I am here");
while(separator.hasMoreTokens())
{
data[i][j]= separator.nextToken();
// System.out.println(" Data for "+(i+1)+" row is "+ data[i][j]);
if(j>7)
{System.out.println("No of coloumns are more than specified " + j);}
j++;
}
i++; j=0;
if(i>50)
{System.out.println("No of Rows are more than specified ");}
}
System.out.println("No of Rows are "+i);
String Record_Data[][] = new String[i][8];
/*for(int cntr=0;cntr<i;cntr++)
for(int cols=0;cols<7;cols++)
Record_Data[cntr][cols]=data[cntr][cols];
/
Record_Data=data;
System.out.println("Record Data length " +Record_Data.length);
SwingExample.Record_Next_SerialNo = i+1;
// keeping serial no for next Record
fr.close();
return Record_Data ;
}
public boolean writeFile(String[] data) throws java.io.FileNotFoundException,java.io.IOException
{
boolean result= false;
FileWriter fw = new FileWriter("store.txt",true);// allowing append in the file
BufferedWriter bw = new BufferedWriter(fw);
//for(int i=0;i<data.length;i++)
// {
String row = rowCreator(data); // creating the row here
if(row!=null)
{
// bw.newLine(); // line separator
bw.write(row); // writing the row in the file
bw.newLine(); // line separator
}
result=true;
// }
bw.close();
return result;
}
private String rowCreator(String [] rowElements)
{
String row=rowElements[0];
if (row==null)// to check the writing the null lines in the file
{
System.out.println("null is detected");
return row;
}
/* System.out.println("row length "+rowElements.length);
for(int i=0;i<rowElements.length;i++)
{
System.out.println(rowElements[i]);
}
/
for(int i=1;i<rowElements.length;i++)
{
if(rowElements[i]!=null)
row=row+","+rowElements[i];
}
// row=row.substring(0,row.lastIndexOf(","));
System.out.println("row is detected "+row);
return row;
}
*/
}
Bue
Gaurav Nigam