Page 1 of 1

Take pity on a Newbie? Error with an Update query!

Posted: Wed Aug 14, 2002 2:01 pm
by johnbran
I am attempting to update a record using the following code:

Code: Select all

$field_str='';

$field_str .= " ratd_Column11= '$ratd_Column11',";
$field_str .= " ratd_Column12= '$ratd_Column12',";
$field_str .= " ratd_Notes= '$ratd_Notes' ";

$query="UPDATE Rates SET $field_str WHERE Rates_Id='$Rates_Id'";
$result=odbc_exec($odbc_db, $query);
if(!$result) die ("Error executing query $query");
}
I get the following error when executing this code:

Error converting data type varchar to numeric.

The 2 fields (column11 and column12) are decimal data types btw. Also, when I take the quotes ' ' off of '$ratd_Column11' and '$ratd_Column12' I get this error:

Incorrect syntax near ','.,

Can someone shed some light on this? What am I missing? :?

Posted: Wed Aug 14, 2002 2:07 pm
by EricS
You can't use double quotes when you want to evaluate a variable. Try this.

Code: Select all

$field_str=''; 

$field_str .= ' ratd_Column11= $ratd_Column11,'; 
$field_str .= ' ratd_Column12= $ratd_Column12,'; 
$field_str .= ' ratd_Notes= $ratd_Notes'; 

$query='UPDATE Rates SET $field_str WHERE Rates_Id=$Rates_Id'; 
$result=odbc_exec($odbc_db, $query); 
if(!$result) die ("Error executing query $query"); 
}
I haven't tested it, but it should work.

Posted: Wed Aug 14, 2002 2:13 pm
by sam
No that won't work try this:

Code: Select all

$field_str=''; 

$field_str .= "ratd_Column11= '". $ratd_Column11."',"; 
$field_str .= " ratd_Column12= '".$ratd_Column12."',"; 
$field_str .= " ratd_Notes= '".$ratd_Notes; 

$query="UPDATE Rates SET ".$field_str." WHERE Rates_Id='".$Rates_Id".'"; 
$result=odbc_exec($odbc_db, $query); 
if(!$result) die ("Error executing query $query"); 
}
Cheers Sammo

Posted: Wed Aug 14, 2002 2:38 pm
by llimllib
Well, IMHO, both of you are wrong. His syntax in the first post looks correct to me, and in fact should work. Eric, you had it backwards, you *have to* use double quotes to evaluate a variable (try echo "$var";echo'$var'; ). Sam, your syntax is correct but really just more confusing, and, to my eye (god know that it's been wrong before) unneccessary.
Seems to me that the problem's with the DB. try this: print out the query right before it happens, then use a command-line interface to your DB to try the query from there. See what errors it gives you (or use the ODBC error function, which I don't mention because I don't use ODBC so I don't know what that is)

Posted: Wed Aug 14, 2002 2:50 pm
by johnbran
Unfortunately your kind suggestions did not work :cry: I neglected to mention that my code works beautifully if I take out ratd_Column11 and ratd_Column12 fields in the query. Those 2 fields are decimal data types and the others are varchar data types. So, I tried your suggestions with just the decimal fields and still no luck. But again, my setup works fine if I'm not trying to use columns 11 & 12. Any other thoughts? :?:

This is my original code.....that works just fine.

Code: Select all

$field_str='';

$field_str .= " ratd_Column01= '$ratd_Column01',";
$field_str .= " ratd_Column02= '$ratd_Column02',";
$field_str .= " ratd_Column03= '$ratd_Column03',";
$field_str .= " ratd_Column04= '$ratd_Column04',";
$field_str .= " ratd_Column05= '$ratd_Column05',";
$field_str .= " ratd_Column06= '$ratd_Column06',";
$field_str .= " ratd_Column07= '$ratd_Column07',";
$field_str .= " ratd_Column08= '$ratd_Column08',";
$field_str .= " ratd_Column09= '$ratd_Column09',";
$field_str .= " ratd_Column10= '$ratd_Column10',";

If I add the following 2 lines for 11 and 12...this code doesn't work.

Code: Select all

$field_str .= " ratd_Column11= '$ratd_Column11',";
$field_str .= " ratd_Column12= '$ratd_Column12',";
$field_str .= " ratd_Notes= '$ratd_Notes' "; 

$query="UPDATE Rates SET $field_str WHERE
                           Rates_Id='$Rates_Id'"; 
                           $result=odbc_exec($odbc_db, $query); 
                           if(!$result) die ("Error executing query $query"); 
                           }

Posted: Wed Aug 14, 2002 3:04 pm
by johnbran
8O

I figured it out....thanks to you guys though!

I did the following:

Code: Select all

$field_str .= " ratd_Column11= '".$ratd_Column11."',";
$field_str .= " ratd_Column12= '".$ratd_Column12."',";
$field_str .= " ratd_Notes= '$ratd_Notes'";

$query="UPDATE Rates SET $field_str WHERE
                           Rates_Id='$Rates_Id'"; 
                           $result=odbc_exec($odbc_db, $query); 
                           if(!$result) die ("Error executing query $query"); 
                           }
And voila! It worked!!!!

Thank you sooooo much!

Posted: Wed Aug 14, 2002 3:15 pm
by sam
llimllib wrote:Sam, your syntax is correct but really just more confusing, and, to my eye (god know that it's been wrong before) unneccessary.
Yep I like to do stuff the hard way... :) no I just phped and it hilights variables only when they are not in a "'ed string so I have just adapted to that style.

Cheers Sam

Posted: Wed Aug 14, 2002 3:20 pm
by llimllib
I guess i'll forgive you this time</sarcasm>. I have to hold myself back from rhyming about it. :)