Datagrids, Unbound Forms & ADO

Datagrids, Unbound Forms & ADO

Post by M. Sawalsk » Wed, 16 Oct 2002 01:40:19



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-

 
 
 

Datagrids, Unbound Forms & ADO

Post by Joe Har » Wed, 16 Oct 2002 03:12:21


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]"

- Show quoted text -

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-


 
 
 

Datagrids, Unbound Forms & ADO

Post by dk » Wed, 16 Oct 2002 04:23:42


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.

 
 
 

1. Unbound form with ado

Hi all,

I created an unbound form and use ado to create a recordset and get the data
to display in textbox.
When I add new record I have to click on "New" button then type data into
textbox then click on "Add" to add that record. I don't want to do like
this.
I want to it work like ado data control. I just click on Add then type data
into text box and that record is added to recordset automatically. How to do
like ado data control?
anyone can help me with its code?
With method I did above I invoked addnew method in Click event of New button
and assign text in text box to recordset in Add button. I feel its not
convient to do like this. Please show me the way to do like adodc
Thanks

Stand

2. Drop All Indexes

3. Urgent--Datagrid &ADO & ORACLE

4. Output from Stored Procedue

5. ADO, Datagrid and 2 forms

6. ODBC question

7. DATAWIDGET: Unbound DataGrid-Problem

8. Moving an MSAccess system to SQL Server

9. datagrid unbound columns ?

10. datagrid with unbound columns ?

11. How to populate an unbound DataGrid?

12. Unbound Datagrid Confusion!

13. ADO & DataGrid binding