Solved

how to update the table  with the datagridvalues using ADODB.recordset

Posted on 2004-10-22
259 Views
Last Modified: 2010-05-02
I would like to know how do i update the values from the data grid to the database table. after making changes in the datagrid. i would loike to load the datagrid with database table values and after making changes i would like to update the values in the database.
0
Question by:raj_ms
    22 Comments
     
    LVL 3

    Expert Comment

    by:Noel_Castillo
    Its just a matter of setting some properties in datagrid. like allowupdate. once you make changes to the grid and change the row. the changes will be reflected in the database. AllowAddnew will allow the user to create a new record by filling up the empty row in the bottom. and do on. :)
    0
     

    Author Comment

    by:raj_ms
    NO i am not using ADODC
    0
     

    Author Comment

    by:raj_ms
    What you say is, when we use ADO DC control
    0
     

    Author Comment

    by:raj_ms
    I have enabled the checkbox in the datagrid, even now its not updating becaz i am not using ADO Data control
    0
     

    Author Comment

    by:raj_ms
    i want to use without ADO DC control
    0
     
    LVL 3

    Expert Comment

    by:Noel_Castillo
    did you checked the allowupdate property?
    0
     
    LVL 3

    Expert Comment

    by:Noel_Castillo
    that should work. even in adodb.

    datagrid.datasource = rs  'something like this right? after creating recordset

    0
     

    Author Comment

    by:raj_ms
    yes i have checked but what you say is to fill datagrid wiyth rs but i want to update the changes in the datagrid to database table
    0
     

    Author Comment

    by:raj_ms
    i want the vice versa
    0
     

    Author Comment

    by:raj_ms
    Dear Noel_Castillo,
    I have done what ever you have told me. but i wish to update the table with the datagrid
    0
     
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    Add a datagrid control and a button on a form.When the form is loaded try changing the value of a cell and then click update.Then check your original database, and you'll see it is updated.


    Dim mycnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Private Sub cmdUpdate_Click()
    rs.UpdateBatch
    End Sub

    Private Sub Form_Load()
    Set mycnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    mycnn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;Server=FIT4;Database=Host;UID=sa;PWD=;"
    mycnn.Open
    rs.Open "Add your SQL statement here", mycnn, adOpenDynamic, adLockBatchOptimistic

    Set DataGrid1.DataSource = rs

    End Sub
    0
     

    Author Comment

    by:raj_ms
    No it didnt update
    0
     

    Author Comment

    by:raj_ms
    when i see the table its not updated
    0
     
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    it did mine.
    Did any error come up?
    did you set the connection string correct?
    0
     
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    it is strange because the code i gave you works with me.

    Have you checked if your database allows updates?

    Do you have administrative rights on the pc you're using?


    After you press the update button on your form,have hou refreshed your database so to be sure you have the updated values?
    0
     

    Author Comment

    by:raj_ms

    Public Sub loadRates()
       Set lr = New ADODB.Recordset
       lr.CursorLocation = adUseClient
       lr.Open "select * from raj.rates", cn, 3, 2
       Set Form1.DataGrid2.DataSource = lr
       Form1.DataGrid2.Columns(0).Visible = False
    End Sub
    Public Sub clearRates()
        Set Form1.DataGrid2.DataSource = Nothing
    End Sub
    Sub changeRates()
    lr.updateBatch
    End Sub
    0
     
    LVL 23

    Accepted Solution

    by:
    try this:


    Public Sub loadRates()
       Set lr = New ADODB.Recordset
       lr.CursorLocation = adUseClient
       lr.Open "select * from raj.rates", cn, adOpenDynamic, adLockBatchOptimistic     '<-----The change is here
       Set Form1.DataGrid2.DataSource = lr
       Form1.DataGrid2.Columns(0).Visible = False
    End Sub
    Public Sub clearRates()
        Set Form1.DataGrid2.DataSource = Nothing
    End Sub
    Sub changeRates()
    lr.UpdateBatch
    End Sub
    0
     

    Author Comment

    by:raj_ms
    it produces an error
    Row cannot be located for updating. Somevalues may have been changed since it was last read.
    0
     
    LVL 1

    Expert Comment

    by:bangerarun

    Dear
    its so simple

    Only to set the datagrid allowupdate property to true
    and what ever changes you are entering in your gird are automatically update in your database.

    You havenothing to do it except only to make chanege the
    allowautoupdate propertly to true

    Banger
    0
     

    Author Comment

    by:raj_ms
    i have set the allowupdate property to true.......
    even now i couldnt update.........
    0
     
    LVL 3

    Expert Comment

    by:Noel_Castillo
    im sorry i have to go last friday before answering ur last query. Im wondering why that doesnt work for you. Are you sure that the table is updatable? Or you have a full access on it? :)
    0
     

    Author Comment

    by:raj_ms
    s indeed i tried it manually
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now