JDBC MS Access Exception

[rlaknar]

Hi I am trying to connect a database through Java. I have written a program and it throws exceptions.I am not so strong in java and database connections and I am a newbie. Please anyone help me as soon as possible as its urgent!!!
Here is my program:

import java.sql.Connection;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;
class db{
ResultSet rs,rs1;
Connection con=null;
Statement stmt;
db()throws Exception{
con=null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:test");
stmt=con.createStatement();
}
void store(String name,String password,String dateofreg)throws Exception{
boolean flag=true;
rs=stmt.executeQuery("select username from userinformation where username=name");
while(rs.next()&&flag==true)
{
String str=rs.getString("username");
if (str.equals(name))
flag=false;
}
if(flag!=false){
rs1=stmt.executeQuery("insert into userinformation(username,password,dateofreg) values(username,password,dateofreg)");
}
if(flag==false)
{System.out.println("Storage failed");}
}
void viewall()throws Exception
{
rs=stmt.executeQuery("select * from userinformation");
while(rs.next())
{
System.out.println(rs.getString("userid")+"\t"+rs.getString("username")+"\t"+rs.getString("password")+"\t"+rs.getString("dateofreg")+"\n");
}
}
boolean lookup(String name,String pswd)throws Exception{
rs=stmt.executeQuery("select password from userinformation where username=name");
String pswd1=rs.getString("password");
if(pswd.equals(pswd1)==true)
return true;
else
return false;
}
}
class database{
public static void main(String args[])throws Exception
{
db dbase=new db();
dbase.store("Ram","raju","30-9.-06");
dbase.store("Ram","raju","30-9.-06");
dbase.viewall();
dbase.lookup("Ram","raju");
}
}


[B]And the Exception:[/B]
Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Microsoft Acc
ess Driver] Too few parameters. Expected 1.
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
        at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
        at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:2
53)
        at db.store(db.java:22)
        at database.main(database.java:51)

Please urgently need a solution.Thanks in advance

[javaAddict]

Try changing this line:

rs1=stmt.executeQuery("insert into userinformation(username,password,dateofreg) values(username,password,dateofreg)");

To this:

rs1=stmt.executeQuery("insert into userinformation(username,password,dateofreg) values ('"+username+"','"+password+"','"+dateofreg+"')");

Also the exception tells you at which lines you got the error. It would be better if you point out to us which lines are that, because we cannot just count 51 lines, especially when you post 2 classes at the same time

[rlaknar]

Thanks for your quick response.I have made the changes you said.But still I am blocked where I dont understand the exception.
Here is the program after changes.And I have commented the Exceptions on the appropriate line italized.

import java.sql.Connection;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;
class db {
    ResultSet rs,rs1;
    Connection con=null;
    Statement stmt;

    db()throws Exception
    {
        con=null;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con=DriverManager.getConnection("jdbc:odbc:test");
        stmt=con.createStatement();
    }


    void store(String name,String password,String dateofreg)throws Exception
    {
        boolean flag=true;
        rs = stmt.executeQuery("select username from userinformation where username='"+name+"'");
        while(rs.next() && flag == true)
        {
            String str=rs.getString("username");
            if (str.equals(name))
                flag=false;
        }
        if(flag!=false){
            rs1=stmt.executeQuery("insert into userinformation(username,password,dateofreg) values ('"+name+"','"+password+"','"+dateofreg+"')"); /* // Exception Exception in thread "main" java.sql.SQLException: No ResultSet was produced
                    at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:2
            59)
                    at db.store(database.java:27)
            */


        }
        if(flag==false)
            {System.out.println("Storage failed");}
    }

    void viewall()throws Exception
    {
        rs=stmt.executeQuery("select * from userinformation");
        while(rs.next())
        {
            System.out.println(rs.getString("userid") + "\t" + rs.getString("username") + "\t" + rs.getString("password") + "\t" + rs.getString("dateofreg") + "\n");
        }
    }

    boolean lookup(String name,String pswd)throws Exception 
    {
        rs=stmt.executeQuery("select password from userinformation where username=name");
        String pswd1=rs.getString("password");
        if(pswd.equals(pswd1)==true)
            return true;
        else
            return false;
    }
}
class database{
    public static void main(String args[])throws Exception
    {
        db dbase=new db();
        dbase.store("Ram","raju","30-9.-06"); // Exception  at database.main(database.java:55)
        dbase.store("Ram","raju","30-9.-06");
        dbase.viewall();
        dbase.lookup("Ram","raju");
    }
}

[javaAddict]

Of course, now I see it. It is very simple. The executeQuery is supposed to return a ResultSet. You use that method when you are querying the database (select * from ...) and you expect to get back some rows.

But the query you have is an INSERT query. You are asking for the database to return you a ResultSet: rs1=stmt.executeQuery("insert into userinformation(username,password,dateofreg) values ('"+name+"','"+password+"','"+dateofreg+"')"); But the INSERT query doesn't return anything, it is simply inserting.

So use this method:

int executeUpdate(String sql)

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

The int that returns is the number of rows affected. In your case will return the number of rows inserted (1)

Also next time you should check the API of the classes you are using:
Statement

[javaAddict]

You must also close the connection and the RsultSets at the end, so better make some public methods that do that.
Also you need to apply these changes to other queries as well:

This: rs=stmt.executeQuery("select username from userinformation where username=name") Should be rs=stmt.executeQuery("select username from userinformation where username='" + name + "'") And any where else is needed

[rlaknar]

Thank you very much for your timely help.