ado/vb: null values

Discussion in 'OT Technology' started by DiabSoule!, Mar 26, 2004.

  1. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    I have a table containing a 255 character text field that may contain null values (ascii 0). I would like to display the nulls as a character (ie. ";"). Right now, the nulls come up as empty strings (""). How can I do this?
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Within the table, not sure if you can. Within a form, you can have it say anything you want with programming.
     
  3. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    ok. I suppose I could go through each character in the field and look for ascii 0 values and display them as ";". However, my problem (or maybe it's me lol) is that ado thinks the field is empty or treats the null value as well, nothing. But when I look at the field through a SQL browser I have I can see the ascii 0s.

    as an example.. here's what a field may look like.

    111;;111;11;;;;;;1 (the 1s are just any value and the ;s are nulls) ado seems to just skip the nulls and produce 111111111 for the example above. I need the nulls in there!!
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Okay. Stop there. What are you ultimately trying to do because I think there's a better way
     
  5. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    This field contains information about user rights to a program. The values in this field correspond to different levels of access to the various functions in this program based on the location of this value within this field (from 1 to 255).

    valid values are ascii 0 (no access) through to ascii 4 (supervisory).

    the app i'm writing will display the level of access a user has to each function within this program. i can read the ascii 1s through 4s just fine..
     
    Last edited: Mar 26, 2004
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    So assuming you don't want to go with x number of fields which would be my preference, why not simply store the number 0-4 in each place field? Why are you insisting on using the value of ASCII characters 0-4?

    If you're stuck with the format (ie it's not yours), then I would suggest that you retrieve the information via a different means other than ado. Dump the data into a text file and read it back perhaps, but using traditional ado calls will ignore the nulls.
     
    Last edited: Mar 26, 2004
  7. Keyzs

    Keyzs OT Supporter

    Joined:
    Nov 3, 2003
    Messages:
    814
    Likes Received:
    0
    Location:
    Charlotte, MI
    You have a field with non-null charactors and null charactors? Didn't think that was possible... Or are you saying their is multple columns in the record one per function? If that is so then do something like isNull()

    Select * from table where field=whatever

    In your code
    if isNull(Field(x)) then
    newvalue = ";"
    else
    newvalue = Field(x)
    end

    Or am I way off base here?
     
  8. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    no can do, that would make this security file useless to the program that uses it. It needs the asc 0 through to 4 values.. it wouldn't recognize 0 through 4 (the characters). my app is a custom add-on that has to work with the existing table in its current form.
     
  9. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    That's exactly what I'm saying. I didn't think such a beast existed either.. till now. It's a pervasive/btrieve database btw. ascii 0 corresponds to null correct?
     
  10. Keyzs

    Keyzs OT Supporter

    Joined:
    Nov 3, 2003
    Messages:
    814
    Likes Received:
    0
    Location:
    Charlotte, MI
    A long shot but can you do a
    Code:
    do until intLocation = 0
      intLocation = instr(startpoint,field,char(0),0) 'Look in field for Null
      arrLocations(counter) = intLocation             'add location of found null to array (or 0 if not found)
      startpoint = intLocation + 1                    'change start location to next character
      counter=counter+1                                'add to array counter
    loop
    
    if arrLocations(0) <> 0 then you have some???
    
    The idea not the code...
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    He still has the problem that when he retrieves the data via ado, it does NOT return the ASCII 0's in the string. It's like a built in filter.
     
  12. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    exactly. there must be a way of turning off this filter. I do appreciate all the feedback and suggestions thus far. Thanks a bunch!

    edit: I guess I could just give everyone read access (ascii 1) at minimum to everything, problem solved. lol. ;)
     
    Last edited: Mar 26, 2004
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Like I said before, you're going to have to dump the data to a datafile or something and then read it back in character by character. In other words, can't use ADO, at least not directly.
     
  14. Keyzs

    Keyzs OT Supporter

    Joined:
    Nov 3, 2003
    Messages:
    814
    Likes Received:
    0
    Location:
    Charlotte, MI
    Sorry I did not understand this the first time. After a little research it dawned on me that you had said that and the research agreed. So basically I second the opinion.
     
  15. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    any suggestion as to what would capture the asc(0)s? real-time would be nice, but i would settle for a 2nd database.
     
  16. are you sure it's not coming through already, and there's simply nothing for you to see? how are you outputting/presenting the data?
     
  17. DiabSoule!

    DiabSoule! ooh, plum been runnin', buck a doz. ooh, smoke ba

    Joined:
    Mar 15, 2000
    Messages:
    5,000
    Likes Received:
    0
    Location:
    Toronto, Canada
    it's not coming through. either in access or with an ado query in vb. it will just skip the asc(0) characters and only display the asc(1) through to asc(3) characters. the only way i can see the asc(0) is with a 'database viewer' that came with the software (which just looks like a sql browser). unfortunately, no provisions in the software to export.

    i am able to look for the asc(0)s and display them as a different character.. they are just being filtered out!
     
    Last edited: Mar 29, 2004

Share This Page