'存储过程sp_author ' Create Procedure sp_author ' @ID varchar(11) ' AS ' select * from authors ' where au_id = @ID Option ExplicitPrivate cn As ADODB.Connection Private cmd As ADODB.Command Private rs As ADODB.Recordset Private param As ADODB.Parameter Private Sub Form_Initialize()
'连接数据库 Set cn = New ADODB.Connection With cn .Provider = "SQLOLEDB.1" .ConnectionString = "Persist Security Info=False;User ID=sa;Password=;Initial Catalog=pubs;" & _ "Data source=." .Open End With
'创建命令对象 Set cmd = New ADODB.Command cmd.ActiveConnection = cn cmd.CommandText = "sp_author" '指定命令的内容 cmd.Prepared = True cmd.CommandType = adCmdStoredProc '指定命令的内容是某个存储过程的名字
'创建参数对象,且将参数对象加入到命令对象的参数集合中 Set param = cmd.CreateParameter("ID", adVarChar, adParamInput, 11) cmd.Parameters.Append param
End Sub Private Sub cmdExecute_Click() Dim txt As TextBox Dim i As Integer
然后设置command的CommandType为adCmdStoredProc然后用execute执行
' Create Procedure sp_author
' @ID varchar(11)
' AS
' select * from authors
' where au_id = @ID
Option ExplicitPrivate cn As ADODB.Connection
Private cmd As ADODB.Command
Private rs As ADODB.Recordset
Private param As ADODB.Parameter
Private Sub Form_Initialize()
'连接数据库
Set cn = New ADODB.Connection
With cn
.Provider = "SQLOLEDB.1"
.ConnectionString = "Persist Security Info=False;User ID=sa;Password=;Initial Catalog=pubs;" & _
"Data source=."
.Open
End With
'创建命令对象
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "sp_author" '指定命令的内容
cmd.Prepared = True
cmd.CommandType = adCmdStoredProc '指定命令的内容是某个存储过程的名字
'创建参数对象,且将参数对象加入到命令对象的参数集合中
Set param = cmd.CreateParameter("ID", adVarChar, adParamInput, 11)
cmd.Parameters.Append param
End Sub
Private Sub cmdExecute_Click()
Dim txt As TextBox
Dim i As Integer
'设定命令对象的参数的值 - 即存储过程的参数的值
cmd.Parameters(0).Value = txtAuthorId.Text
'执行命令对象 - 即执行存储过程
Set rs = cmd.Execute
'将txtData控件数组中的每个TextBox依次绑定到返回记录集的各个字段
For Each txt In txtData
Set txt.DataSource = rs
txt.DataField = rs.Fields(i + 1).Name
i = i + 1
Next
End Sub
试试;select colname=? from tablename