DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ADO and DataGrid

  1. #1
    mac Guest

    ADO and DataGrid


    I want to display certain records (consisting of 4 fields) from a table into
    a DataGrid. I want to be able to Add, Amend and Delete from within this DataGrid.
    I have achieved this by adding a ADO control, is this necessary ?

    The DataGrid form represents 'child' records associated with a master record
    from another table that is displayed in a combo i.e. if the value in the
    combo is changed, the DataGrid should only display records where a specific
    field in the 'child' table matches the value displayed in the combo.

    The database has been opened using ADO code only and not the ADO Data control.
    Can this be done, or do I need to add the ADO control onto the form containing
    the DataGrid ?

    Any responses or references to elsewhere will be greatly appreciated. Appologies
    for the long winded explanation !

    Mac.

  2. #2
    Kevin MacCallum Guest

    Re: ADO and DataGrid


    You don't need a data control.
    Populate the OLEDB Datagrid by generating an ADODB Recordset and setting
    the DataSource property of the grid to the recordset.
    To pass updates back to the database recreate a recordset based on the grid's
    datasource then use the recordset's UpdateBatch method.

    Two sub-routines are below - note I have excluded error checking etc to keep
    the basic code as short as possible.

    1) FillDataGrid - populates the grid
    2) UpdateData - writes changes back to database

    **************************
    Private Sub FillDataGrid()
    'note locktype is set to adLockBatchOptimistic
    'required when reconnecting to database & populating updates

    Dim rs As ADODB.Recordset
    Dim sConnstr As String, sSql As String
    dim lLoop As Long, lFldCount As Long

    sConnstr = "your specfic connection string to database"
    sSql = "Your sql to generate recordset"

    Set rs = New ADODB.Recordset

    With rs
    .CursorLocation = adUseClient
    .Open sSql, sConnstr, adOpenStatic, adLockBatchOptimistic , adCmdText
    Set .ActiveConnection = Nothing
    If .LockType = adLockBatchOptimistic Then .MarshalOptions = adMarshalModifiedOnly
    End With

    If Not rs.BOF And Not rs.EOF Then
    With grd'<<--- your grid name here
    .ClearFields
    With .HeadFont
    .Bold = True
    .Size = 10
    End With
    .MarqueeStyle = dbgFloatingEditor
    .DefColWidth = 1200

    Set .DataSource = rs '<<-------set rs to grid's datasource

    lColCount = rs.Fields.Count - 1
    For lLoop = 0 To lColCount
    'next line calls another sub-rroutine that fills in the header for
    each of the columns
    GridColumnSetup .Columns(lLoop), True, rs.Fields(lLoop).Name
    Next

    End With
    End If

    Set rs = Nothing 'do not close it or grid's datasource will close also
    End Sub


    *****************************************
    Private Sub UpdateData()
    'writes changes in datagrid back to the database

    Dim rs As ADODB.Recordset
    Dim sConnstr As String
    sConnstr = "your specfic connection string to database"

    Set rs = grd.DataSource
    With rs
    .ActiveConnection = sConnstr'reconnect to the database
    .UpdateBatch'writes changes back to the database
    End With

    CleanUP:
    On Error Resume Next
    If Not rs Is Nothing Then
    Set rs.ActiveConnection = Nothing
    Set rs = Nothing
    End If

    End Sub

    Kevin
    --------------------
    "mac" <pashma@talk21.com> wrote:
    >
    >I want to display certain records (consisting of 4 fields) from a table

    into
    >a DataGrid. I want to be able to Add, Amend and Delete from within this

    DataGrid.
    >I have achieved this by adding a ADO control, is this necessary ?
    >
    >The DataGrid form represents 'child' records associated with a master record
    >from another table that is displayed in a combo i.e. if the value in the
    >combo is changed, the DataGrid should only display records where a specific
    >field in the 'child' table matches the value displayed in the combo.
    >
    >The database has been opened using ADO code only and not the ADO Data control.
    >Can this be done, or do I need to add the ADO control onto the form containing
    >the DataGrid ?
    >
    >Any responses or references to elsewhere will be greatly appreciated. Appologies
    >for the long winded explanation !
    >
    >Mac.



  3. #3
    mac Guest

    Re: ADO and DataGrid


    Kevin,

    Thanks for your response.

    Mac


    "Kevin MacCallum" <kdmaccal@gapac_stuff_.com> wrote:
    >
    >You don't need a data control.
    >Populate the OLEDB Datagrid by generating an ADODB Recordset and setting
    >the DataSource property of the grid to the recordset.
    >To pass updates back to the database recreate a recordset based on the grid's
    >datasource then use the recordset's UpdateBatch method.
    >
    >Two sub-routines are below - note I have excluded error checking etc to

    keep
    >the basic code as short as possible.
    >
    >1) FillDataGrid - populates the grid
    >2) UpdateData - writes changes back to database
    >
    >**************************
    >Private Sub FillDataGrid()
    >'note locktype is set to adLockBatchOptimistic
    >'required when reconnecting to database & populating updates
    >
    >Dim rs As ADODB.Recordset
    >Dim sConnstr As String, sSql As String
    >dim lLoop As Long, lFldCount As Long
    >
    >sConnstr = "your specfic connection string to database"
    >sSql = "Your sql to generate recordset"
    >
    >Set rs = New ADODB.Recordset
    >
    >With rs
    > .CursorLocation = adUseClient
    > .Open sSql, sConnstr, adOpenStatic, adLockBatchOptimistic , adCmdText
    > Set .ActiveConnection = Nothing
    > If .LockType = adLockBatchOptimistic Then .MarshalOptions = adMarshalModifiedOnly
    >End With
    >
    >If Not rs.BOF And Not rs.EOF Then
    > With grd'<<--- your grid name here
    > .ClearFields
    > With .HeadFont
    > .Bold = True
    > .Size = 10
    > End With
    > .MarqueeStyle = dbgFloatingEditor
    > .DefColWidth = 1200
    >
    > Set .DataSource = rs '<<-------set rs to grid's datasource
    >
    > lColCount = rs.Fields.Count - 1
    > For lLoop = 0 To lColCount
    > 'next line calls another sub-rroutine that fills in the header for
    >each of the columns
    > GridColumnSetup .Columns(lLoop), True, rs.Fields(lLoop).Name
    > Next
    >
    > End With
    >End If
    >
    >Set rs = Nothing 'do not close it or grid's datasource will close also
    >End Sub
    >
    >
    >*****************************************
    >Private Sub UpdateData()
    >'writes changes in datagrid back to the database
    >
    >Dim rs As ADODB.Recordset
    >Dim sConnstr As String
    >sConnstr = "your specfic connection string to database"
    >
    >Set rs = grd.DataSource
    >With rs
    > .ActiveConnection = sConnstr'reconnect to the database
    > .UpdateBatch'writes changes back to the database
    >End With
    >
    >CleanUP:
    >On Error Resume Next
    >If Not rs Is Nothing Then
    > Set rs.ActiveConnection = Nothing
    > Set rs = Nothing
    >End If
    >
    >End Sub
    >
    >Kevin
    >--------------------
    >"mac" <pashma@talk21.com> wrote:
    >>
    >>I want to display certain records (consisting of 4 fields) from a table

    >into
    >>a DataGrid. I want to be able to Add, Amend and Delete from within this

    >DataGrid.
    >>I have achieved this by adding a ADO control, is this necessary ?
    >>
    >>The DataGrid form represents 'child' records associated with a master record
    >>from another table that is displayed in a combo i.e. if the value in the
    >>combo is changed, the DataGrid should only display records where a specific
    >>field in the 'child' table matches the value displayed in the combo.
    >>
    >>The database has been opened using ADO code only and not the ADO Data control.
    >>Can this be done, or do I need to add the ADO control onto the form containing
    >>the DataGrid ?
    >>
    >>Any responses or references to elsewhere will be greatly appreciated. Appologies
    >>for the long winded explanation !
    >>
    >>Mac.

    >



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