Why can I not insert into my Access database?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

Why can I not insert into my Access database?

Post 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?
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

Small change

Post 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.
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

Change of plan...

Post 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))
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

Anyone?

Post by mercuryinhell »

Bump....
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

I changed to this

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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))');
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have you thought about using a DSN?
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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...
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

Error

Post 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>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
mercuryinhell
Forum Newbie
Posts: 18
Joined: Wed Apr 19, 2006 1:24 pm

I don't know the difference...

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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: .
Post Reply