REPOST: Re: 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/

========= WAS CANCELLED BY =======:
Path: news.sol.net!spool1-milwwi.newsops.execpc.com!newsfeeds.sol.net!newspump.sol.net!newsfeed.direct.ca!look.ca!nntp.kreonet.re.kr!news.kigam.re.kr!feeder.kornet.net!news1.kornet.net!ua4canc3ll3r
From: "Steve Holden" <sholden at holdenweb.com>
Newsgroups: comp.lang.python
Subject: cmsg cancel <hlKW7.64555$7l5.43721 at atlpnn01.usenetserver.com>
Control: cancel <hlKW7.64555$7l5.43721 at atlpnn01.usenetserver.com>
Date: Mon, 31 Dec 2001 02:33:30 GMT
Organization: A poorly-installed InterNetNews site
Lines: 2
Message-ID: <cancel.hlKW7.64555$7l5.43721 at atlpnn01.usenetserver.com>
NNTP-Posting-Host: 211.57.49.2
X-Trace: news2.kornet.net 1009775640 27193 211.57.49.2 (31 Dec 2001 05:14:00 GMT)
X-Complaints-To: usenet at news2.kornet.net
NNTP-Posting-Date: Mon, 31 Dec 2001 05:14:00 +0000 (UTC)
X-No-Archive: yes
X-Unac4ncel: yes
X-Commentary: I love NewsAgent 1.10 and the Sandblaster Cancel Engine Build 74 (19 March 1999)

This message was cancelled from within Mozilla.