Solved

How to update null field with SQL?

Posted on 2000-04-18
3
156 Views
Last Modified: 2010-04-04
This update function works fine for non-null values, but it won't work if the field requests is null.  How do I fix it?

function IncRequests(trkid : integer) : boolean;
var ok : boolean;
     q : TQuery;
begin
  ok := false;
  q := TQuery.Create(nil);
  try
    if trkid > 0 then
      begin
        q.SQL.Clear;
        q.SQL.Add('update tracks.db set requests = requests + 1 '+
                  'where id = '+inttostr(trkid));
        q.ExecSQL;
        ok := true;
      end;
  finally
    q.Free;
  end;
  IncRequests := ok;
end;
0
Comment
Question by:rwv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
simonet earned 100 total points
ID: 2729303
There are 2 ways to solve that:

1) Before calling the routine, make sure there are no NULL values for "Requests". You can do that, by changing all nulls to zero, so your routine works:

UPDATE "tracks.db" SET Requests=0 WHERE Requests IS NULL

2) Prevent your existing routine to work on records where "Requests" has a null value. You can do that by adding an extra test to the existing WHERE clause:

  q.SQL.Add('update tracks.db set requests = requests + 1 '+
                  'where id = )'+inttostr(trkid)+') AND (Requests IS NOT NULL)');


You choose whichever way works best for your specific needs.

Yours,

Alex
0
 

Author Comment

by:rwv
ID: 2729357
Alex,
Is it possible to use an if then statement in sql?
ie -
if requests is null then requests = 1
else requests = requests + 1

Bobby
0
 
LVL 15

Expert Comment

by:simonet
ID: 2729388
Unfortunatly not, Bobby. I also wish it was possible, but so far, it's not.

Alex
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question