form update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

richy2424 - 31 Mar 2006 01:28 GMT

i have a form action page with the code below to update my table

what i need is if one of the text fields are left blank to enter a null value

at the min i get a syntax error

<cfquery datasource="Cricket">
    UPDATE PlayersList_Table
    SET Nickname = '#Form.Nickname#',
    Email = '#form.Email#',
    Phone = '#form.Phone#',
    BatsmanType = '#form.BatsmanType#',
    BowlerType = '#form.BowlerType#',
    FavFielding = '#form.FavFielding#',
    FavBatting = #form.FavBatting#

    WHERE PlayerID=#form.PlayerID#
</CFQUERY>

Reply to this Message

Not Nick - 31 Mar 2006 02:30 GMT

I suggest that you validate the form before updating the database

ex:
<cfif trim(form.bowlertype) is "">
<cfset form.bowlertype = "hooked">
</cfif>

Or, in the query, check to make sure there is something entered for that field
before using it:

<cfquery datasource="Cricket">
    UPDATE PlayersList_Table
    SET
<cfif trim(Form.Nickname) is not "">
Nickname = '#Form.Nickname#',
</cfif>
<cfif trim(Form.Nickname) is not "">
    Email = '#form.Nickname#',
</cfif>
    Phone = '#form.Phone#',
    BatsmanType = '#form.BatsmanType#',
    BowlerType = '#form.BowlerType#',
    FavFielding = '#form.FavFielding#',
    FavBatting = #form.FavBatting#

    WHERE PlayerID=#form.PlayerID#
</CFQUERY>

etc, your problem with that solution is that you have to take care of the
comma for the last parameter, if     FavBatting = #form.FavBatting# is not used,
FavFielding = '#form.FavFielding#', <= note comma will produce an error.

I would go with the first solution and maybe also to modify the db to accept
nulls in some cases.

Reply to this Message

richy2424 - 31 Mar 2006 02:46 GMT

ok i have tried both but i still get an error, i am using access as my
database, the columns are just normal text fields i thought they could accept
null values

i just tried this with on field with no luck

<cfquery datasource="Cricket">
    UPDATE PlayersList_Table
    SET
    Password = '#Form.Password#'
    WHERE PlayerID=#form.PlayerID#
</CFQUERY>

Reply to this Message

Not Nick - 31 Mar 2006 02:56 GMT

What are the exact error messages?
Yo say it is all text fields, but there is no ' for the playerid field
WHERE PlayerID=#form.PlayerID#

try WHERE playerid='#form.PlayerID#'

also, try using lowercase for all fields and tablenames

Reply to this Message

Dan Bracuk - 31 Mar 2006 05:14 GMT

Your ultimate objective is get something like
fieldabc = 'the form data'
if the form field had something in it.
or
fieldabc = null
if it is empty.
The tricky part is the single quotes.

There are many approaches, all of which work. My preference is to process the
form variables first and build a set clause. Something like

setclause = "";
if (form.abc is "")
setclause = setclause & "fieldabc = '" & form.abc & "',";
else
setclause = setclause & " fieldabc = null,";
etc

then your query becomes
update thetable
set #PreserveSingleQuotes(SetClause)#
where whatever

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / March 2006

form update