I don't know what you Set Execute does, what is Execute?
you need to the the prepared parameter of your ADO command object:
ADOCmd.Prepared = True
you need to set the Type of command....
.CommandType = adCmdText ' type adCmdText is used for SQL Statement execution.
this should give you and idea of what I'm doing.....
This stuff works with an SLX Connection and ? parameter.
It does not work with a SQL Server native connection.
' create basic SQL statement used to retrieve the data from the database.
' when using a Native SQL Server connection WITH(NOLOCK) hints should be added.
SearchSQL = "SELECT COALESCE(A1." & UCase(SearchTableNameIDfield) & ", '') AS " & UCase(SearchTableNameIDfield) & _
", COALESCE(A1.DESCRIPTION,'') AS DESCRIPTION" & _
", COALESCE(A2.ACCOUNT, '') AS A2_ACCOUNT" & _
", COALESCE(A1.SALESPOTENTIAL, 0.00) AS SALESPOTENTIAL " & _
" FROM " & UCase(SearchTableName) & " A1 " & _
" INNER JOIN ACCOUNT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) "
grdLookup.KeyField = SearchTableNameIDfield
' build the grid columns.
Call BuildGrid_grdLookup (grdLookup)
' instantiate/create a database connection object, ADO Recordset object, ADO Command object, and an ADO Command Parameter.
Call CreateScriptObjects
End Sub
Sub CreateScriptObjects
' instantiate native connection object, opened.
' SLXDatabaseSupport_GetNativeConnection(ByRef TheSQL_Server_ConnectionObject)
' Call SLXDatabaseSupport_GetNativeConnection( grdLookupCN )
Set grdLookupCN = application.GetNewConnection
' Create ADO RS object
' SLXDatabaseSupport_GetADORecordSet (ByRef TheADO_RecordsetObject) ' 5/9/2010 Native Connection not working with named or ? parameters.
Call SLXDatabaseSupport_GetADORecordSet (grdLookupRS)
With grdLookupRS
' Add Fields to the recordset as needed
.Fields.Append "OPPORTUNITYID", adChar, 12
.Fields.Append "DESCRIPTION", adVarChar, 128
.Fields.Append "A2_ACCOUNT", adVarChar, 128
.Fields.Append "SALESPOTENTIAL", adCurrency
End With
' create ADO DB Command object
set objADOcommand = createObject("ADODB.Command")
' set up command object's basic properties.
' active connection, stored proc type
With objADOcommand
.ActiveConnection = grdLookupCN
.Prepared = True ' command will be reused.
.CommandType = adCmdText ' type adCmdText is used for SQL Statement execution.
End With
' create 1 input Parameter object
set objInputParam1 = createObject("ADODB.Parameter")
' set the parameter Type, name ?, size, input parameter, code variable to derive the value from
With objInputParam1
.Type = adVarWChar
.Name = "?" ' "@P1" named parameters do not work so far. using a ? matches the 'appended parameter' position ? in the CommandText property. ParameterName
.Size = 12
.Direction = adParamInput
End With
'add parameter to the command (Append parameter 1)
objADOcommand.Parameters.Append objInputParam1
End Sub
and then call this to fire off the search:
Sub RefreshGrid_grdLookup (ByVal TheSearchValue, ByRef TheDataGridControl, ByRef TheSQL_Server_ConnectionObject, ByRef TheADO_RecordsetObject, ByRef TheADO_CommandObject, ByRef TheParam1)
Dim TheSearchSQL
' set up command object's properties.
' active connection, stored proc type, populate the actual Command Text to execute, refresh the parameters collection.
With TheADO_CommandObject
.ActiveConnection = TheSQL_Server_ConnectionObject ' grdLookupCN
.Prepared = True
.CommandType = adCmdText ' type text for SQL execution.
' set up the Command Text (what get's executed)
' named parameters do not work in SQL Server!
TheSearchSQL = SearchSQL & " WHERE " & SearchTableNameIDfield & " LIKE ? "
Application.Debug.WriteLine "58 RefreshGrid_grdLookup SQL:= " & TheSearchSQL
.CommandText = TheSearchSQL
Application.Debug.WriteLine "58 RefreshGrid_grdLookup SQL CommandText:= " & TheADO_CommandObject.CommandText
.Parameters.Refresh ' when reusing command objects, refresh the parameter Collection for the Command AFTER setting CommandText!
End With
' set the parameter
TheADO_CommandObject.Parameters(0).Value = SearchValue ' from the global variable
' Set the RS Object to the Command Object.Execute, the Execute returns an ADO Recordset to the TheADO_RecordsetObject
If TheADO_RecordsetObject.State = 1 Then TheADO_RecordsetObject.Close
Set TheADO_RecordsetObject = objADOcommand.Execute ()
' load / refresh the data grid.
With TheADO_RecordsetObject
Application.Debug.WriteLine "58 RefreshGrid_grdLookup: " & .RecordCount & " Records were found. "
If Not ( .BOF or .EOF) Then
' Load the Grid RS to refresh the grid.
' traditional method is: Set TheDataGridControl.RecordSet = TheADO_RecordsetObject
' Sub GridCommon_RefreshGridByRS (byRef theGrid, byRef theRS)
Call GridCommon_RefreshGridByRS ( TheDataGridControl, TheADO_RecordsetObject)
Else ' no search hits, set grid to 'nothing'
TheDataGridControl.SQL.TEXT = "SELECT A1.OPPORTUNITYID FROM OPPORTUNITY A1 WHERE 1 = 2 "
End If
End With
End Sub
|