Cannot get a datagrid to refresh 1

[Giraffe69]

Im having real trouble getting a DataGrid to refresh after updating the ResordSource of a bound DataControl.

I have a DataControl, DataGrid, TextBox and CommandButton on a form.

This bit of code works OK when it is first run:

Private Sub cmdRun_Click()
    With adodcSQL
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBPath & "\" & strMDBName & ";User Id=admin;Password="
        .ConnectionTimeout = 10
        .CommandTimeout = 30
        .CommandType = adCmdText
        .LockType = adLockReadOnly
        .RecordSource = txtSQL.Text
    End With
    With dgridSQL
        Set .DataSource = adodcSQL
        .ReBind
        .Refresh
    End With
End Sub

But changing the RecordSource text and running it a second time has no effect of the DataGrid?

Any advice would be appreciated - this is really starting to drive me nuts!

 

[ClulessChris]

Have you tried clearing the datagrid prior to refreshing it?

Everybody body is somebodys Nutter.

 

[BogdanMBM]

It may be a good idea to put this:

With adodcSQL
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBPath & "\" & strMDBName & ";User Id=admin;Password="
        .ConnectionTimeout = 10
        .CommandTimeout = 30
        .CommandType = adCmdText
        .LockType = adLockReadOnly
        .RecordSource = txtSQL.Text
[highlight]
        .Refresh
[/highlight]
    End With

Hope I've been helpful,
Bogdan Muresan.

 

[Giraffe69]

Thanks Chris,

Ive tried ClearFields and ClearSelCols in different parts of the code but it doesnt seem to have any effect. Is there another way of clearing it that I'm missing?

ta

 

[BogdanMBM]

You don' have to clear the datagrid.
Refresh the adodc control. Basicly, the control brings locally a recordset based on your .RecordSource (a SQL statement, I suppose). If you just change the .RecordSource without issuing a .Refresh (the actual execution of your SQL statement) you end up using the old recordset as source for your datagrid.
So do a .Refresh for the adodc control.

Hope I've been helpful,
Bogdan Muresan.

 

[Giraffe69]

Wow, thanks Bogdan - that works a treat! I feel rather silly now...

 

[BogdanMBM]

You're wellcome!
Advice (if I may): trie not to use adodc controls in your applications. They are strange (don't "die" when you want, using memory, etc.) A more flexible solution is to use a refference to Microsoft ActivX Data Objects 2.7 and use ado connection and ado recordset (with cursor location set to client, in order to be able to browse locally). You can use souch a recordset as datasource for your datagrid.

Hope I've been helpful,
Bogdan Muresan.