Update with NULL value

[Benbunny]

Hi all,

Can anyone tell me how to update the record with the null value. The action script as follows:

<cfquery name=&quot;updatelog&quot; datasource=&quot;Shipping Management&quot;>
update log
set logdate='#dateformat(form.ilogdate, &quot;mm/dd/yyyy&quot#', type='#form.itype#', vesselid='#form.ivesselid#',
voyage='#form.ivoyage#', lastport='#form.ilastport#',
departuredate='#dateformat(form.ideparturedate, &quot;mm/dd/yyyy&quot#', callport='#form.icallport#',
position='#form.iposition#', remark1='#form.iremark1#',
activity='#form.iactivity#', activitytype='#form.iactivitytype#',
etaoretd='#form.ietaoretd#', etdate='#dateformat(form.ietdate, &quot;mm/dd/yyyy&quot#'
where logid='#form.ilogid#'
</cfquery>

When pressing the submit button, the following error will be shown as follow:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[MERANT][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near ','.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:62).

Thanks a lot

 

[JNC73]

Hello Benbunny,

If you want to update a field with the NULL value your statement should be something like this.


UPDATE tablename
SET field1 = NULL
WHERE field2 = 'xxx'.


This means that around the value there are no quotes.

Hope this helps.

JNC73

 

[Benbunny]

Hi JNC73,

Sorry, I think you get the wrong message or may be my represent skill is not good.

I wanna to know is that when user inputs the fields of a record, may be the user doesn't touch some of the input fields. So the fields may still empty.

When I running the cfquery I shown before, the error may occur. Cause some value of the fields of the form is null.

Thanks JNC73 kindly help!
Can anyone help me!!

Benbunny

 

[caibang]

Create a new variable and check for NULL before you actual do an update statement.

ie

if (a eq null)
a = null

update table set f1 = a
where f2 = 'xxx'