Access SQL query problems

[ryanh_106]

Hi

I am trying to manipulate a microsoft access database (with DSN) using ODBC funcitons in PHP. I can connect, close and query fine but it will not let me run this update command. Can anyone tell me why not I have been searching the internet for hours


[code]$sql = "SELECT * FROM Customers WHERE 1";
$myresults = db_query($sql);  // Wraps odbc_exec

print '<br /><br />I have '.no_of_results($myresults).' Customers, They Are:<br />';
print_results($myresults); // Wraps odbc_result_all

while ($row = next_row($myresults)) { // next_row = odbc_fetch_array
    $sql = "UPDATE Customers c SET c.Password = \"".md5($row['Password'])."\" WHERE c.CustID = '{$row['CustID']}'";
    db_update($sql); // Wraps odbc_exec
}

[/code]


Error is:

Could not execute update, Error message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.!
Query was:
UPDATE Customers c SET c.Password = "a029d0df84eb5549c641e04a9ef389e5" WHERE c.CustID = '1'

(its a nice error I defined to include the original query thats not straight from ODBC obviously)
Tried with all types of quotes, the field names are definately right, only thing i can poss think may be causing it is CustID is auto number??? could that be it?

Please help

Cheers

[trq]

A few things....i think. For starters, the [i]c[/i] bit, I dont understand it.

Password is more than likely a reserved word in access so i would change that filed name.

Access does not like INTEGER values being surrounded by quotes, and all other values should be surrounded by single quotes.

Try...
[code]
$sql = "UPDATE Customers SET newPassword = '".md5($row['Password'])."' WHERE CustID = {$row['CustID']}";
[/code]
Dont forget to change the Password field name.

[ryanh_106]

Thanks for that ace advice,

Its funny as soon as you mention it I have had trouble using the word Password in a MySQL database before

Anyway I will look at that again later thank you

and the c is a reference name for the customers name. In SQL writing
FROM Customers c
means later on you can refer to fields as c.username etc. Mainly for if you are doing are doing a join query with two tables that contain fields with the same name, it was just something i tried, heres an example

SELECT CustName, OrderNumber
FROM Customers c, Odrers o
WHERE (CustName = 'Me' AND c.CustID = o.CustID)

Anyway, there you have it

Thanks for the help