Access Problem

[Steve Holden]

"John Yeager" <webbmaster2001 at directvinternet.com> wrote in message
news:3c289e2f$1_2 at nopics.sjc...
> Useing the following code to access a Access Database, I run into a
problem
> getting the Database to Autonumber the primary key
>
> Here is my code what am I missing
> import time
> import odbc
>
> Time = time.ctime(time.time())
> connection = odbc.odbc('Everquest')
> cur = connection.cursor()
> sql = "Insert into Member VALUES( '(Autonumber goes here )','test',
> 'test','NULL','p','R300478','Testuser','Test','%s')" % ( Time )
> cur.execute( sql )
> connection.commit()
> cur.close()
> connection.close()
>
> So what am I doing wrrong here?
>
It would have been more helpful to supply the *actual* code (which I hope
the above wasn't), along with the *actual* error message. I assume that the
first field in the table's definition is the autonumbered one, but it's
always dangerous to assume a particular column ordering in SQL - someone can
modify the table definition later without your knowing, and your statement
suddenly becomes invalid - or, even worse, puts values in the wrong columns!

I'm thinking you probably need something like either:

    sql = """INSERT INTO Member
             VALUES (NULL, 'test', 'test', NULL,
             'p', 'R300478', 'Testuser', 'Test',
             '%s')""" % ( Time, ) # Note this is now a tuple

or something which uses explicit names for the database columns, and
explicitly omits the key field (which then has NULL provided for it, which
Access will replace with an autonembered value). Like:

    sql = """INSERT INTO Member
                (Field2, Field3, Field4, Field5m Field6, Field7)
                VALUES ('test', 'test', NULL, 'p' 'R300478',
                'Testuser', 'Test',
                '%s')""" %  Time # Tuple not needed with one substitution

You should also note that you can supply the variable data as a tuple second
argument to the execute if you use parameter substitution, whcih you should
look up in the DBI documentation.

regards
 Steve
--
http://www.holdenweb.com/