DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: JDBC/ Inserting rows into an Access Database

  1. #1
    David Rancour Guest

    JDBC/ Inserting rows into an Access Database


    Hi everyone..

    I am getting into JDBC and am having what appears to be a stupid problem.
    I
    have an access database that I am testing against. There are 3 cols in the
    users table (the only table) and they are as follows:

    id - AutoNumber
    username - Text, 250
    password - Text 50

    Anyway, I wrote the simpliest java program to insert a row into this table
    automatically and everything appears to work but when I got back into the
    database the row did not insert at all. Does anyone see any problems with
    the basic code below:

    import java.sql.*;

    class javaDBTest
    {
    public static void main(String[] args)
    {
    try
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String url = "jdbcdbc:javaTest";
    Connection con = DriverManager.getConnection(url,"","");
    Statement stmt = con.createStatement();
    String strSQL = "INSERT INTO users (username,password) values
    ('daffy duck','xa7guot9')";
    System.out.println(strSQL);
    try
    {
    System.out.println(stmt.executeUpdate(strSQL));
    con.commit();
    System.out.println("username inserted");
    }
    catch (SQLException e)
    {
    System.out.println(e.toString());
    }
    }
    catch (Exception e)
    {
    System.out.println(e.toString());
    }
    }
    }


    output:

    ---------- Run Java Code ----------
    INSERT INTO users (username,password) values ('daffy duck','blah')
    1
    username inserted
    Normal Termination
    Output completed (0 sec consumed).

    ..?? I get the line that says 'username inserted' and the output for the
    executeUpdate statement is 1, so I assume that means that 1 record was
    affected (ie - correct), but the row is not actually being inserted into
    the
    database. I can execute select statements against the connection and they
    work fine. What am I doing wrong?? JDK 1.3 SE.

    Thanks!!

    David Rancour




  2. #2
    David Rancour Guest

    Re: JDBC/ Inserting rows into an Access Database


    I got a response to this question on another newsgroup. I thought that I'd
    share it with you all. Basically you have reposition the cursor by either
    closing the connection or moving to another row for the change to show up,
    so below after calling executeUpate I added con.close(); and it worked fine.


    Strange...oh well, if it was easy everyone would do it right?

    David Rancour

    "David Rancour" <david_rancour@hotmail.com> wrote:
    >
    >Hi everyone..
    >
    >I am getting into JDBC and am having what appears to be a stupid problem.
    >I
    >have an access database that I am testing against. There are 3 cols in the
    >users table (the only table) and they are as follows:
    >
    >id - AutoNumber
    >username - Text, 250
    >password - Text 50
    >
    >Anyway, I wrote the simpliest java program to insert a row into this table
    >automatically and everything appears to work but when I got back into the
    >database the row did not insert at all. Does anyone see any problems with
    >the basic code below:
    >
    >import java.sql.*;
    >
    >class javaDBTest
    >{
    > public static void main(String[] args)
    > {
    > try
    > {
    > Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    > String url = "jdbcdbc:javaTest";
    > Connection con = DriverManager.getConnection(url,"","");
    > Statement stmt = con.createStatement();
    > String strSQL = "INSERT INTO users (username,password) values
    >('daffy duck','xa7guot9')";
    > System.out.println(strSQL);
    > try
    > {
    > System.out.println(stmt.executeUpdate(strSQL));
    > con.commit();
    > System.out.println("username inserted");
    > }
    > catch (SQLException e)
    > {
    > System.out.println(e.toString());
    > }
    > }
    > catch (Exception e)
    > {
    > System.out.println(e.toString());
    > }
    > }
    >}
    >
    >
    >output:
    >
    >---------- Run Java Code ----------
    >INSERT INTO users (username,password) values ('daffy duck','blah')
    >1
    >username inserted
    >Normal Termination
    >Output completed (0 sec consumed).
    >
    >..?? I get the line that says 'username inserted' and the output for the
    >executeUpdate statement is 1, so I assume that means that 1 record was
    >affected (ie - correct), but the row is not actually being inserted into
    >the
    >database. I can execute select statements against the connection and they
    >work fine. What am I doing wrong?? JDK 1.3 SE.
    >
    >Thanks!!
    >
    >David Rancour
    >
    >
    >



  3. #3
    Paul Clapham Guest

    Re: JDBC/ Inserting rows into an Access Database

    How do you know the records are not being inserted? This question is not as
    facetious as it appears: if you open the table in Access, then run your
    program that inserts records, the Access display will not automatically
    update itself to display the new records. Thus it appears that adding
    records didn't work, although the records are in the database and will be
    displayed if you close the Access display and reopen it.

    PC2

    "David Rancour" <david_rancour@hotmail.com> wrote in message
    news:3b149664$1@news.devx.com...
    >
    > Hi everyone..
    >
    > I am getting into JDBC and am having what appears to be a stupid problem.
    > I
    > have an access database that I am testing against. There are 3 cols in the
    > users table (the only table) and they are as follows:
    >
    > id - AutoNumber
    > username - Text, 250
    > password - Text 50
    >
    > Anyway, I wrote the simpliest java program to insert a row into this table
    > automatically and everything appears to work but when I got back into the
    > database the row did not insert at all. Does anyone see any problems with
    > the basic code below:
    >
    > import java.sql.*;
    >
    > class javaDBTest
    > {
    > public static void main(String[] args)
    > {
    > try
    > {
    > Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    > String url = "jdbcdbc:javaTest";
    > Connection con = DriverManager.getConnection(url,"","");
    > Statement stmt = con.createStatement();
    > String strSQL = "INSERT INTO users (username,password)

    values
    > ('daffy duck','xa7guot9')";
    > System.out.println(strSQL);
    > try
    > {
    > System.out.println(stmt.executeUpdate(strSQL));
    > con.commit();
    > System.out.println("username inserted");
    > }
    > catch (SQLException e)
    > {
    > System.out.println(e.toString());
    > }
    > }
    > catch (Exception e)
    > {
    > System.out.println(e.toString());
    > }
    > }
    > }
    >
    >
    > output:
    >
    > ---------- Run Java Code ----------
    > INSERT INTO users (username,password) values ('daffy duck','blah')
    > 1
    > username inserted
    > Normal Termination
    > Output completed (0 sec consumed).
    >
    > .?? I get the line that says 'username inserted' and the output for the
    > executeUpdate statement is 1, so I assume that means that 1 record was
    > affected (ie - correct), but the row is not actually being inserted into
    > the
    > database. I can execute select statements against the connection and they
    > work fine. What am I doing wrong?? JDK 1.3 SE.
    >
    > Thanks!!
    >
    > David Rancour
    >
    >
    >




  4. #4
    David Rancour Guest

    Re: JDBC/ Inserting rows into an Access Database


    No, I had closed out the database completely and reopened it...no new record.
    Then I rebooted and opened the database - still the record is not there.
    However closing the connection after the insert and opening the database
    in access had the new record.

    "Paul Clapham" <pclapham@core-mark.com> wrote:
    >How do you know the records are not being inserted? This question is not

    as
    >facetious as it appears: if you open the table in Access, then run your
    >program that inserts records, the Access display will not automatically
    >update itself to display the new records. Thus it appears that adding
    >records didn't work, although the records are in the database and will be
    >displayed if you close the Access display and reopen it.
    >
    >PC2
    >
    >"David Rancour" <david_rancour@hotmail.com> wrote in message
    >news:3b149664$1@news.devx.com...
    >>
    >> Hi everyone..
    >>
    >> I am getting into JDBC and am having what appears to be a stupid problem.
    >> I
    >> have an access database that I am testing against. There are 3 cols in

    the
    >> users table (the only table) and they are as follows:
    >>
    >> id - AutoNumber
    >> username - Text, 250
    >> password - Text 50
    >>
    >> Anyway, I wrote the simpliest java program to insert a row into this table
    >> automatically and everything appears to work but when I got back into

    the
    >> database the row did not insert at all. Does anyone see any problems with
    >> the basic code below:
    >>
    >> import java.sql.*;
    >>
    >> class javaDBTest
    >> {
    >> public static void main(String[] args)
    >> {
    >> try
    >> {
    >> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    >> String url = "jdbcdbc:javaTest";
    >> Connection con = DriverManager.getConnection(url,"","");
    >> Statement stmt = con.createStatement();
    >> String strSQL = "INSERT INTO users (username,password)

    >values
    >> ('daffy duck','xa7guot9')";
    >> System.out.println(strSQL);
    >> try
    >> {
    >> System.out.println(stmt.executeUpdate(strSQL));
    >> con.commit();
    >> System.out.println("username inserted");
    >> }
    >> catch (SQLException e)
    >> {
    >> System.out.println(e.toString());
    >> }
    >> }
    >> catch (Exception e)
    >> {
    >> System.out.println(e.toString());
    >> }
    >> }
    >> }
    >>
    >>
    >> output:
    >>
    >> ---------- Run Java Code ----------
    >> INSERT INTO users (username,password) values ('daffy duck','blah')
    >> 1
    >> username inserted
    >> Normal Termination
    >> Output completed (0 sec consumed).
    >>
    >> .?? I get the line that says 'username inserted' and the output for the
    >> executeUpdate statement is 1, so I assume that means that 1 record was
    >> affected (ie - correct), but the row is not actually being inserted into
    >> the
    >> database. I can execute select statements against the connection and they
    >> work fine. What am I doing wrong?? JDK 1.3 SE.
    >>
    >> Thanks!!
    >>
    >> David Rancour
    >>
    >>
    >>

    >
    >



  5. #5
    Jack Guest

    Re: JDBC/ Inserting rows into an Access Database


    David -
    I don't think it's your code. I had the same problem with MS Access a few
    months ago ... I wrote a small test program to do selects and inserts and
    it worked fine in Oracle, but did what you describe when I tried it on an
    Access db: selects worked fine, but inserts never happened, even though the
    statement appeared to execute successfully.

    I gave-up after a short time. I suspect that either: (a) update permissions
    were not enabled for the account (username/password) I was using for the
    database itself (but this really *should* have thrown a SQLException), or
    (b) it is a bug in the ODBC driver.

    Jack

    "David Rancour" <david_rancour@hotmail.com> wrote:
    >
    >No, I had closed out the database completely and reopened it...no new record.
    >Then I rebooted and opened the database - still the record is not there.
    >However closing the connection after the insert and opening the database
    >in access had the new record.
    >
    >"Paul Clapham" <pclapham@core-mark.com> wrote:
    >>How do you know the records are not being inserted? This question is not

    >as
    >>facetious as it appears: if you open the table in Access, then run your
    >>program that inserts records, the Access display will not automatically
    >>update itself to display the new records. Thus it appears that adding
    >>records didn't work, although the records are in the database and will

    be
    >>displayed if you close the Access display and reopen it.
    >>
    >>PC2
    >>
    >>"David Rancour" <david_rancour@hotmail.com> wrote in message
    >>news:3b149664$1@news.devx.com...
    >>>
    >>> Hi everyone..
    >>>
    >>> I am getting into JDBC and am having what appears to be a stupid problem.
    >>> I
    >>> have an access database that I am testing against. There are 3 cols in

    >the
    >>> users table (the only table) and they are as follows:
    >>>
    >>> id - AutoNumber
    >>> username - Text, 250
    >>> password - Text 50
    >>>
    >>> Anyway, I wrote the simpliest java program to insert a row into this

    table
    >>> automatically and everything appears to work but when I got back into

    >the
    >>> database the row did not insert at all. Does anyone see any problems

    with
    >>> the basic code below:
    >>>
    >>> import java.sql.*;
    >>>
    >>> class javaDBTest
    >>> {
    >>> public static void main(String[] args)
    >>> {
    >>> try
    >>> {
    >>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    >>> String url = "jdbcdbc:javaTest";
    >>> Connection con = DriverManager.getConnection(url,"","");
    >>> Statement stmt = con.createStatement();
    >>> String strSQL = "INSERT INTO users (username,password)

    >>values
    >>> ('daffy duck','xa7guot9')";
    >>> System.out.println(strSQL);
    >>> try
    >>> {
    >>> System.out.println(stmt.executeUpdate(strSQL));
    >>> con.commit();
    >>> System.out.println("username inserted");
    >>> }
    >>> catch (SQLException e)
    >>> {
    >>> System.out.println(e.toString());
    >>> }
    >>> }
    >>> catch (Exception e)
    >>> {
    >>> System.out.println(e.toString());
    >>> }
    >>> }
    >>> }
    >>>
    >>>
    >>> output:
    >>>
    >>> ---------- Run Java Code ----------
    >>> INSERT INTO users (username,password) values ('daffy duck','blah')
    >>> 1
    >>> username inserted
    >>> Normal Termination
    >>> Output completed (0 sec consumed).
    >>>
    >>> .?? I get the line that says 'username inserted' and the output for the
    >>> executeUpdate statement is 1, so I assume that means that 1 record was
    >>> affected (ie - correct), but the row is not actually being inserted into
    >>> the
    >>> database. I can execute select statements against the connection and

    they
    >>> work fine. What am I doing wrong?? JDK 1.3 SE.
    >>>
    >>> Thanks!!
    >>>
    >>> David Rancour
    >>>
    >>>
    >>>

    >>
    >>

    >



  6. #6
    Ruchi Dhar Guest

    Re: JDBC/ Inserting rows into an Access Database


    The problem is neither with java nor with MSAccess. Its with odbc. When
    you open the table in Access and try to use the odbc to insert ot update
    a record it will fail. Close your Access application and run your java appliction
    now open access you will find the record.

    "Jack" <jdonohue@irise.com> wrote:
    >
    >David -
    >I don't think it's your code. I had the same problem with MS Access a few
    >months ago ... I wrote a small test program to do selects and inserts and
    >it worked fine in Oracle, but did what you describe when I tried it on an
    >Access db: selects worked fine, but inserts never happened, even though

    the
    >statement appeared to execute successfully.
    >
    >I gave-up after a short time. I suspect that either: (a) update permissions
    >were not enabled for the account (username/password) I was using for the
    >database itself (but this really *should* have thrown a SQLException), or
    >(b) it is a bug in the ODBC driver.
    >
    >Jack
    >
    >"David Rancour" <david_rancour@hotmail.com> wrote:
    >>
    >>No, I had closed out the database completely and reopened it...no new record.
    >>Then I rebooted and opened the database - still the record is not there.
    >>However closing the connection after the insert and opening the database
    >>in access had the new record.
    >>
    >>"Paul Clapham" <pclapham@core-mark.com> wrote:
    >>>How do you know the records are not being inserted? This question is

    not
    >>as
    >>>facetious as it appears: if you open the table in Access, then run your
    >>>program that inserts records, the Access display will not automatically
    >>>update itself to display the new records. Thus it appears that adding
    >>>records didn't work, although the records are in the database and will

    >be
    >>>displayed if you close the Access display and reopen it.
    >>>
    >>>PC2
    >>>
    >>>"David Rancour" <david_rancour@hotmail.com> wrote in message
    >>>news:3b149664$1@news.devx.com...
    >>>>
    >>>> Hi everyone..
    >>>>
    >>>> I am getting into JDBC and am having what appears to be a stupid problem.
    >>>> I
    >>>> have an access database that I am testing against. There are 3 cols

    in
    >>the
    >>>> users table (the only table) and they are as follows:
    >>>>
    >>>> id - AutoNumber
    >>>> username - Text, 250
    >>>> password - Text 50
    >>>>
    >>>> Anyway, I wrote the simpliest java program to insert a row into this

    >table
    >>>> automatically and everything appears to work but when I got back into

    >>the
    >>>> database the row did not insert at all. Does anyone see any problems

    >with
    >>>> the basic code below:
    >>>>
    >>>> import java.sql.*;
    >>>>
    >>>> class javaDBTest
    >>>> {
    >>>> public static void main(String[] args)
    >>>> {
    >>>> try
    >>>> {
    >>>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    >>>> String url = "jdbcdbc:javaTest";
    >>>> Connection con = DriverManager.getConnection(url,"","");
    >>>> Statement stmt = con.createStatement();
    >>>> String strSQL = "INSERT INTO users (username,password)
    >>>values
    >>>> ('daffy duck','xa7guot9')";
    >>>> System.out.println(strSQL);
    >>>> try
    >>>> {
    >>>> System.out.println(stmt.executeUpdate(strSQL));
    >>>> con.commit();
    >>>> System.out.println("username inserted");
    >>>> }
    >>>> catch (SQLException e)
    >>>> {
    >>>> System.out.println(e.toString());
    >>>> }
    >>>> }
    >>>> catch (Exception e)
    >>>> {
    >>>> System.out.println(e.toString());
    >>>> }
    >>>> }
    >>>> }
    >>>>
    >>>>
    >>>> output:
    >>>>
    >>>> ---------- Run Java Code ----------
    >>>> INSERT INTO users (username,password) values ('daffy duck','blah')
    >>>> 1
    >>>> username inserted
    >>>> Normal Termination
    >>>> Output completed (0 sec consumed).
    >>>>
    >>>> .?? I get the line that says 'username inserted' and the output for

    the
    >>>> executeUpdate statement is 1, so I assume that means that 1 record was
    >>>> affected (ie - correct), but the row is not actually being inserted

    into
    >>>> the
    >>>> database. I can execute select statements against the connection and

    >they
    >>>> work fine. What am I doing wrong?? JDK 1.3 SE.
    >>>>
    >>>> Thanks!!
    >>>>
    >>>> David Rancour
    >>>>
    >>>>
    >>>>
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center