This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Problem with DSN less Connection with MS Access 2000 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with DSN less Connection with MS Access 2000" Watch "Problem with DSN less Connection with MS Access 2000" New topic
Author

Problem with DSN less Connection with MS Access 2000

Manish Sridharan
Ranch Hand

Joined: Jul 19, 2005
Posts: 64
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;
}
*/
}


Manish S.
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
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.


Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
 
 
subject: Problem with DSN less Connection with MS Access 2000
 
Similar Threads
Problem with DSN less Connection with MS Access 2000
Applet componets not showing
Invisible JTable Column Header!
JTable problem
Thanks to Craig W. and one more thing...