Inconsistent Syntax Error

mvarian · #1 (**permalink**) 12-07-04, 13:48

I have the chunk of code below. The exact same code with just a slightly different query to access a different database works fine in another script elsewhere on the site, but for some reason this page alone always throws the error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement., SQL state 37000 in SQLExecDirect in C:\Inetpub\wwwroot\fuel\test.php on line 29
INSERT INTO mreports (Year, Month, File, Order) VALUES ('2003', 'Jan', 'monthlyreports/test.pdf', '1')
Record not added, an error occurred : 37000

any ideas???? I'm stumped!

Thanks

PHP Code:


			
// connect to access database

                    $cfg_dsn = "DRIVER=Microsoft Access Driver (*.mdb);

                    DBQ=C:/Inetpub/wwwroot/monthly_fuel.mdb;

                    UserCommitSync=Yes;

                    Threads=3;

                    SafeTransactions=0;

                    PageTimeout=5;

                    MaxScanRows=8;

                    MaxBufferSize=2048;

                    php DriverId=281;

                    DefaultDir=C:/ProgramFiles/CommonFiles/ODBC/DataSources";



                    $cfg_dsn_login = "";

                    $cfg_dsn_mdp = "";

            

                $connection = odbc_connect($cfg_dsn,$cfg_dsn_login,$cfg_dsn_mdp);

                $command = "INSERT INTO mreports (Year, Month, File, Order) VALUES ('2003', 'Jan', 'monthlyreports/test.pdf', '1')";

                if(odbc_exec($connection, $command)) {

guelphdad · #2 (**permalink**) 12-07-04, 20:24

You are better off to ask this in another forum since this one is for mysql queries. there is a forum for Microsoft SQL Server and Other forums where you are likely to ge an answer.

healdem · #3 (**permalink**) 12-08-04, 03:36

I'd of thought the PHP forum would be more appropriate

but in the db how is the column year defined, numeric or alpha?, the script suggests alpha

mvarian · #4 (**permalink**) 12-09-04, 12:32

Sorry if I misplaced this, my reasoning was that the error was a sql statement syntax error so it would've fallen under the mysql category.

The year field is defined as a number, as is the Order, everything else is numeric. I've tried removing the quotes and I get the same error, and actually in a working statement I have elsewhere for another DB and table I use the quotes and it works...

healdem · #5 (**permalink**) 12-09-04, 13:15

Good practise would indocate the that you inform the db that its a string by using quotes, a number doen't use quotes.

I'd try rerunning the query by removing all quotation marks form numeric fields and see what happens.

It is entirely possible that PHP may make decisions for you as to what the column type should be, but why rely on someone else. If its numeric make it explicitly numeric
HTH

White Knight · #6 (**permalink**) 12-09-04, 13:51

You just about managed to get a full row of Reserved Words here!

OK so you can use reserved words but you have to enclose them in [ ]

$command = "INSERT INTO mreports ([Year], [Month], [File], [Order]) VALUES ('2003', 'Jan', 'monthlyreports/test.pdf', '1')";

mvarian · #7 (**permalink**) 12-09-04, 14:46

Ah that was it!!! (reserved words)

THANK YOU! I've had a headache for a week over this!

Internet Services

Web Development

Digital Marketing

Consumer Tech