insert doesn't work - help please! ...and thank you.

[perplexia]

I'm trying to insert new data into a MS Access database using php. I get the following error"

 

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> ADODB.Connection<br/><b>Description:</b> Operation is not allowed when the object is closed.' in C:\wamp\www\Jim_Langeveld\insert.php on line 37

 

Here's my code for the form:

 

    
<?php	
require_once("connect.php");

include('menu.html');
?>

    <form action="insert.php" name="add_header" method="post">  

<fieldset>
<p><label for="vendor">Vendor </label><input name="vendor" id="vendor" type="text" size="30" /></p> 
	<p><label for="date">Date </label><input name="date" id="date" type="text" size="12" /></p>
<p><label for="invoice">Invoice </label><input name="invoice" id="invoice" type="text" size="20" /></p>
<p><label for="total">Total Amount </label><input name="total" id="total" type="text" size="8" /></p> 
<p><label for="tax">Tax Amount </label><input name="tax" id="tax" type="text" size="8" /></p>
        
    </fieldset>
    <input type="submit" name="submit" value="submit">  
    </form> 
     
    </body>  
    </html>  

 

and here is the code for insert.php

 

	
<?php

require_once("connect.php");

include('menu.html');
	   
$vendor = mysql_real_escape_string($_POST['vendor']);
$date = mysql_real_escape_string($_POST['date']);
$invoice = mysql_real_escape_string($_POST['invoice']);	
$total = mysql_real_escape_string($_POST['total']);
$tax = mysql_real_escape_string($_POST['tax']);	

$insert	= "INSERT INTO tblPurchasesHeader (Vendor, InvoiceDate,Invoice,TotalAmount,TaxAmount) VALUES (".$vendor."','".$date."','".$invoice."','".$total."','".$tax."')";

    $conn->Execute($insert); 

echo "The information has been submitted.";

    ?>  

 

Thank you for your help!

 

 

[Donald.]

I might be wrong as I'm not a pro, but try the following.

 

Replace:

$conn->Execute($insert); 
echo "The information has been submitted.";

 

With:

if(mysql_query($insert)){ 
echo "The information has been submitted.";
}

 

 

[perplexia]

Thanks Donald! That's a great suggestion. I tried it and now I don't get any error message, but it still does not insert the new data into the database. Basically nothing happens. I thought I needed the "execute" command because it was MS Access... but I'm not sure. Any idea why it's not inserting the data?

[andrew_biggart]

With most insert issues it's normally a spelling error with one or the variables, table names within the actual sql query. Double check everything is exactly how it should be. I don't think you need the following either.

 

Your variables are currently like this

".$vendor."'

but all you really need is this

'$vendor'

.

[Donald.]

With most insert issues it's normally a spelling error with one or the variables, table names within the actual sql query. Double check everything is exactly how it should be. I don't think you need the following either.

 

Your variables are currently like this

".$vendor."'

but all you really need is this

'$vendor'

.

 

You're totally right, he missed the first ' in the insert statement.

 

This should do the trick.

	$insert	= "INSERT INTO tblPurchasesHeader (Vendor, InvoiceDate,Invoice,TotalAmount,TaxAmount) VALUES ('".$vendor."','".$date."','".$invoice."','".$total."','".$tax."')";

 

[andrew_biggart]

I still don't think you need the ". before and ." after each variable either. But I maybe wrong.

[boompa]

Sort of an aside, having not using MS Access for the last 15+ years and never from PHP. I'm a bit perplexed as to why you'd use mysql_real_escape_string on values being passed to an Access database? Does that work?

[perplexia]

Sort of an aside, having not using MS Access for the last 15+ years and never from PHP. I'm a bit perplexed as to why you'd use mysql_real_escape_string on values being passed to an Access database? Does that work?

 

  I guess it was just a habit. I don't know if it works - haven't tested it.

[perplexia]

I still don't think you need the ". before and ." after each variable either. But I maybe wrong.

 

Thanks for you help! it's always something simple!

[cyberRobot]

I still don't think you need the ". before and ." after each variable either. But I maybe wrong.

 

 

That is correct. Since the overall string is within double quotes, the following should work:

 

<?php
$insert = "INSERT INTO tblPurchasesHeader (Vendor, InvoiceDate,Invoice,TotalAmount,TaxAmount) VALUES ('$vendor','$date','$invoice','$total','$tax')";
?>

[Barand]

Sort of an aside, having not using MS Access for the last 15+ years and never from PHP. I'm a bit perplexed as to why you'd use mysql_real_escape_string on values being passed to an Access database? Does that work?

 

mysql_real_escape_string() requires a mysql connection therefore it won't work.

[andrew_biggart]

Please mark this topic as solved.

[perplexia]

Please mark this topic as solved.

 

I will - as soon as I can test it to see if it works.

[perplexia]

hmmm... all your replies make perfect sense to me, but it's still doesn't work for some reason. I don't get any error messages, but it doesn't insert the data into the DB. Maybe there's a problem with the database itself? could it have something to do with the format of the columns ie: number, text, date, currency?

 

Here's my updated code:

//declare variables
$vendor = ($_POST['vendor']);
$date = ($_POST['date']);
$invoice = ($_POST['invoice']);	
$total = ($_POST['total']);
$tax = ($_POST['tax']);	

//insert the data
$insert = "INSERT INTO tblPurchasesHeader (Vendor,InvoiceDate,Invoice,TotalAmount,TaxAmount) VALUES ('$vendor','$date','$invoice','$total','$tax')";	


if(mysql_query($insert))
  {
  echo "your information was submitted";
  }
else
  {
  echo "your information was not submitted";
  }

 

Thanks again for all your help!

[cyberRobot]

Have you tried going back to your original code for executing the query?

 

<?php

$conn->Execute($insert);

?>

 

 

I would imagine that the Execute() method handles the mysql_query() part.

[Jessica]

Are you doing Access or MySQL? If you're using Access, none of your variables will be correct, and should cause errors.

 

The original code is using the ADODB library.

 

At the top of the code add

error_reporting(E_ALL); 
ini_set('error_reporting', 1);

 

Then change $conn->Execute($insert);  to:

if ($conn->Execute($insert) === false) {
         print "<p>SQL: $insert <br>Error: {$conn->ErrorMsg()}</p>";
}

[perplexia]

Have you tried going back to your original code for executing the query?

 

<?php

$conn->Execute($insert);

?>

 

 

I would imagine that the Execute() method handles the mysql_query() part.

 

yes, when I do that I get this error:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> ADODB.Connection<br/><b>Description:</b> Operation is not allowed when the object is closed

[andrew_biggart]

Not sure if this is making a difference but I think there may be a problem with how you are declaring your variables.

 

Try changing this:

        $vendor = ($_POST['vendor']);
$date = ($_POST['date']);
$invoice = ($_POST['invoice']);	
$total = ($_POST['total']);
$tax = ($_POST['tax']);

 

to this:

        $vendor  = $_POST['vendor'];
$date    = $_POST['date'];
$invoice = $_POST['invoice'];	
$total   = $_POST['total'];
$tax     = $_POST['tax'];

 

[jcbones]

You are not connected to the database, the error tells you the source of the problem is the connection, and the reason is that the object is closed.  You need to go back and check your connections to see why it isn't open.

[xyph]

You still haven't cleared up the most important part.

 

Are you using MS Access or MySQL?

 

Where is $conn defined? What kind of object does it hold?

[perplexia]

...Are you using MS Access or MySQL?...

 

I am using MS Access.

[perplexia]

Where is $conn defined? What kind of object does it hold?

 

$insert = "INSERT INTO tblPurchasesHeader (Vendor, InvoiceDate,Invoice,TotalAmount,TaxAmount) VALUES (".$vendor."','".$date."','".$invoice."','".$total."','".$tax."')";

 

$conn->Execute($insert);

[perplexia]

Have you tried going back to your original code for executing the query?

 

<?php

$conn->Execute($insert);

?>

 

 

yes, I am using this code presently, but I still get a "object closed" error.

[perplexia]

You are not connected to the database, the error tells you the source of the problem is the connection, and the reason is that the object is closed.  You need to go back and check your connections to see why it isn't open.

 

makes sense to me. how do I check the connection?

 

Thanks.

[perplexia]

Where is $conn defined? What kind of object does it hold?

 

...sorry, I think this is what you were looking for:

 

$conn is defined in connect.php as

$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");