Method refresh of IAdodc failed

[DebbieH]

In fixing a VB application, the previous programmer in some instances used a SSOLEDBGRID to populate table data and the variable used to populate the grid was type ADODC.

The ADODC variable has the following properties:
Connection String: Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=[Database Name];Data Source=[Server];Initial File Name="";Server SPN=""
[I also tried "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Reliant"]

RecordSource is: "SELECT * FROM Configure"; the query has a command type of "adCommandUnknown"

The error displayed on the screen occurs at ResultDC.Refresh when the View Results command button is clicked:
-- I abort the program at this time.

When I inspect the properties if the GRID - the following errors are displayed:
-- after I click on "OK" the following appears:
"Warning - the datasource property has not been set and/or the data control has not been configured correctly"


The code behind the command button reads:
Dim res As Boolean

On Error GoTo cmdPerform_Click_Error

res = C1Query.BuildSQL
If res Then
ResultDC.CommandType = adCmdText
ResultDC.RecordSource = C1Query.SQL
ResultDC.Refresh
End If


Any suggestions to fix this problem would be appreciated. Let me know if I need to provide additional information.

[Tech99]

You need to execute, instead of refresh...

    Dim res As Boolean
    
  On Error GoTo cmdPerform_Click_Error

    res = C1Query.BuildSQL
    If res Then
        ResultDC.CommandType = adCmdText
        ResultDC.RecordSource = C1Query.SQL
        ResultDC.Refresh
    End If

'sSQL variable in this holds your query clause
'Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = DB
    .CommandType = CommandTypeEnum.adCmdText
    .CommandText = sSQL
End With
     
Set RS = cmd.Execute(RecordsAffected)
.
.
Set cmd.ActiveConnection = Nothing
Set RS = Nothing