Whats best way to insert '0' instead of have a 'Null' value in SQL 2000 database??

jeffmace
jeffmace used Ask the Experts™
on
If I am doing a update query:

<cfoutput query="countPrevAdPages">
<cfquery name="updatePrevAdPageCount" datasource="#DataTables#">
UPDATE prevAdSales
SET prevtotal='#totalpages#'
WHERE salesrep = #salesrep# AND issueID = #magazine_id#
</cfquery>
</cfoutput>

and the is no value for a few users, it automatically inserts a NULL value in the database.  What is the best way to have it insert a '0' instead of have a null value in the database.

Thanks,
Jeff
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Well, depending on how you want to handle this, you could set the default value in the DB to be 0. Thus, whenever you insert a record that doesn't have a value for that column, it will insert the default value.

For the case of updating a record, I would say the best thing to do would be to put something in the Cold Fusion like this:

<cfquery name="updatePrevAdPageCount" datasource="#DataTables#">
UPDATE prevAdSales
SET prevtotal=<cfif Len(Trim(totalpages))>'#totalpages#'<cfelse>0</cfif>
WHERE salesrep = #salesrep#
  AND issueID = #magazine_id#
</cfquery>

Commented:
the best way wld be to have default value as "0" set from the table itself

& in the query u chk

<cfif Len(Trim(totalpages))>
    <cfquery name="updatePrevAdPageCount" datasource="#DataTables#">
    UPDATE prevAdSales
    SET prevtotal='#totalpages#'
    WHERE salesrep = #salesrep#
     AND issueID = #magazine_id#
    </cfquery>
</cfif>

that way u avoid the query being executed as well.

K'Rgds
Anand
anandkp, the only problem with this is, if the current value of totalpages is lets say 12, and he wants to update it to null (zero actually is what he wants) then your query will not run and update the value.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Commented:
Better change the database field to NOT NULL and a default value of 0 (number or integer field)

Change the query in :

<cfquery name="updatePrevAdPageCount" datasource="#DataTables#">
UPDATE prevAdSales
SET prevtotal=#val(totalpages)#
WHERE salesrep = #salesrep# AND issueID = #magazine_id#
</cfquery>
Commented:
try with this.


<cfquery name="updatePrevAdPageCount" datasource="#DataTables#">
UPDATE prevAdSales
SET prevtotal=<cfif Trim(totalpages) is not "" >'#trim(totalpages)#'<cfelse>0</cfif>
WHERE salesrep = #salesrep#
 AND issueID = #magazine_id#
</cfquery>

we need trim in both checking and updating also.
Commented:
Hi there,

he will wish to update the record with "0" - only if the value retrieved from teh form is not null.

so in that case - during insert, the default value from the table wld have already gone as "0"

& he will update it with the new value only if he wants to change it from "0" to something else.

thats why i wrote my query in that <CFIF> condition earlier ... thinking the flow to eb this way ...

K'Rgds
Anand
Or he could create a trigger.

That is activated by an update to that table.

That if the field contained a null to update it to 0.

I have not written triggers, but i know stored procedure. Somehow it has to pass what records were updated. Then create a stored procedure to update that field.

create procedure update_total
(@record_id in integer)
as

declare @p_total varchar;

begin

cursor read_total local as
select prevtotal
from prevadsales
where id = @record_id

open read_total
fetch read_total into @p_total

if @p_total is null then
  update prevtotalsales
  set prevtotal = 0
  where id = @record_id
end if;

close read_total
deallocate read_total

end;

Commented:
Hi jeff,

ne-luck with this one ???

K'Rgds
Anand

Commented:
hi jeff ...

still working on this one ?

Let me know

K'Rgds
Anand

Commented:
I think he forget to close the question as it is not a big problem to still working on it.

Commented:
yeah i know - just asking if ne-thing more is required !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial