ACCESS ODBC

psuplat · July 5, 2010

Hi everyone.

I'm having problems with ODBC while retireveing data from ACCESS db. I know, I know - using ACCESS as a db is a VERY bad idea, but this is what I have to work with, no other choice.

Well to the point.

site runs on sessions:

1 (initial) page:

declare 3 sesion variables: "name", "cmmodel", "countrycode";

retrieve name from db table and place them in form's select list,

go to next page using $post

2 page:

to $session['name'] asign posted value from list box;

retrieve model from db table and place them in form's select list,

3 page:

to $session['cmmodel'] asign posted value from list box;

retrieve country code from db table and place them in form's select list,

4 page:

to $session['countrycode'] asign posted value from list box;

go to page 5:

so far everything works like a dream. On page 5 problems start.

for testing i'm listing all the values from session variable to see if they are there

echo $session['name'];

echo $session['cmmodel'];

echo $session['countrycode'];

they all exist.

next using two of those values I'm running a query:

$MySQL = 'select Product_code from CM_specs where Model="'.$_SESSION['cmmodel'].'" AND Country_code='.$_SESSION['countrycode'].'';

if i place the sql statement in MS ACCESS query windows it retrieves the proper record

but on page i get this:

PHP Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in \\10.25.129.24\S_Drive\Data\Bases\SQA\Intranet\test\test2.php on line 49

PHP Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in \\10.25.129.24\S_Drive\Data\Bases\SQA\Intranet\test\test2.php on line 50

what am I missing.

i'm attaching php file below

thanks for any help and advice

[attachment deleted by admin]

trq · July 5, 2010

Mind posting some relevant code?

psuplat · July 5, 2010

well, I've attached a php file

but if it will help I'm attaching the zip with all the files used, except from access db

maybe this will help

Thanks

[attachment deleted by admin]

trq · July 5, 2010

I really don't want to have to download anything, if you could post the relevant code you will likely get a quicker response.

psuplat · July 5, 2010

OK, it's quite a bit, but here you go:

PAGE 1:

<?php

session_start();

//----------------------------------------------------------------------------

//-------DEFINE SESSION VARIABLES------

$_SESSION['name']="bogus inspector";

$_SESSION['cmmodel']="fake model";

$_SESSION['countrycode']="third world country";

//----------------------------------------------------------------------------

echo session_id();echo "<br>";

if(isset($_SESSION['views']))

$_SESSION['views'] = $_SESSION['views']+ 1;

else

$_SESSION['views'] = 1;

echo "views = ". $_SESSION['views'];

echo "<br>";

//----------LIST INSPECTOR FROM ACCESS DB

function listsimple()

{

$ret = '<form name="choose_inspector" method="post" action="page2.php">';

$ret .= '<select name="inspector_name" >'; // create a container to return stuff

$MySQL = "select name from inspectors"; // Prepare SQL

$MyCon=odbc_connect('SQA_Typewriter','',''); // use the SQA_Typewriter ODBC

$MyResult=odbc_exec($MyCon,$MySQL); // run the query against the ODBC

while (odbc_fetch_row($MyResult))

{

$ret .= '<option>'.odbc_result($MyResult,"Name").'</option>';

}

odbc_close($MyCon); // allways close the connection when you have finished

$ret .= '</select>';

$ret .='<p> <input type="submit" name="Submit" value="Continue"></p>';

return $ret;

}

echo listsimple();

?>

PAGE 2

<?php

session_start();

echo session_id();echo "<br>";

//--------ASSISGN INSPECTORS NAME TO SESSION VARIABLE 'name'

$_SESSION['name']=$_POST['inspector_name'];

echo "Inspector choosen: ";

echo $_SESSION['name'];

//------------LIST CM MODELS FROM ACCESS DB

function listsimple()

{

$ret = '<form name="choose_cm_model" method="post" action="page3.php">';

$ret .= '<select name="cm_model">'; // create a container to return stuff

$MySQL = "select model from CM_models"; // Prepare SQL

$MyCon=odbc_connect('SQA_Typewriter','',''); // use the SQA_Typewriter ODBC

$MyResult=odbc_exec($MyCon,$MySQL); // run the query against the ODBC

while (odbc_fetch_row($MyResult))

{

$ret .= '<option>'.odbc_result($MyResult,"Model").'</option>';

}

odbc_close($MyCon); // allways close the connection when you have finished

$ret .= '</select>';

$ret .='<p> <input type="submit" name="Submit" value="Continue"></p>';

return $ret;

}

echo listsimple();

?>

PAGE 3

<?php

session_start();

echo session_id();echo "<br>";

//----ASSIGN CM MODEL TO SESSION VARIABLE 'cmmodel'

$_SESSION['cmmodel']=$_POST['cm_model'];

echo "Inspector choosen: ";

echo $_SESSION['name'];

echo "<br>";

echo "CM model choosen: ";

echo $_SESSION['cmmodel'];

// -----------LIST COUNTRY CODES FROM ACCESS DB

function listsimple()

{

$ret = '<form name="choose_country_code" method="post" action="page4.php">';

$ret .= '<select name="country_code">';

// create a container to return stuff

//$MySQL = "select CountryCode from country_codes order by CountryCode"; // Prepare SQL

$MySQL = 'SELECT Countrycode FROM Country_codes ORDER BY Countrycode'; // Prepare SQL

$MyCon=odbc_connect('SQA_Typewriter','',''); // use the SQA_Typewriter ODBC

$MyResult=odbc_exec($MyCon,$MySQL); // run the query against the ODBC

while (odbc_fetch_row($MyResult))

{

$ret .= '<option>'.odbc_result($MyResult,"Countrycode").'</option>';

}

odbc_close($MyCon); // allways close the connection when you have finished

$ret .= '</select>';

$ret .='<p> <input type="submit" name="Submit" value="Continue"></p>';

return $ret;

}

echo listsimple();

?>

PAGE 4

<?php

session_start();

echo session_id();echo "<br>";

//----ASSIGN COUNTRY CODE TO SESSION VARIABLE 'countrycode'

$_SESSION['countrycode']=$_POST['country_code'];

echo "Confirm the choosen settings:<br>";

echo "Inspector choosen: ";

echo $_SESSION['name'];

echo "<br>";

echo "CM model choosen: ";

echo $_SESSION['cmmodel'];

echo "<br>";

echo "country code choosen: ";

echo $_SESSION['countrycode'];

echo "<br>";

?>

</form>

</form>

PAGE 5

<?php

session_start();

function showqsql($MySQL,$MyID="No ID")

{

echo '<hr>';

echo '<p>'.$MyID.'</p>';

echo '<p>'.$MySQL.'</p>';

echo '<hr>';

}

///////////////////////////////list session data

echo "Inspector: ";

echo $_SESSION['name'];

echo "<br>";

echo "CM model: ";

echo $_SESSION['cmmodel'];

echo "<br>";

echo "Country code: ";

echo $_SESSION['countrycode'];

echo "<br>";

function listsimple()

{

$ret = '<table>'; // create a container to return stuff

$MySQL = 'select Product_code from CM_specs where Model="'.$_SESSION['cmmodel'].'" AND Country_code='.$_SESSION['countrycode'].''; // Prepare SQL

showqsql($MySQL,"listsimple");

$MyCon=odbc_connect('SQA_Typewriter','',''); // use the SQA_Typewriter ODBC

$MyResult=odbc_exec($MyCon,$MySQL); // run the query against the ODBC

while (odbc_fetch_row($MyResult))

{

echo odbc_result($MyResult,"Product_code") .'<br>';

$ret .='<tr><td>'.odbc_result($MyResult,"daisy_wheel_no").'</td></tr>';

}

odbc_close($MyCon); // close the connection when you have finished

$ret .= '</table>';

return $ret;

}

echo listsimple();

echo "<br>";

?>

psuplat · July 5, 2010

one more thing,

the connection is defined in separate file (DbConnect.php) but it does work anyway, cause i can retrieve data on first 3 pages

trq · July 5, 2010

Sorry, but I said relevant code. The error states, line 49 of test2.php.

You've posted a bunch of code, none of which are marked as being test2.php.

ps: We have

tags for posting code.

psuplat · July 5, 2010

Oh, this is because I did some changes in the code and file names before posting them to be easier to follow through.

the error is:

PHP Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in \\10.25.129.24\S_Drive\Data\Bases\SQA\Intranet\nowe\page5.php on line 47

PHP Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in \\10.25.129.24\S_Drive\Data\Bases\SQA\Intranet\nowe\page5.php on line 48

although in the warning tell which function is returning error,

anyway tha's the page with problems - page5.php

<?php
session_start();
///------------ function to print out sql
function showqsql($MySQL,$MyID="No ID")
   {
   echo '<hr>';
   echo '<p>'.$MyID.'</p>';
   echo '<p>'.$MySQL.'</p>';
   echo '<hr>';
   }

///////////////////////////////list session data
echo "Inspector: ";
echo $_SESSION['name'];
echo "<br>";
echo "CM model: ";
echo $_SESSION['cmmodel'];
echo "<br>";
echo "Country code: ";
echo $_SESSION['countrycode'];
echo "<br>";


function listsimple()
   {
   $ret = '<table>';                                    // create a container to return stuff
   $MySQL = 'select Product_code from CM_specs where Model="'.$_SESSION['cmmodel'].'" AND Country_code='.$_SESSION['countrycode'].'';   // Prepare SQL
   showqsql($MySQL,"listsimple");


   $MyCon=odbc_connect('SQA_Typewriter','','');         // use the SQA_Typewriter ODBC
   $MyResult=odbc_exec($MyCon,$MySQL);                  // run the query against the ODBC
   while (odbc_fetch_row($MyResult))
      {
      echo odbc_result($MyResult,"Product_code") .'<br>';

       $ret .='<tr><td>'.odbc_result($MyResult,"Product_code").'</td></tr>';
       
      }
    odbc_close($MyCon);                                 //  close the connection when you have finished
   $ret .= '</table>';
   return $ret;
   }
echo listsimple();
?>

psuplat · July 6, 2010

Got it solved, so just in case somebody need this in the future:

MSSQL server configuration doesn't allow escaping of double quotations. I'm guessing that this is a default setting - can it be changed, that I don't know. Apparently when conecting to Access db same rule applies (read: M$ rules )

On the code line:

$MySQL = 'select Product_code from CM_specs where Model="'.$_SESSION['cmmodel'].'" AND Country_code='.$_SESSION['countrycode'].'';

session variable passed as string ($_SESSION['cmmodel']) has to be blace in ' instead of " and then they can be escaped. Like this:

$MySQL = 'select Product_code from CM_specs where Model=\''.$_SESSION['cmmodel'].'\' AND Country_code='.$_SESSION['countrycode'].'';

so the error message giving code line numbers was a bit useless, since the error was a bit further up. I always thought that ability to code was the ability to use logic and apply it, not to blindly follow error messages

trq · July 6, 2010

I always thought that ability to code was the ability to use logic and apply it, not to blindly follow error messages

The error occurred on the line where your query was actually executed wiseguy.

Sign In

ACCESS ODBC

Recommended Posts

psuplat

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

psuplat

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

psuplat

Link to comment

Share on other sites

psuplat

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

psuplat

Link to comment

Share on other sites

psuplat

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information