Very Computer

Hi!  In the past, I have used ADO controls and bound forms for database
access, but now have a need to use an unbound form, and fill a datagrid
control with data.

I declare the following for the form "frmCharter":

Dim mcnCharterApp As Connection
Dim mrsCharter As Recordset

Next, at the Form Load event, I use the following code to establish the
link to the database:

    Set mcnCharterApp = New Connection
    Set mrsCharter = New Recordset
    mcnCharterApp.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        " Data Source =" & databasePath & ";"

where databasePath is a variable containing the fully qualified path to
my database. (an Access 2000 database).

Now, I want to get all of my records for the date specified, so I use
the following to load up my recordset:

    strQuery = "SELECT
[CHARTER].[CHARTER_EVENT_NAME],[CHARTER].[CHARTER_DESTINATION],[CHARTER].[CHARTER_ORIGINATION],[CHARTER].[CHARTER_SPECIAL_INSTRUCTIONS_1],[CHARTER].[CHARTER_PICKUP_TIME],[CHARTER].[CHARTER_RETURN_TIME],[CHARTER].[CHARTER_DAY_OF_CHARTER],[CHARTER].[CHARTER_QUOTED_PRICE],[CHARTER].[CHARTER_NUMBER_OF_BUSES],[CHARTER].[CHARTER_HOW_CUSTOMER_PAYS],[CHARTER].[CHARTER_DATE_CONTACT_CALLED],[CHARTER].[CHARTER_WAS_SLIP_MADE],[CHARTER].[CHARTERID],[CHARTER].[CONTACTID],[CONTACT].[CONTACTID],[CONTACT].[CONTACT_LAST_NAME],[CONTACT].[CONTACT_FIRST_NAME],[CONTACT].[CONTACT_PHONE_NUMBER]"
& _
                " FROM [CONTACT] INNER JOIN [CHARTER] ON
[CONTACT].[CONTACTID] = [CHARTER].[CONTACTID]" & _
                " WHERE ([CHARTER].[CHARTER_DATE_OF_CHARTER] = #" & _
               Calendar1.Value & "#" & _
                " AND ([CHARTER].[CHARTER_AM_OR_PM_TRIP] = '" & sAMPM &
"'));"
    mrsCharter.Open strQuery, mcnCharterApp, adOpenKeyset, _
                    adLockOptimistic, adCmdText

Finally, I set the record source for my grid control (grdCharter) as
follows:

    Set grdCharter.DataSource = mrsCharter

My question is as follows:  What do I need to do to populate the
information in the recordset into the datagrid.  In the past, when I
have done this with a bound form (datagrid to the ADO control on the
form), this all happened automatically.  It doesn't seem to be happening
this way now, and I suspect that I need to load the form up through
code.

Any thoughts?

Thanks!

-Mike-

Assuming your SQL query is valid (and that's one heck of a query :), you can
load the grid by looping through the recordset -- something like:

'** Warning: AIR CODE
Do While Not mrsCharter.EOF
    <YourGridCell>.Text = mrsCharter.Fields("<FieldName>").Value
    '** Continue for each data item in the recordset
    ...

    mrsCharter.MoveNext
Loop

If you go this route, you can get rid of the "Set grdCharter.DataSource =
mrsCharter" line.

Personally, I prefer the flexibility of handling the recordsets
programatically vs. using the data bound controls/ADODC.

--
Good Luck,

Joe
----------------------------------------------------------------------------
See if your question has already been answered:

http://groups.google.com/advanced_group_search?q=group:*vb*&num=100
----------------------------------------------------------------------------

Quote:> Hi!  In the past, I have used ADO controls and bound forms for database
> access, but now have a need to use an unbound form, and fill a datagrid
> control with data.

> I declare the following for the form "frmCharter":

> Dim mcnCharterApp As Connection
> Dim mrsCharter As Recordset

> Next, at the Form Load event, I use the following code to establish the
> link to the database:

>     Set mcnCharterApp = New Connection
>     Set mrsCharter = New Recordset
>     mcnCharterApp.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>         " Data Source =" & databasePath & ";"

> where databasePath is a variable containing the fully qualified path to
> my database. (an Access 2000 database).

> Now, I want to get all of my records for the date specified, so I use
> the following to load up my recordset:

>     strQuery = "SELECT

[CHARTER].[CHARTER_EVENT_NAME],[CHARTER].[CHARTER_DESTINATION],[CHARTER].[CH
ARTER_ORIGINATION],[CHARTER].[CHARTER_SPECIAL_INSTRUCTIONS_1],[CHARTER].[CHA
RTER_PICKUP_TIME],[CHARTER].[CHARTER_RETURN_TIME],[CHARTER].[CHARTER_DAY_OF_
CHARTER],[CHARTER].[CHARTER_QUOTED_PRICE],[CHARTER].[CHARTER_NUMBER_OF_BUSES
],[CHARTER].[CHARTER_HOW_CUSTOMER_PAYS],[CHARTER].[CHARTER_DATE_CONTACT_CALL
ED],[CHARTER].[CHARTER_WAS_SLIP_MADE],[CHARTER].[CHARTERID],[CHARTER].[CONTA
CTID],[CONTACT].[CONTACTID],[CONTACT].[CONTACT_LAST_NAME],[CONTACT].[CONTACT
_FIRST_NAME],[CONTACT].[CONTACT_PHONE_NUMBER]"

Quote:> & _
>                 " FROM [CONTACT] INNER JOIN [CHARTER] ON
> [CONTACT].[CONTACTID] = [CHARTER].[CONTACTID]" & _
>                 " WHERE ([CHARTER].[CHARTER_DATE_OF_CHARTER] = #" & _
>                Calendar1.Value & "#" & _
>                 " AND ([CHARTER].[CHARTER_AM_OR_PM_TRIP] = '" & sAMPM &
> "'));"
>     mrsCharter.Open strQuery, mcnCharterApp, adOpenKeyset, _
>                     adLockOptimistic, adCmdText

> Finally, I set the record source for my grid control (grdCharter) as
> follows:

>     Set grdCharter.DataSource = mrsCharter

> My question is as follows:  What do I need to do to populate the
> information in the recordset into the datagrid.  In the past, when I
> have done this with a bound form (datagrid to the ADO control on the
> form), this all happened automatically.  It doesn't seem to be happening
> this way now, and I suspect that I need to load the form up through
> code.

> Any thoughts?

> Thanks!

> -Mike-

The below code will allow you to bind to a disconnected ADO RS and update.  
It looks like you needed to specify clientside cursors in your code:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

Private Sub DataGrid1_AfterColEdit(ByVal ColIndex As Integer)
    rs.ActiveConnection = cn
    rs.UpdateBatch
End Sub

Private Sub Form_Load()

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False"
rs.CursorLocation = adUseClient
rs.Open "select * from Customers", cn, adOpenStatic, adLockBatchOptimistic

Set DataGrid1.DataSource = rs
Set rs.ActiveConnection = Nothing

End Sub

Let me know if this works for you.

Thanks

John Sirmon, MCSD
Microsoft Visual Basic Web Data Support Team

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.