SQL Insert Error

[Matthew Paterson]

Ok I'm having a problem with a SQL insert going to a Access database...here
is the connection code:

// PHP db connection
$db = 'C:\\Phpweb\\test.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
$query = $conn->Execute($sql);

Here is the print out of the SQL:

INSERT INTO
[Cards]([active],[division],[province],[filetitle],[air],[water],[sewage],[p
ttw],[partVIII],[part46]) VALUES ('1','ob','Ontario','0 0 0
0','1','1','1','1','1','1')

Here is the errors:

Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
JET Database Engine Description: Syntax error in INSERT INTO statement. in
c:\phpweb\admin.card.view.php on line 193

Warning: (null)(): Invoke() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 196

Warning: main(): PropGet() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 197

Any help would be greatly appricated!!

Thanks

[Terence]

Matthew Paterson wrote:

Ok I'm having a problem with a SQL insert going to a Access database...here
is the connection code:

// PHP db connection
$db = 'C:\\Phpweb\\test.mdb';
$conn = new COM('ADODB.Connection');

This is going to be expensive and less than reliable.

Do yourself a favour and use PEAR:DB
you can change to a real RDBMS later and your code will be none the
wiser. Here is a class I wrote as part of an MSAccess db app.

You need only look at the constructor if you don't want to use the rest.

Warning: the ODBC driver for PEAR:DB has a bug. It is designed to work
with unix style odbc driver. Go to c:/php/PEAR/DB/odbc.php and change

$conn = @$connect_function($dbhost, $user, $pw);
to
$conn = @$connect_function($dsninfo['database'], $user, $pw);

on line 134

I spoke to the maintainer of odbc.php and the fault as acknowledged but
left alone.

if you have no pear directory, then go the a dos command window, change
to your php directory, and type "go-pear" (make sure you are connected
to the net).
<?php

include_once "db.php";
include_once "class_Exception.php";

class sopsDb {

var $strUser ="";
var $strPass ="";
var $strDBname = "sops";
var $strHost = "localhost";
var $strDbType = "odbc";
var $dsn;
var $db;

function sopsDb() {
$this->dsn =
$this->strDbType."://"
// .$this->strUser.":"
// .$this->strPass."@"
.$this->strHost."/"
.$this->strDBname
;
if (DB::isError($this->db = DB::connect($this->dsn)))
new
Exception($this->db->getMessage().":\n".$this->db->getUserinfo(),true);
}

function &query($sql,$complain=true) {
$res =& $this->db->query($sql);
if($complain) $this->DBerrorCheck($res,$sql);
return $res;
}

function DBerrorCheck($obj,$sql) {
if (DB::isError($obj)) {
$err = $obj->getMessage().":\n".$obj->getUserinfo();

if(stristr($obj->getUserinfo(),"duplicate values"))
$err = "The name you specified already exists.";

new Exception($err,true);
}
}

function strMSEscapeSingleQuotes(&$arrPayload,$arrFields) {
foreach($arrFields AS $field) {
$arrPayload[$field] =
str_replace("'","''",$arrPayload[$field]);
}
}

function strOdbcDateTimeFormat($intTs=0) {
if(!$intTs) $intTs=time();
// { ts '1998-05-02 01:23:56.123' }
//
http://msdn.microsoft.com/library/en...on_03_04l0.asp
$strTs = "{ts'";
$strTs .= date("Y-m-d H:i:s",$intTs);
$strTs .= "'}";
return $strTs;
}

function MsSqlPrep(&$sql) {
$sql = str_replace(" '',"," NULL,",stripslashes($sql));
}

function escapeUri($uri) {
if(strpos($uri,"\\\\") === 0)
$uri = "file://".str_replace("\\","/",$uri);
return $uri;
}

function query2d($sql) {
$arr2d = array();
$objResTaskSystems = $this->query($sql);
while($arr2d[] = $objResTaskSystems->fetchRow(DB_FETCHMODE_ASSOC));
if(is_array($arr2d)) array_pop($arr2d);
return $arr2d;
}
}

?>