• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

Access- ODBC/PHP error

Hi,

Im currently working on linking my web page with an access database using PHP and and ODBC. I have the following code to input a new record in a table and display the old and new data. I know its fairly basic but im just trying to learn how the odbc connection works, ive done some work with php/mysql but never with access/odbc. The code:

<?php

function HTML_Head() {
    echo "
    <HTML><HEAD>
    <TITLE>Processing Form</TITLE>
    </HEAD>
    <BODY BGCOLOR=\"#D5D5AB\">";
}

function HTML_Foot() {
    echo "</body></html>";
}

function Database_Entries($msg) {
    echo $msg;
}

function Output_Entries() {
    /*
        Make the connection to the database. The syntax is
        odbc_connect( 'SYSTEM_DSN' , 'USER', 'PASSWORD' );
        $cnx will hold the
        pconnect is used to establish a persistent database
        connection to the Database until the procedure is completed.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }

    // send a simple odbc query . returns an odbc cursor
    $cur= odbc_exec( $cnx, "select Index,FirstName,LastName,PhoneNumber from People" );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }
    echo "<table border=1><tr><th>Index</th><th>First Name</th>".
        "<th>Last Name</th><th>Phone Number</th></tr>\n";
    $nbrow=0;   //Local variable to count number of rows

    // fetch the succesive result rows
    while( odbc_fetch_row( $cur ) ) {
        $nbrow++;
        $Index= odbc_result( $cur, 1 ); // get the field "Index"
        $FirstName= odbc_result( $cur, 2 ); // get the field "FirstName"
        $LastName= odbc_result( $cur, 3 ); // get the field "LastName"
        $PhoneNumber= odbc_result( $cur, 4 ); // get the field "PhoneNumber"

        echo "<tr><td>$Index</td><td>$FirstName</td>".
            "<td>$LastName</td><td>$PhoneNumber</td></tr>\n";
    }

    echo "<tr><td colspan=2>$nbrow entries </td></tr></table>";

    // close the connection. important if persistent connection are "On"
    odbc_close( $cnx);
}

function Error_Handler( $msg, $cnx ) {
    echo "$msg \n";
    odbc_close( $cnx);
    exit();
}

function Enter_New_Entry($FirstName,$LastName,$PhoneNumber) {

    /*
        First, we create a connection to our ODBC source. This is done by creating
        a connection. Once this is done, we are returned an ODBC connection number.
        We use this number to use the ODBC functions within PHP.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }
    $SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', '$PhoneNumber')";

    $cur= odbc_exec( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }

    odbc_close( $cnx);
}

$strOldEntries = "Previous Entries in database";
$strNewEntries = "Updated version of databse (after entries)";

HTML_Head();
Database_Entries($strOldEntries);
Output_Entries();
Enter_New_Entry($FirstName,$LastName,$PhoneNumber);
Database_Entries($strNewEntries);
Output_Entries();
HTML_Foot();

?>

This generates a connection as i can see the current data in the db but it does not input new data from my form, it brings an error msg referring to odbc_exec section:

'Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in C:\Program Files\Apache Group\Apache2\htdocs\webpages\mygym\DataAccess.php on line 81
Error in odbc_exec( no cursor returned )'

This in turn refers to the $SQL_Exec_String, where the data is inserted. The table has another column that is just an auto number so is not referenced in the insert statement, this is the only think i can think of that is causing the problem.

Any Ideas?

Cheers
0
generali
Asked:
generali
  • 6
  • 3
1 Solution
 
flavoCommented:
<Wild Guess>

Do any of the Names contain a '  (ie O'Brien?)

Dave
0
 
flavoCommented:
ahh.. Shouldn't have hit submit.

If so, then you need to replace the single ' with double '

Not sure of the PHP syntax, but it would be like this in VBA

"Insert Into People (FirstName, LastName, PhoneNumber) Values ('" & Replace(FirstName, "'", "''") & "', '" & Replace(lasttName, "'", "''") & "', '$PhoneNumber')";

Dave

0
 
flavoCommented:
>Data type mismatch in criteria expression.,

Given that I can't read PHP, the following maybe a problem

Text fields need to be wraped in ' or "
Date fields need to be wraped in #
Numeric fields don't need any wrappings (They don't like Christams :P)

So, your SQL needs to be interperated as:

"Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('FirstNameValue', 'LastNameValue', '555-100-100')"
           
Assuming all fields are text.  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
generaliAuthor Commented:
Thanks for the comments

Just had another play around with the wrappings etc. I removed the input of the PhoneNumber as this is formatted to number in the DB and the input was successful, except for the fact that it did'nt enter the text for firstname and last name, just the auto number for index......great!!

....but there was no error with the data type mismatch so its the number value that it doesnt like. ive changed the format of the number to text as i would normally format a phone number as text (been working from someone elses example), and also re wrote the form and action page which now works fine.

so im ok with any inserts that are text, but will become unstuck if i need to insert a number, ive tried no quotes, double quotes and it doesnt like either, any more suggestions?

0
 
flavoCommented:
Let's say your phonenumber was still Numeric, then I would have assumed this would have worked:


    $SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', $PhoneNumber)";

If not, can you post the example that does work, I'll see what I can work out.

Dave
0
 
generaliAuthor Commented:
Thats what i thought but it returns an error with regards to the sql syntax when i tried that.

It works like this:

$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', '$PhoneNumber')";

but only if the field PhoneNumber is set to text.
0
 
flavoCommented:
hmm.. According to this post : http:Q_20958626.html#10857389  I thought I would have got it right :(

I can ask some PHP Experts to look at this if you want?

Dave
0
 
generaliAuthor Commented:
Feel like a bit of an idiot now......

just checked with the new scripts i created and it works fine without the '' around the variable holding the phonenumber, so the problem arose from the original example script i was working from.

Many Thanks for the help
0
 
flavoCommented:
Glad to help :^)

Dave
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now