#1
  1. Just another guy
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2003
    Location
    Wisconsin
    Posts
    2,953
    Rep Power
    262

    subroutine causes unexpected delection of fields


    Hi all,
    Here's the situaiton: I am writing a program that uses an adodc connection to a database to fill the fields in a form. these fields can then be edited, with the changes of course being saved automatically when you advance to the next record. This works fine. The problem lies in one of the subroutines I wrote to trim the whitespace off the fields when they are brought in from the ms sql database (which is why the whitespace is there). Since you can't use "Trim" on a null field, I wrote the following code:
    Private Sub loadTrim()
    Adodc2.Recordset.Fields("comments") = varComments
    Adodc2.Recordset.Fields("location") = varLocation
    If IsNull(varComments) Then
    Text14.Text = Adodc2.Recordset.Fields("comments")
    Else: Text14.Text = Trim(Adodc2.Recordset.Fields("comments"))
    End If

    If IsNull(varLocation) Then
    Text13.Text = Adodc2.Recordset.Fields("location")
    Else: Text13.Text = Trim(Adodc2.Recordset.Fields("location"))
    End If
    End Sub
    The object is to trim whitespace from fields that have it, and just leave the blank fields for those that are empty. However, when this runs, it clears the entries from the location and comments fields as it goes. There are no syntax errors or anything that gives me a messge, and (sadly), I'm the best there is at this place, so I can't ask anyone for help. Can someone please tell me why this is happening? Thanks in advance

    Edit:
    I changed the code to
    Private Sub loadTrim()
    Dim varComments
    Dim varLocation

    varComments = Adodc2.Recordset.Fields("comments")
    varLocation = Adodc2.Recordset.Fields("location")
    If IsNull(varComments) Then
    Text14.Text = Adodc2.Recordset.Fields("comments")
    Else: Text14.Text = Trim(Adodc2.Recordset.Fields("comments"))
    End If

    If IsNull(varLocation) Then
    Text13.Text = Adodc2.Recordset.Fields("location")
    Else: Text13.Text = Trim(Adodc2.Recordset.Fields("location"))
    End If
    End Sub
    which might keep the location field from being deleted, but it now gives me 'Runtime Error 94, Invalid Use of 'Null' ', which is the message I was trying to avoid in the first place, and which I did not get with the old code. I do not get the error when I progress forward through the previously deleted records, but I do get it when I get to the next null field, or if I try to go back once I reach the field just before the null field with causes the error also. (does that make sense)?
    I'm now probably more confused, so I'm going to wait to hear a suggestion before continuing, if possible
    Last edited by karsh44; June 17th, 2003 at 03:12 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Melbourne, Australia
    Posts
    212
    Rep Power
    12
    Rather than using NULL, you could try using IsEmpty, or testing the String length.
    If the string length is 0 or the field is empty, then you have a null field.
    I've struck problems with the null value.

    HTH
    How can I soar like an eagle when
    I'm flying with turkey's?
  4. #3
  5. Just another guy
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2003
    Location
    Wisconsin
    Posts
    2,953
    Rep Power
    262
    I tried changing "isNull" to "isEmpty" in both versions of my original code, with the same results as before. Version 1 gives no errors but deletes the fields, version 2 deletes the fields that have already been deleted if additions are made to them (that is, strings that are empty, no null), but gives the "Invalid use of null" error message when it encountes the first null field.
    I also tried doing "LenB(varComments)=0" instead of isNull or isEmpty, in both versions of the code, with the same results as before. Any other ideas?

IMN logo majestic logo threadwatch logo seochat tools logo