ActiveX data provider - Adding record doesn't update datagrid with autonumber field.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ActiveX data provider - Adding record doesn't update datagrid with autonumber field.

  1. #1
    aterren Guest

    ActiveX data provider - Adding record doesn't update datagrid with autonumber field.



    I've created an ActiveX ADO Data Provider control to manage interaction with
    an Access database. In the control I create connection and recordset (rs)
    objects and open both OK. The control is placed on a form and bound to a
    DataGrid's datasource. The population of the grid works fine.

    The problem comes when I add a record that contains an autonumber field (used
    as a primary key). The command rs.addnew does not yield a recordset containing
    a new record with a value in the autonumbered field. Inspection of the rs
    object shows the value to be NULL. The record is successfully created in
    access and I can force the recordset to update a datagrid contained IN the
    ActiveX control (as opposed to on the same form) with the commands rs.update
    followed by rs.requery. This has 3 significant limitations:
    (1) the user can't cancel the add since update commits the add to the db.

    (2) You must insert a "Dummy" value in one of the fields because you can't
    execute the update if all the fields in one of the records are NULL
    (3) This does not update the grid on the form with the control. To force
    this grid to update I have to set grid.datasource = nothing then set grid.datasource
    = MyADOControl with button controls. The clearfields then rebind doesn't
    work (VB documentation indicates this should have the same effect).

    I think I can make this work, but it's going to take a lot of logic to manage
    all the states once I have 6 or 8 grids in the program. It sure seems like
    I'm missing something. Any help would be greatly appreciated.

    aterren@hotmail.com
    P.S. I can e-mail a simple sample that demonstrates the problem. Just drop
    me a note.


  2. #2
    MIke Guest

    Re: ActiveX data provider - Adding record doesn't update datagrid with autonumber field.


    "aterren" <aterren@hotmail.com> wrote:
    >
    >
    >I've created an ActiveX ADO Data Provider control to manage interaction

    with
    >an Access database. In the control I create connection and recordset (rs)
    >objects and open both OK. The control is placed on a form and bound to a
    >DataGrid's datasource. The population of the grid works fine.
    >
    >The problem comes when I add a record that contains an autonumber field

    (used
    >as a primary key). The command rs.addnew does not yield a recordset containing
    >a new record with a value in the autonumbered field. Inspection of the rs
    >object shows the value to be NULL. The record is successfully created in
    >access and I can force the recordset to update a datagrid contained IN the
    >ActiveX control (as opposed to on the same form) with the commands rs.update
    >followed by rs.requery. This has 3 significant limitations:
    >(1) the user can't cancel the add since update commits the add to the db.
    >
    >(2) You must insert a "Dummy" value in one of the fields because you can't
    >execute the update if all the fields in one of the records are NULL
    >(3) This does not update the grid on the form with the control. To force
    >this grid to update I have to set grid.datasource = nothing then set grid.datasource
    >= MyADOControl with button controls. The clearfields then rebind doesn't
    >work (VB documentation indicates this should have the same effect).
    >
    >I think I can make this work, but it's going to take a lot of logic to manage
    >all the states once I have 6 or 8 grids in the program. It sure seems like
    >I'm missing something. Any help would be greatly appreciated.
    >
    >aterren@hotmail.com
    >P.S. I can e-mail a simple sample that demonstrates the problem. Just drop
    >me a note.
    >

    What Type of cursor are you using.



  3. #3
    Gagan Behl Guest

    Re: ActiveX data provider - Adding record doesn't update datagrid with autonumber field.


    Hi Mike,

    Man I think you should change you code slightly.
    (a) First, instead of using the addnew method(which is
    inefficient & error prone) use the INSERT statements
    directly using the command/connection objects.
    (b) Next, you must have a class/property inside youe control
    that handles all your DML(Insert/Updat/Delete) operations,
    when a plain string consisting of DML commands are passsed
    to it.
    (c) Last, before any DML operation you should set
    grid.datasource = nothing & reset it after the
    DML operation has been done.

    Hope it helps you out. Repost another message for further clarifiactions.

    Gagan

    "MIke" <wightman_michael@hotmail.com> wrote:
    >
    >"aterren" <aterren@hotmail.com> wrote:
    >>
    >>
    >>I've created an ActiveX ADO Data Provider control to manage interaction

    >with
    >>an Access database. In the control I create connection and recordset (rs)
    >>objects and open both OK. The control is placed on a form and bound to

    a
    >>DataGrid's datasource. The population of the grid works fine.
    >>
    >>The problem comes when I add a record that contains an autonumber field

    >(used
    >>as a primary key). The command rs.addnew does not yield a recordset containing
    >>a new record with a value in the autonumbered field. Inspection of the

    rs
    >>object shows the value to be NULL. The record is successfully created in
    >>access and I can force the recordset to update a datagrid contained IN

    the
    >>ActiveX control (as opposed to on the same form) with the commands rs.update
    >>followed by rs.requery. This has 3 significant limitations:
    >>(1) the user can't cancel the add since update commits the add to the db.
    >>
    >>(2) You must insert a "Dummy" value in one of the fields because you can't
    >>execute the update if all the fields in one of the records are NULL
    >>(3) This does not update the grid on the form with the control. To force
    >>this grid to update I have to set grid.datasource = nothing then set grid.datasource
    >>= MyADOControl with button controls. The clearfields then rebind doesn't
    >>work (VB documentation indicates this should have the same effect).
    >>
    >>I think I can make this work, but it's going to take a lot of logic to

    manage
    >>all the states once I have 6 or 8 grids in the program. It sure seems like
    >>I'm missing something. Any help would be greatly appreciated.
    >>
    >>aterren@hotmail.com
    >>P.S. I can e-mail a simple sample that demonstrates the problem. Just drop
    >>me a note.
    >>

    >What Type of cursor are you using.
    >
    >



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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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