Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New to the CF scene
    Join Date
    May 2004
    Location
    England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question The joy of MSAccess through ODBC

    I having problems inserting/updating information in a msaccess database throught php.
    running php 4.3.4/windows XP/access 2000

    I get the following message

    Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query., SQL state S1000 in SQLExecDirect in

    There is no problem reading information from the database.

    I have try

    1. creating a new db with just one record.
    2. adding security to the database
    3. adding a index to the table
    4. setting the read only flag in the (just to see what happends)
    5. setting the exclusive flag on/off
    6. updating the microsoft access components to 2.8
    also download KB829558 from microsoft web site

    can anybody help


    Code Example

    The following code is run on a single table microsoft access database
    the select command works ok and returns data item but the insert
    command fails with the message

    Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query., SQL state S1000 in SQLExecDirect in d:\test.php on line 38 failed to update action table -[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.


    <?php

    $currentdb = odbc_connect("newdb","","");

    if ($currentdb == 0)
    {
    die("Cannot connect".odbc_error($currentdb));
    }
    else
    {
    $sqlstring = "select * from name";
    $currentres = odbc_exec( $currentdb,$sqlstring);

    if ($currentres == FALSE)
    {
    die("Cannot exec read".odbc_errormsg($currentdb));
    }
    else
    {
    $s = odbc_result($currentres,1);
    print "$s\n";
    print "</br>";
    }

    $sqlstring = "INSERT INTO name VALUES ('paul2')";

    print "$sqlstring\n";
    print "</br>";

    $currentres = odbc_prepare ( $currentdb , $sqlstring);

    if ($currentres == FALSE)
    {
    die("Cannot prepare".odbc_errormsg($currentdb));
    }

    if (odbc_exec($currentdb , $sqlstring ) == false)
    {
    die("failed to update action table -".odbc_errormsg($currentdb));
    }
    }
    ?>
    Last edited by PAUL HOOPER; 05-19-2004 at 10:06 AM. Reason: add example of source code

  • #2
    New Coder
    Join Date
    Nov 2002
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could you post some code for us to review please?

  • #3
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Take a look at this tutorial, it might help:
    http://www.phpfreaks.com/tutorials/61/0.php

    Sadiq.

  • #4
    New to the CF scene
    Join Date
    May 2004
    Location
    England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have look at the tutor but it only covers selection from a database

  • #5
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I should have read your code over.. it's incomplete:
    Code:
    $sqlstring = "INSERT INTO name (columnName) VALUES ('paul2')";
    You have to specify the columns in your insert statement -- so it's an SQL error.

    Try that and hopefully that fixes your troubles.

    Sadiq.
    Last edited by sad69; 05-20-2004 at 06:53 PM. Reason: Changed PHP tags to CODE tags (highlighting..)

  • #6
    New to the CF scene
    Join Date
    May 2004
    Location
    England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Syntactically it is correct if you are defining all the fields for the table (Ok so its not best practice)
    I did try it as it alway thing that you think you know that cause the problem. but no joy…..

  • #7
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm..don't really know what to tell you.

    Have you tried executing the SQL statement (the insert..) in Access directly? And did it work? Also, you prepare the insert, but not the select (not that I know what prepare means..) but would that have an affect?

    Sadiq.

  • #8
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also, I don't know much about Access, but I wonder about this line:
    PHP Code:
    $currentdb odbc_connect("newdb","",""); 
    You haven't provided a username/password. Could that prevent you from getting write access to the database? I don't know that it will or won't, just talking 'out loud' here (as if that's possible..)

    Also, what is the type of the field your inserting into? Is it text? And do you want to insert 'paul2' or "$paul2" or $paul2 (no quotes in the last one as it is a number or something other than text)?

    Sadiq.
    Last edited by sad69; 05-21-2004 at 06:37 PM.

  • #9
    New to the CF scene
    Join Date
    May 2004
    Location
    England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The prepare statement is a bit of a red herring, there another execute command that uses a pre validated statement. i have try adding a username and password and then running the php but it has no effect
    Last edited by PAUL HOOPER; 05-24-2004 at 04:54 PM.

  • #10
    Regular Coder
    Join Date
    May 2004
    Location
    sweden
    Posts
    236
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This usually is a windows security issue. Without knowing the windows platform I would say right click on the database file and check to make sure that user is "everyone" and all rights are given. Then check the webserver to make sure that the folder that the file is in is accessible. You can also check to make sure INET_USER has permissions on the file or folder that the website is in.
    Carl McDade
    _____________
    Hiveminds Magazine
    for web publisher and community builders
    eRuby Tutorials

  • #11
    New to the CF scene
    Join Date
    May 2004
    Location
    England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok got it working , carl_mcdade was right about the file permisions.
    As a test i moved the .MDB file to the shared folder in XP and the code was happy to write data the to database

    Having been given this thread i found Microsoft Knowledge Base Articles
    that may also be usefull if any one has a similar problem

    Microsoft Knowledge Base Article - 175168
    Microsoft Knowledge Base Article - 316475
    Microsoft Knowledge Base Article - 830977


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •