DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Data entry fields not holding values when bound to recordset based on empty table

  1. #1
    Dan Tharp Guest

    Data entry fields not holding values when bound to recordset based on empty table

    We're having a problem using ADODB recorsets that are bound to fields on a

    We have a form with fields bound to a recorset which is defined in the data
    environment. The data environment has a connection to a SQL Server 2000
    database. The recordset, as defined in the data environment, is initially
    opened with a query like 'select top 1 * from part', so we only have 1 record
    in the recordset and the screen doesn't take too long to load.

    When the user adds a record to the recorset, we close the recordset and re-open
    it using a query that will return no records (....where 0=1) and then issue
    a recordset.ADDNEW to add a record.
    Fields on the form are then re-bound to the new recordset and the user can
    data enter values into the bound fields.
    When the user clicks save, then we issue a recorset.UPDATE to write out to
    the database.

    This works fine....except, when the recordset is pointing to a table that
    is empty.
    Even when we "zero" out the recorset with a "where 0=1" clause to have no
    records in the new recorset, as long as the table underneath has at least
    one record in it, everything works fine.
    If, the table is empty, however, the data entry fields on the form will not
    hold values. As the user tabs from field to field, the values are not retained.

    We dumped all of the properties of the recorset as well as all of the properties
    of the recorset's "Active Command" both in the case where the table had 1
    record in it and when the table had no records in it.
    There were no differences in ANY of the properties--the recorsets are identical.
    The only thing different is that when the underlying table is empty, fields
    don't hold values.

    Anyone have any ideas as to why this is happening?

  2. #2
    Dan Tharp Guest

    Re: Data entry fields not holding values when bound to recordset based on empty table

    FYI, Update: I'm not exactly sure why it works, but we've figured out a solution
    (work-around) to this problem, that I thought you might be interested in:

    Whenever we set the (field).DataField, (field).DataMember, and (field).DataSource
    properties on a bound field on a form--IN DESIGN MODE--the dataenvironment
    automatically opens the recordset, based on the SQL string constructed in
    the data environment when the application runs.
    We don't need to initially open the recorset on the Form_Load, since the
    data environment does this for us.
    In the case where the table behind the recordset is empty, bound fields don't
    behave correctly after a (recordset).AddNew has been issued.
    Once the field loses focus, the value is not held.
    We tried this with fields bound, unbound, and all sorts of situations.
    We even (programmatically) set (field).DataField to "", (field).DataMember
    to "", and (field).DataSource to Nothing, yet the field STILL would not hold
    a value if the underlying table behind the recordset was empty.

    Again, I'm not sure why this works, but here's what we did to address this:
    In DESIGN mode, we set (field).DataSource to the data environent variable,
    (field).DataMember to the recordset variable, and (field).DataField to the
    field...but then, went back and set (field).DataSource to Nothing.
    We did this for every bound field on the form.
    Now, since there are no fields (in DESIGN mode) with (field).DataSource set
    to the data environment, the recordset does not automatically open.
    So, we have to add a line of code in the Form_Load to initially open the
    Once we did this, we could issue a (recordset).AddNew and all fields hold
    their values regardless of whether or not the underlying table has records
    or not.

    Again...I'm not sure why this works, but setting (field).DataSource to Nothing
    (blank) in DESIGN mode and then forcing the recordset open on Form_Load,
    allows us to have bound fields that behave normally whether or not the underlying
    table has records in it or not.

    Just thought you might be interested...

    Dan Tharp,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center