1

In PHP I am connecting to an access database using ODBC. I update several fields no problem, but I am having a hell of a time getting two of them to work.

First one is of type date/time in access.

Second one is of type memo in access.

$mdbFilename = "pathTo.mdb";
$cimdb = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password);

$sqlInsert = "UPDATE MyTable ";
$sqlInsert .= "SET ";
$sqlInsert .= "Time='07:30:00 AM', ";
$sqlInsert .= "Note='My really long note here...' ";
$sqlInsert .= " WHERE ID=777";
$res = odbc_exec($cimdb, $sqlInsert);

The code produces.....

UPDATE MyTable SET Time='07:30:00 AM', Note='My really long note here...' WHERE ID=555

I have tried soo many things from casting to converting, to different types of spacing/formatting. I really hope someone has done this before.

What I really need to know is What format do I put the data in to get the access DB to accept the input?

Here is the error it throws...

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement., SQL state 37000 in SQLExecDirect in H:\web\count\countInject.php on line 116
SQL statement failed with error: 37000: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Thank you MUCH for reading this through, and double thanks if you help me out =)

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Andrew
  • 69
  • 1
  • 10
  • 2
    Is it possible that thare are ' marks in your really long quote? You should take a look at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php for the "ugly" mssql solution to escaping strings. – Carl F. Sep 20 '11 at 00:48
  • No, that is literally the string I am using to test the MEMO field. nothing special, I can fix/secure special junk after I figure out how to get a very simple string into a MEMO datatype. – Andrew Sep 20 '11 at 15:57
  • adding square brackets to 'Note' fixed it $sqlInsert .= "[Note]='My really long note here...' "; – Andrew Sep 20 '11 at 16:07

1 Answers1

2

When debugging, I find it helpful to break problems into the smallest chunks possible. For example, is it possible to separate the datetime update from the memo update to ensure you're troubleshooting the right problem.

For Access datetime values, it expects a format of #yyyy-mm-dd HH:MM:SS# from Formatting Date Time Values for Access SQL Ignore the delphi bit, just translate it to PHP.

For Memo, it doesn't look like there's any real special tricks. However, you might be running into an unescaped parenthesis in your source.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 1
    If those are real field names, the OP is running into reserved words (http://office.microsoft.com/en-us/access-help/sql-reserved-words-HP001032249.aspx). These need to be enclosed in square brackets. – Fionnuala Sep 20 '11 at 11:20
  • Thanks for the reply! =) I am working on these 2 issues separately. Let us assume my PHP is golden and just look the SQL it produces. (since frankly I am just putting it directly in now the test these fields) – Andrew Sep 20 '11 at 15:59
  • Here are the statements I have tried... `UPDATE CountData SET Time='#2000-01-01 01:00:00#' WHERE CountID=555 UPDATE CountData SET Time=#2000-01-01 01:00:00# WHERE CountID=555 UPDATE CountData SET Time='2000-01-01 01:00:00' WHERE CountID=555 UPDATE CountData SET Time=2000-01-01 01:00:00 WHERE CountID=555 ` Every one still gives me: **Syntax error in UPDATE statement., SQL state 37000 in SQLExecDirect in** – Andrew Sep 20 '11 at 16:01
  • @Remou THANK YOU! now I get a type mismatch, so partly solved, working on it now I'll let you all know what end up at. – Andrew Sep 20 '11 at 16:04
  • ok I fixed the time using `$sqlInsert .= "[Time]='01:00:00', ";` – Andrew Sep 20 '11 at 16:05
  • and fixed the MEMO just by putting square brackets on the field name THANK EVERYONE FOR KEEPING ME SANE!!! ...and making me realize I really need to check for reserved words. I didn't write the database so I never thought about it. – Andrew Sep 20 '11 at 16:06