Page 1 of 1

Why can I not insert into my Access database?

Posted: Fri Apr 21, 2006 12:35 pm
by mercuryinhell
Here is my code:

Code: Select all

<?php
$db_connection = new COM("ADODB.Connection"); 

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("fpdb/quote.mdb") ." ;DefaultDir=". realpath("../"); 
$db_connection->open($db_connstr); 
$db_connection = "Insert Into customer_q (name, address, city)
Values ('$name', '$address', '$city')";

php?>
It does not return an error, but no data is entered into the table.
Any ideas?

Small change

Posted: Fri Apr 21, 2006 1:00 pm
by mercuryinhell

Code: Select all

<?php
$db_connection = new COM("ADODB.Connection"); 
$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("fpdb/quote.mdb") ." ;DefaultDir=". realpath("../"); 
$db_connection->open($db_connstr) or die('Could    Not Connect to Database!');
$db_connection = "Insert Into customer_q (name, address, city)
Values ('$name', '$address', '$city')";
php?>
I added:

or die('Could Not Connect to Database!');

On line 3 and now it returns "Could Not Connect to Database!"

Any ideas? I simply took my connection string on how to get results from the database, and used it here. It works to get results from the database.

Change of plan...

Posted: Fri Apr 21, 2006 2:43 pm
by mercuryinhell

Code: Select all

<?php

//global variables
$expr1 = $_POST["expr1"];
$msrp = $_POST["msrp"];
$type = $_POST["type"];
$make = $_POST["make"];
$expr2 = $_POST["expr2"];
$msrpA = $_POST["msrpA"];
$FitMK = $_POST["FitMK"];
$name = $_POST["name"];
$address = $_POST["address"];
$city = $_POST["city"];
$state = $_POST["state"];
$zip = $_POST["zip"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$dob = $_POST["dob"];
$ssn = $_POST["ssn"];
$salesman = $_POST["salesman"];
$date = $_POST["date"];
$qexpire = mktime(0, 0, 0, date("m")  , date("d")+7, date("Y"));
$tax = $_POST["tax"];
$title = $_POST["title"];
$total = $_POST["total"];
$acclist = $_POST["acclist"];
$quotenum = ($_POST["quotenum"]-1);
$acctax = $_POST["acctax"];
$acctotal = $_POST["acctotal"];
$outthedoor = $_POST["outthedoor"];

// connect to ODBC
$db_connection = new COM("ADODB.Connection"); 
$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("fpdb/quote.mdb") ." ;DefaultDir=". realpath("../"); 
$db_connection->open($db_connstr); 

/* run insert */ 
$stmt = odbc_prepare($db_connstr, "INSERT INTO customer_q (quotenum, date, name, address, city, state, zip, ssn, dob, phone, email, salesman, vehicle1, accessory0) 
VALUES('$quotenum', '$date', '$name', '$address, '$city', '$state', '$zip', '$ssn', '$dob', '$phone', '$email', '$salesman', '$vehicle1', '$accessory0');" ); 

/* check for errors */
if (!odbc_execute( $stmt))
{
/* error */
echo "Whoops<BR>";
}

echo $quotenum
?>
Viewing the source code, I get:

PHP Warning: odbc_prepare(): supplied argument is not a valid ODBC-Link resource in test5.php on line 82
PHP Warning: odbc_execute(): supplied argument is not a valid ODBC result resource in test5.php on line 85

Line 82 is:

Code: Select all

VALUES('$quotenum', '$date', '$name', '$address, '$city', '$state', '$zip', '$ssn', '$dob', '$phone', '$email', '$salesman', '$vehicle1', '$accessory0');" );
Line 85 is:

Code: Select all

if (!odbc_execute( $stmt))

Anyone?

Posted: Mon Apr 24, 2006 11:34 am
by mercuryinhell
Bump....

I changed to this

Posted: Mon Apr 24, 2006 3:05 pm
by mercuryinhell

Code: Select all

<?php
error_reporting(E_ALL); 
//global variables
$expr1 = $_POST["expr1"];
$msrp = $_POST["msrp"];
$type = $_POST["type"];
$make = $_POST["make"];
$expr2 = $_POST["expr2"];
$msrpA = $_POST["msrpA"];
$FitMK = $_POST["FitMK"];
$name = $_POST["name"];
$address = $_POST["address"];
$city = $_POST["city"];
$state = $_POST["state"];
$zip = $_POST["zip"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$dob = $_POST["dob"];
$ssn = $_POST["ssn"];
$salesman = $_POST["salesman"];
$date = $_POST["date"];
$qexpire = mktime(0, 0, 0, date("m")  , date("d")+7, date("Y"));
$tax = $_POST["tax"];
$title = $_POST["title"];
$total = $_POST["total"];
$acclist = $_POST["acclist"];
$quotenum = ($_POST["quotenum"]-1);
$acctax = $_POST["acctax"];
$acctotal = $_POST["acctotal"];
$outthedoor = $_POST["outthedoor"];

// connect to ODBC
$db_connection = new COM("ADODB.Connection"); 
$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("fpdb/quote.mdb") ." ;DefaultDir=". realpath("fpdb/quote.mdb")or die('Could Not Connect to ODBC Database!'); 
$db_connection->open($db_connstr); 

/* run insert */ 
$stmt = odbc_prepare($db_connstr, "INSERT INTO customer_q 
(quotenum, date, name, address, city, state, zip, ssn, dob, phone, email, salesman, vehicle, accessory, outthedoor) VALUES('$quotenum', '$date', '$name', '$address', '$city', '$state', '$zip', '$ssn', '$dob', '$phone', '$email', '$salesman', '$expr1', '$acclist', '$outthedoor')");

/* check for errors */
if (!odbc_execute( $stmt))
{
/* error */
echo "Whoops<BR>";
}
echo $quotenum . odbc_errormsg()
?><BR><BR>
The page prints "whoops" but does not give any error code. The table fails to be updated.
Do I have the connection string in the wrong context?
What am I missing?

Posted: Mon Apr 24, 2006 3:25 pm
by timvw
Your first (or second) post indicated that On line 3 and now it returns "Could Not Connect to Database!"

You've changed nothing, so you still can't connect (and thus it doesn't matter what you change after that code, since you're not connected...)

When i created a directory fpdb with (access) quote.mdb file in it the following code did work:

Code: Select all

$com = new COM('ADODB.Connection');
$connstr = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' . realpath('fpdb/quote.mdb'); 
$com->open($connstr); 
$rs = $com->execute('CREATE TABLE test ( id INT NOT NULL, PRIMARY KEY (id))');

Posted: Mon Apr 24, 2006 5:43 pm
by RobertGonzalez
Have you thought about using a DSN?

Posted: Mon Apr 24, 2006 5:52 pm
by n00b Saibot
or changing you connection string to this

Code: Select all

$connstr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' . realpath('fpdb/quote.mdb');
try this one. i am sure your problem will be solved...

Error

Posted: Tue Apr 25, 2006 7:48 am
by mercuryinhell
This produces:
Could Not Insert Into ODBC Database!
PHP Warning: odbc_prepare(): supplied argument is not a valid ODBC-Link resource in D:\NSPowerSports\test5.php on line 89

Code: Select all

// connect to ODBC
$db_connection = new COM("ADODB.Connection"); 
$db_connstr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' . realpath('fpdb/quote.mdb') or die('Could Not Connect to ODBC Database!'); 
$db_connection->open($db_connstr); 

/* run insert */ 
$stmt = odbc_prepare($db_connstr, "INSERT INTO customer_q ([quotenum], [date], [name], [address], [city], [state], [zip], [ssn], [dob], [phone], [email], [salesman], [vehicle], [accessory, [outthedoor]) VALUES('$quotenum', '$date', '$name', '$address', '$city', '$state', '$zip', '$ssn', '$dob', '$phone', '$email', '$salesman', '$expr1', '$acclist', '$outthedoor')") or die('Could Not Insert Into ODBC Database!'); 
/* check for errors */
if (!odbc_execute( $stmt))
{
/* error */
echo "Whoops<BR>";
}
echo $quotenum . odbc_errormsg()

?><BR><BR>

Posted: Tue Apr 25, 2006 10:26 am
by RobertGonzalez
Seriously, look into setting up a DSN. I was developing a PHP4 app with a MS Access backend and could not connect at all regardless of the connection string. As soon as I set up a DSN and used the DSN connection string I was in with no more problems.

I don't know the difference...

Posted: Tue Apr 25, 2006 12:16 pm
by mercuryinhell
Sorry to be such a N00B, but why would this work, and using my connection string above, how would it need altered to make it DSN?

Sorry to suck so bad at this.

Posted: Tue Apr 25, 2006 12:42 pm
by RobertGonzalez
Try googling 'Connect to Access with PHP DSN' or something along those lines. It has been such a long time since I set one up that I have almost forgotten what it takes to do it. I do now that google and I were best friends at the time though :wink: .