• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with DSN less Connection with MS Access 2000

 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
}
*/
}

Bue
Gaurav Nigam
 
Manish Sridharan
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
HI GUYS ! I HAVE SOLVED THE EARLIER PROBLEM MYSELF BUT NOW THE PROBLEM I M FACING RITE NOW IS THAT
MY INSERT QUERY IS GIVING RESULT AS FALSE. THAT MEANS THERE IS NO AFFECT OF FIRING THE INSERT QUERY ON THE TABLE AND THERE IS NO ERROR ALSO.
CAN ANYBODY TEL ME WHY IS THIS ERROR(FALSE AS RESULT COMING UP) IS COMING UP

BYE
GAURAV

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DriverManager;
import java.util.*;

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;";

String Statement1 = null;
Connection con = null ;
ResultSet rs = null ;
Statement st = null ;
Vector rows = null ;
Vector columns = null ;

public MyFileAccessor()
{
try{
getConnected();
}catch(Exception e){System.out.println("FILE ACCESS ERROR :" + e);}
}

private Connection getConnected() //throws Exception
{
try{
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection (url);
}catch(Exception e){System.out.println("connect error " +e);}
return con ;
}

public void writeData(String[] data )
{
int num = 1 , num1 = 10;
String query = "insert into mytable (SerialNo,Name,Visitingto,Purpose_of_Visit,Company_Name) VALUES (10,'GAURAV','GAURAV','GAURAV','GAURAV' )";

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[]) throws Exception
{

MyFileAccessor mfa = new MyFileAccessor();
mfa.writeData(arg);

}


}
 
A feeble attempt to tell you about our stuff that makes us money
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic