Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with DSN less Connection with MS Access 2000

 
Manish Sridharan
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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;
}
*/
}
 
Tom Blough
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most people don't have time to wade through a bunch of code, so if you could distill your problem down to a few lines of code, you might get some more responses. Also, please post any exceptions you code generates.

One thing you might look at is updates and inserts routinely fail because of problems related to the keys.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic