FAQ
First time using Access.

I've used my usual insert using placeholders and got an error about a
count mismatch. Does Access not support placeholders?

Anything I should know about Access and DBI?

Also (a little off topic) how do I create an AutoNumber field?

Are there any advantages/disadvantages in using Access over SQL
Server? I don't know enough to make anyone switch...

Cheers,
Jeff
(who'd rather be on *nix)

Search Discussions

  • Paul Boutros at Dec 20, 2002 at 6:27 am

    First time using Access.

    I've used my usual insert using placeholders and got an error about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the versions I've
    used. Can you post more info?
    Anything I should know about Access and DBI?
    1) You can do crosstab queries directly from DBI using the appropriate SQL
    2) You can access Access queries just like tables -- in other words, an
    Access query is basically just a view to DBI
    Also (a little off topic) how do I create an AutoNumber field?
    Go to table design, create a field and select the type "AutoNumber".
    Incidentally, afaik (and somebody *PLEASE* correct me if I'm wrong about
    this!) you insert values into a table w/ an autonumbered field by just
    ignoring that field in the insert statement. If you then want to find out
    what number was assigned to that field by Access' autonumbering, you have
    to go back and run another query to check it.
    Are there any advantages/disadvantages in using Access over SQL
    Server? I don't know enough to make anyone switch...
    Hmm... don't know. Access DBs are single-file and the software and
    drivers are pretty much available on any PC. You can do crosstab queries
    in Access but I don't think they're available in SQL-Server.

    HTH,
    Paul
  • Jeff Thies at Dec 20, 2002 at 7:15 am

    Paul Boutros wrote:
    First time using Access.

    I've used my usual insert using placeholders and got an error about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the versions I've
    used. Can you post more info?
    I get this error:

    DBD::ADO::st execute failed: Can't execute statement 'insert into
    tracking (lstord,time_stamp)values(?,?)': -2147217904: OLE exception
    from "Microsoft OLE DB Provider for ODBC Drivers":

    [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

    (there are only two fields and COUNT is not one of them)

    with this code:

    $sql=q{insert into tracking (lstord,time_stamp)values(?,?)};
    $sth=$db->prepare($sql);
    $sth->execute($lstord,$time_stamp) or die "Cannot execute:
    $DBI::errstr\n";

    taking the placeholders out works:

    $sql=qq{insert into tracking
    (lstord,time_stamp)values('$lstord','$time_stamp')};
    $sth=$db->prepare($sql);
    $sth->execute() or die "Cannot execute: $DBI::errstr\n";
    Anything I should know about Access and DBI?
    1) You can do crosstab queries directly from DBI using the appropriate
    SQL

    Excuse my total ignorance. What is a crosstab query?

    Cheers and thanks,
    Jeff
    2) You can access Access queries just like tables -- in other words, an
    Access query is basically just a view to DBI
    Also (a little off topic) how do I create an AutoNumber field?
    Go to table design, create a field and select the type "AutoNumber".
    Incidentally, afaik (and somebody *PLEASE* correct me if I'm wrong about
    this!) you insert values into a table w/ an autonumbered field by just
    ignoring that field in the insert statement. If you then want to find out
    what number was assigned to that field by Access' autonumbering, you have
    to go back and run another query to check it.
    Are there any advantages/disadvantages in using Access over SQL
    Server? I don't know enough to make anyone switch...
    Hmm... don't know. Access DBs are single-file and the software and
    drivers are pretty much available on any PC. You can do crosstab queries
    in Access but I don't think they're available in SQL-Server.

    HTH,
    Paul
  • Thomas A. Lowery at Dec 20, 2002 at 2:40 pm
    Place holder example:

    @dbi:ADO:File Name=.\access.udl> insert into tst(id,str) values (99,'this old ma n');
    [1 row affected]
    @dbi:ADO:File Name=.\access.udl> insert into tst(id,str) values (101,?);
    Statement has 1 parameters:
    Parameter 1 value: this old man
    [1 row affected]
    On Fri, Dec 20, 2002 at 02:17:54AM -0500, Jeff Thies wrote:
    Paul Boutros wrote:
    I've used my usual insert using placeholders and got an error about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the versions I've
    used. Can you post more info?
    I get this error:

    DBD::ADO::st execute failed: Can't execute statement 'insert into
    tracking (lstord,time_stamp)values(?,?)': -2147217904: OLE exception
    from "Microsoft OLE DB Provider for ODBC Drivers":

    [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
    Odd error, could be a bug in DBD::ADO attempting to determine the number of
    parameters required.

    Also, I'd suggest using the actually ADO driver to Access (Jet) instead of the
    OLE DB Provider for ODBC ... Information on DSN-Less connection: (Sorry about
    all the pop-ups, I need to move this to standard FAQ site)

    http://tlowery.hypermart.net/faq_examples/information_about_ado_and_dsn.htm
    $sql=q{insert into tracking (lstord,time_stamp)values(?,?)};
    $sth=$db->prepare($sql);
    $sth->execute($lstord,$time_stamp) or die "Cannot execute:
    $DBI::errstr\n";
    Tom

    --
    Thomas A. Lowery
    See DBI/FAQ http://www.xmlproj.com/cgi/fom.cgi
  • Michael A Chase at Dec 20, 2002 at 2:43 pm

    On Fri, 20 Dec 2002 02:17:54 -0500 Jeff Thies wrote:

    Paul Boutros wrote:
    First time using Access.

    I've used my usual insert using placeholders and got an error about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the versions I've
    used. Can you post more info?
    I get this error:

    DBD::ADO::st execute failed: Can't execute statement 'insert into
    tracking (lstord,time_stamp)values(?,?)': -2147217904: OLE exception
    from "Microsoft OLE DB Provider for ODBC Drivers":

    [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

    (there are only two fields and COUNT is not one of them)

    with this code:

    $sql=q{insert into tracking (lstord,time_stamp)values(?,?)};
    $sth=$db->prepare($sql);
    $sth->execute($lstord,$time_stamp) or die "Cannot execute:
    $DBI::errstr\n";

    taking the placeholders out works:

    $sql=qq{insert into tracking
    (lstord,time_stamp)values('$lstord','$time_stamp')};
    $sth=$db->prepare($sql);
    $sth->execute() or die "Cannot execute: $DBI::errstr\n";
    What types are lstord and time_stamp? Literals get
    converted automagically while placeholders do not.

    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
  • Michael A Chase at Dec 21, 2002 at 12:13 am

    On Fri, 20 Dec 2002 15:14:36 -0500 Jeff Thies wrote:



    Michael A Chase wrote:
    On Fri, 20 Dec 2002 02:17:54 -0500 Jeff Thies
    wrote:
    Paul Boutros wrote:
    First time using Access.

    I've used my usual insert using placeholders and got an error
    about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the versions
    I've
    used. Can you post more info?
    I get this error:

    DBD::ADO::st execute failed: Can't execute statement 'insert into
    tracking (lstord,time_stamp)values(?,?)': -2147217904: OLE exception
    from "Microsoft OLE DB Provider for ODBC Drivers":

    [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

    (there are only two fields and COUNT is not one of them)

    with this code:

    $sql=q{insert into tracking (lstord,time_stamp)values(?,?)};
    $sth=$db->prepare($sql);
    $sth->execute($lstord,$time_stamp) or die "Cannot execute:
    $DBI::errstr\n";

    taking the placeholders out works:

    $sql=qq{insert into tracking
    (lstord,time_stamp)values('$lstord','$time_stamp')};
    $sth=$db->prepare($sql);
    $sth->execute() or die "Cannot execute: $DBI::errstr\n";
    What types are lstord and time_stamp? Literals get
    converted automagically while placeholders do not.
    Everything is a varchar.
    So much for the easy answer.
    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
  • Jeff Urlwin at Dec 21, 2002 at 1:59 am


    Michael A Chase wrote:
    On Fri, 20 Dec 2002 02:17:54 -0500 Jeff Thies
    wrote:
    Paul Boutros wrote:
    First time using Access.

    I've used my usual insert using placeholders and
    got an error
    about a
    count mismatch. Does Access not support placeholders?
    It definitely does support place-holders, at least in the
    versions
    I've
    used. Can you post more info?
    I get this error:

    DBD::ADO::st execute failed: Can't execute statement
    'insert into
    tracking (lstord,time_stamp)values(?,?)': -2147217904: OLE
    exception from "Microsoft OLE DB Provider for ODBC Drivers":

    [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

    (there are only two fields and COUNT is not one of them)

    with this code:

    $sql=q{insert into tracking (lstord,time_stamp)values(?,?)};
    $sth=$db->prepare($sql);
    $sth->execute($lstord,$time_stamp) or die "Cannot execute:
    $DBI::errstr\n";

    taking the placeholders out works:

    $sql=qq{insert into tracking
    (lstord,time_stamp)values('$lstord','$time_stamp')};
    $sth=$db->prepare($sql);
    $sth->execute() or die "Cannot execute: $DBI::errstr\n";
    What types are lstord and time_stamp? Literals get converted
    automagically while placeholders do not.
    Everything is a varchar.
    Can you give DBD:::ODBC a shot?

    Jeff
  • Bart Lateur at Dec 20, 2002 at 10:15 am

    On Fri, 20 Dec 2002 01:14:43 -0500, Jeff Thies wrote:

    I've used my usual insert using placeholders and got an error about a
    count mismatch. Does Access not support placeholders?
    It does. But you'll get this confusing error message when using a field
    name that Access doesn't recognise. Apparently, it then treats it as a
    named placeholder, if such a beast would exist.

    --
    Bart.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedDec 20, '02 at 6:12a
activeDec 21, '02 at 1:59a
posts8
users6
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase