retrieve data from access using php odbc throws error in parameter line

Question

I'm trying to extract data from a table in access database using odbc connection and php. I have written the code below but it gives error "Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'table1.col_date"

<?php $cn = new com("ADODB.Connection");
 $rs = new com("ADODB.Recordset");

$cn->open("dsn=odbcconnection");
?>
<form action = "thispage.php" method = "post">
Enter Date : <input type = "text" name = "datadate" />
<input type = "submit" name = "submit" value = "submit" /> 
</form>
<?php
$datadate = isset($_REQUEST['datadate']) ? $_REQUEST['datadate'] : null; ?>

<?php $sql = "select col_date, sum(qty1), sum(qty2) from table1
              where  table1.col_date = '".$datadate."'
              group by col_date
              order by col_date";

// Execute query
$rs = $cn->execute($sql);

I think there is a problem in only parameter line table1.col_date = $datadate because when I replace $datadate with static date like table1.col_date = #05/08/2012# , it displays output correctly for the date

im getting this error even before selecting date..so I don't think it is related to format...i tried changing table1.col_date = '$datedate' but still the same but if I change it to table1.col_date = #01/05/2012#, it correctly displays output for 1st May, 2012 — user1449596
– user1449596, Commented Aug 29, 2012 at 8:50
$_REQUEST['datadate'] suggests a query string parameter. Is that populated and spelt correctly? Are you posting to this page or an actual thispage.php file? — Conrad Lotz
– Conrad Lotz, Commented Aug 29, 2012 at 9:06
yes it is spelled correctly..Now I'm getting error "data type mismatch" after editing code as per your answer with double quotes(i updated in question) — user1449596
– user1449596, Commented Aug 29, 2012 at 9:09

Conrad Lotz · Accepted Answer · 2012-08-29 09:19:09Z

1

The problem is that is either you are using query string parameters posting to the current page which are empty or you are getting confused with POST. Try the following

If you are posting to the page itself then do the following

<form action = "<?php echo $PHP_SELF; ?>" method = "post">

then change the the to the following:

$datadate = isset($_POST['datadate']) ? $_POST['datadate'] : null; ?>

Lastly make the change as previously stated

Replace the [table1.col_date] with [table1].[col_date] or remove the brackets completely and try the following

<?php $sql = "select col_date, qty1, qty2 from table1 where  [table].[col_date]= '".$datadate."' group by col_date order by col_date";

edited Aug 29, 2012 at 9:19

answered Aug 29, 2012 at 8:49

Conrad Lotz

8,9533 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1449596 Over a year ago

still I'm getting Syntax error (missing operator) in query expression 'table1.col_date = '.'..please see original question i edited my code above as per your answer.....

user1449596 Over a year ago

Thanks JLC007 for your help but it doesn't seem to work. I will keep trying anyway.

user1449596 Over a year ago

After a long effort, i managed to make it work. I used [table].[col_date] = #$datadate#

Collectives™ on Stack Overflow

retrieve data from access using php odbc throws error in parameter line

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related