Accessing two tables on the same page

[Ntropy_]

Hey guys,

The following code doesn't work. On similar pages I haven't been able to access tables using two seperate connects. I know for most of you MySQL is your strongest suit, but I've found they're fairly similar sans the function names. (significant code included only)

---

<?php
while (list($key, $val) = @each($HTTP_POST_VARS)) {
$GLOBALS[$key] = $val;
}

function Output_Entries($AGSNumID, $LearnDesc, $Completed, $DateStart, $DateComp, $LearnComment)
{

if(!$LearnDesc){  $LearnDesc = 'N/A'; }
if(!$Completed){ $Completed = 'N/A'; }
if(!$DateStart){ $DateStart = 'N/A'; }
if(!$DateComp){ $DateComp = 'N/A'; }
if(!$LearnComment){ $LearnComment = 'N/A'; }

// Insert Variables into table

$AGSNum=$AGSNumID;

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

$cur= odbc_exec( $cnx, "Insert into TblLearnPlan (AGSNum, LearnDesc, Completed, DateStart, DateComp, LearnComment) values ('$AGSNum', '$LearnDesc', '$Completed', '$DateStart', '$DateComp', '$LearnComment');" ); 
if (!$cur) { 
    Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
} 
odbc_close( $cnx); 

// Display variables in a table

$cnx = odbc_connect( 'AHW' , 'root', '' );
if(!$cnx) {
    Error_handler( "Error in odbc_connect" , $cnx );
}
$cur= odbc_exec( $cnx, "select AGSNum, LearnDesc, Completed, DateStart, DateComp, LearnComment from TblLearnPlan WHERE AGSNum='$AGSNum' AND LearnDesc='$LearnDesc';");
odbc_fetch_row( $cur );
$AGSNum= odbc_result( $cur, 1 );
$LearnDesc= odbc_result( $cur, 2 );
$Completed= odbc_result( $cur, 3 );
$DateStart= odbc_result( $cur, 4 );
$DateComp= odbc_result( $cur, 5 );
$LearnComment= odbc_result( $cur, 6 );

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

echo "
<table border=1>
<p><h3><u>Learning Plan Details</u><h3><BR>
<table><tr><td>AGS Number:</td><td>$AGSNum</td></tr>
<tr><td>Learning Plan Description:</td><td>$LearnDesc</td></tr>
<tr><td>Has it been Completed?:</td><td>$Completed</td></tr>
<tr><td>Date Started:</td><td>$DateStart</td></tr> 
<tr><td>Date Completed:</td><td>$DateComp</td></tr>
<tr><td>Learning Plan Comments:</td><td>$LearnComment</td></tr>
</table>
<INPUT type=\"Submit\" value=\"Update\">
</form>\n
<p>
<a href=\"Main.php\">Return to the main screen</a><p>
<a href=\"Modify.php\">Return to the Modify AHW Details screen</a>
";
odbc_close( $cnx);

}

---

The error I'm getting is:

Warning: 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\TEST\minifini\InsertLearnR.php on line 57

---

As an aside, the following SQL code doesn't work for me either.

"select Completed, DateStart, DateComp, LearnComment from TblLearnPlan WHERE AGSNum='$AGSNumID' AND LearnDesc='$LearnDescID'"

[tsinka]

Hi,

I'm not an access expert at all but I think the first error has something to do with the fact that access is very strict with data types. The problem might be that you try to insert N/A as a date which is not a valid date.

The second problem might be caused by one of the variables being empty or you might try to remove ' on values that are numeric, e.g. "select Completed, DateStart, DateComp, LearnComment from TblLearnPlan WHERE AGSNum=$AGSNumID AND LearnDesc=$LearnDescID"

You might also try to remove the ; at the end of your queries.

Regards,
Thomas

[Ntropy_]

Neither of those are working unfortunately. For the time being I'm not concerning myself with parsing, so I've left all date fields as text until I get the rest of the program.

Removing the ' gives me a new error msg of

"Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\Program Files\Apache Group\Apache2\htdocs\TEST\minifini\InsertLearnR.php on line 57"

I'm thinking that it must be the structure of the SQL statement itself. I'm having a concurrent problem using two connects in the same page, but upon removing the first one from the code it still doesn't work.

Running the query through Access, I'm getting this as the auto-created SQL statement -

SELECT TblLearnPlan.Completed, TblLearnPlan.DateStart, TblLearnPlan.DateComp, TblLearnPlan.LearnComment
FROM TblLearnPlan
WHERE (((TblLearnPlan.AGSNum)=1) AND ((TblLearnPlan.LearnDesc)="2"))
GROUP BY TblLearnPlan.Completed, TblLearnPlan.DateStart, TblLearnPlan.DateComp, TblLearnPlan.LearnComment;

Thanks for your help!