Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    11

    Unanswered: Delete command rejects null values

    Delete command rejects null values

    using ASP on ODBC for Progress 9.1D database I cannot delete records which have a blank field

    Delete From Pub.Table T Where . . . and (T.Field = "");

    However, I can select the identical record

    Select * From Pub.Table T Where . . . and (T.Field = "");


    Funny thing, using Crystal 8.5 to analyse the data show that the length of a blank progress field is zero, but the length of a blank MS Access field does not return any value.

    I had to trick MS Access into creating a blank progress field (linked table) by first adding the field value "1" (length=3) and saving. Then deleting the 1 to produce "" with length=0. Conversely, if I start with "" and save, the length has no value.

    I believe there may be a relationship between creating a blank zero length field in access and trying to delete a zero length field in sql92.

    Thank you in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know progress etc, but instead of (T.Field = "") try:

    isnull(T.Field)
    or
    Len(nz(T.Field, "")) = 0

    maybe one or both will do the trick

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    11
    Sorry, thanks,

    I tried your suggestions with both the delete & select & no luck.

    Only the following select statement works, but replacing Select * with Delete does not.

    Select * FROM Pub.PcRules R WHERE R.Company='KCC' AND R.PartNum=' tttt' AND R.RevisionNum='A' AND R.ParPartNum='' AND R.AsmPartNum=' tttt' AND R.RuleSeq=20 AND R.OprSeq=5 AND R.MtlSeq=211;

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    last idea for the night: len(rtrim(T.Field))=0

    maybe your machine is returning a string stuffed with spaces instead of an empty string.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Tossing my 2 cents in: Why not : ... WHERE (T.Field & ""="") ...?

  6. #6
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    11
    Thank you but no cigars

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •