ACCE ADP组合框的行来源如何使用参数存储过程

文章作者 100test 发表时间 2007:10:10 12:46:12
来源 100Test.Com百考试题网

组合框的行来源中只能写存储过程名，无法加上参数，我们这里用值列表绕过次限制
Public Sub CreateP2Procedure()
创建本示例需要用的参数存储过程
表名和字段名请按你的实际情况填写

Dim strSQL As String
strSQL = "Create Procedure procCreateMyList(@P1 int =0,@P2 nvarChar(50)=) As SELECT field1 FROM tblTable1 WHERE field2 = @P1 and field3 like @P2"
CurrentProject.Connection.Execute strSQL
End Sub

Private Sub Form_Open(Cancel As Integer)
将本过程放在窗体的打开事件中，设定组合框的行来源

Me.ComboBox.RowSourceType = "value list"
Me.ComboBox.RowSource = GetRowSource("p2", 155, "%")
End Sub

Public Function GetRowSource(ByVal strCommandText As String, _
Optional ByVal varP1, _
Optional ByVal varP2, _
Optional ByVal varP3)
本函数作用：
将存储过程的执行结果转换为用分号分隔的值列表
现在参数是 3 个，可以更改一下，用数组来代替

Dim Rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Const DELIM As String = "."
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strCommandText
.CommandType = adCmdStoredProc
End With

If Not IsMissing(varP1) Then
Set prm = cmd.CreateParameter("P1", _
adInteger, adParamInput, , varP1)
cmd.Parameters.Append prm
End If
If Not IsMissing(varP2) Then
Set prm = cmd.CreateParameter("P2", _
adVarChar, adParamInput, 50, varP2)
cmd.Parameters.Append prm
End If
If Not IsMissing(varP3) Then
Set prm = cmd.CreateParameter("P3", _
adVarChar, adParamInput, 50, varP3)
cmd.Parameters.Append prm
End If

Rs.Open cmd
GetRowSource = ""
If Not Rs.EOF Then
GetRowSource = Rs.GetString(adClipString, , _
DELIM, DELIM)
End If

End Function