Error on Insert on a Access Data Base

[slb813]

Hello,

I am having a problem that I can't seem to work out. I am running Apache 2.2 on a Windows XP platform, using PHP5. I am attempting to insert a row into a MS Access data base with a PHP script. I have a good connection into the data base, because select's work well.

The error that I am getting is:
Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5., SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\test.php on line 98 Error in SQL Insert statment please check code

from the statment:

INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT "Issue ID 1" AS Issue_Key, "steve" AS user, "7777" AS phone, "test information" AS issue, "10/30/2007" AS Issue_Date; 

Has anyone seen this error, and knows what the cause may be? I would appreciate any advice very much.

Thank You

[w0rKInPHP]

I don't use access too often but the last half of your query (SELECT "Issue ID 1" AS Issue_Key, "steve" AS user, "7777" AS phone, "test information" AS issue, "10/30/2007" AS Issue_Date) looks incomplete. Select from what table?

[slb813]

I don't use access too often but the last half of your query (SELECT "Issue ID 1" AS Issue_Key, "steve" AS user, "7777" AS phone, "test information" AS issue, "10/30/2007" AS Issue_Date) looks incomplete. Select from what table?

I don't work with access much either, this is a insert statment that I did with the GUI inside Access, then cut and pasted into the PHP code. When I cut the statment back into Access it works great with no error.

[code green]

SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5., 

This is usually because you are using a wrong field name

[slb813]

SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5., 

This is usually because you are using a wrong field name

I checked and double checked the field names, then I can cut the insert statment and past it into a Access query inside Access and it works. This leads me to think that the field names are OK.

Thanks

[code green]

Required syntax varies between Access DB and Access query in PHP.
Try replacing the double quotes with square brackets where there is a field name space.
In fact why are the quotes there?

INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT [Issue ID 1] AS Issue_Key, "steve" AS user, "7777" AS phone, [test information] AS issue, "10/30/2007" AS Issue_Date;

[slb813]

Required syntax varies between Access DB and Access query in PHP.
Try replacing the double quotes with square brackets where there is a field name space.
In fact why are the quotes there?

INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT [Issue ID 1] AS Issue_Key, "steve" AS user, "7777" AS phone, [test information] AS issue, "10/30/2007" AS Issue_Date;

I tried your suggestion:

$sql='INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT [Issue ID 1] AS Issue_Key, ['.$Fname.'] AS user, ['.$Fphone.'] AS phone, ['.$Fissuetext.'] AS issue, ['.$Fdate.'] AS Issue_Date;';
 

and still got the error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5., SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\test.php on line 97 Error in SQL Insert statment please check code

Thank you for your suggestion

[code green]

You have still got the semi colon within the query statement.
That is not required for any DB

AS Issue_Date;';

Change to

AS Issue_Date';

[w0rKInPHP]

I tried your suggestion:

$sql='INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT [Issue ID 1] AS Issue_Key, ['.$Fname.'] AS user, ['.$Fphone.'] AS phone, ['.$Fissuetext.'] AS issue, ['.$Fdate.'] AS Issue_Date;';
 

and still got the error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5., SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\test.php on line 97 Error in SQL Insert statment please check code

Thank you for your suggestion

So something is going on between the php source and the database. My first suggestion would be to output the query without posting to the database, copy it from the page and then paste it into the database. My guess is that maybe something like single quotes are not being escaped correctly or maybe some are missing. But you want to output the sql exactly as it is being passed to the database like

$sql='INSERT INTO problems (Issue_key, user, phone, issue, Issue_Date ) SELECT [Issue ID 1] AS Issue_Key, ['.$Fname.'] AS user, ['.$Fphone.'] AS phone, ['.$Fissuetext.'] AS issue, ['.$Fdate.'] AS Issue_Date;';
 
echo $sql;
 

then copy the output and paste that into your database to see if that works. If that doesn't work then try to get it to work and that may narrow down your issue.