Dim f_cmdKeep As New ADODB.Command With f_cmdKeep .ActiveConnection = m_cnnAdo .CommandType = adCmdStoredProc .CommandText = "sp_KeepAccount" .Parameters.Append .CreateParameter("AccCode", adChar, adParamInput, 4, m_strAccountCode) .Parameters.Append .CreateParameter("CurDate", adVarChar, adParamInput, 6, m_strPeriod) .Parameters.Append .CreateParameter("VouNo", adDouble, adParamInput, 9, Val(lblNum.Caption)) .Parameters.Append .CreateParameter("UserID", adVarChar, adParamInput, 18, m_strUserID) .Parameters.Append .CreateParameter("Flag", adVarChar, adParamInput, 2, f_strFlag) .Parameters.Append .CreateParameter("ReturnCode", adVarChar, adParamOutput, 1) .Execute If .Parameters("ReturnCode") = "0" Then lblAccount.Visible = True SetEnabled False Else lblVoucher(11).Caption = Left(lblVoucher(11).Caption, 2) & " " lblAccount.Visible = False End If End With Set f_cmdKeep = Nothing 这是调用的方法
vb可以存写存储过程,我以mssql中的northwind库为例 Private Sub Command1_Click() Dim com As New ADODB.Command Dim con As New ADODB.Connectioncon.ConnectionString = "driver={SQL Server};server=10.50.165.11;" & _ "uid=sa;pwd=;database=Northwind"
con.Open com.ActiveConnection = con com.CommandText = "create proc sunny (@x int) as select * from orders where employeeid=@x " com.CommandType = adCmdTextEnd Sub 以上就是写了一个存储过程到数据库中 Private Sub Command2_Click() Dim con As New ADODB.Connection Dim com As New ADODB.Command Dim rec As New ADODB.Recordset Dim pa As Parametercon.ConnectionString = "driver={SQL Server};server=10.50.165.11;" & _ "uid=sa;pwd=;database=Northwind"
con.Opencom.ActiveConnection = con com.CommandText = "sunny" com.CommandType = adCmdStoredProc Set pa = com.CreateParameter("@x", adInteger, adParamInput) com.Parameters.Append paSet rec = com.Execute Text1.Text = rec.Fields(0)end sub这就是调用存储过程,虽然此存储过程并没有什么意义,但他能说明vb中生成、调用存储过程
create proc Test(@id int ,@name varchar(20) output) as begin select @name=customername from customers where customerid= @id End
Dim cmd As ADODB.Command Dim par1 As ADODB.Parameter Dim par2 As ADODB.Parameter Set cmd = New ADODB.Command cmd.CommandText = "test" cmd.CommandType = adCmdStoredProc Set par1 = cmd.CreateParameter("a", adInteger, adParamInput, , 3) cmd.Parameters.Append par1 Set par2 = cmd.CreateParameter("b", adVarChar, adParamOutput, 20) cmd.Parameters.Append par2 Set cmd.ActiveConnection = cnDatabase cmd.Execute Debug.Print par2.Value Set cmd = Nothing
With f_cmdKeep
.ActiveConnection = m_cnnAdo
.CommandType = adCmdStoredProc
.CommandText = "sp_KeepAccount"
.Parameters.Append .CreateParameter("AccCode", adChar, adParamInput, 4, m_strAccountCode)
.Parameters.Append .CreateParameter("CurDate", adVarChar, adParamInput, 6, m_strPeriod)
.Parameters.Append .CreateParameter("VouNo", adDouble, adParamInput, 9, Val(lblNum.Caption))
.Parameters.Append .CreateParameter("UserID", adVarChar, adParamInput, 18, m_strUserID)
.Parameters.Append .CreateParameter("Flag", adVarChar, adParamInput, 2, f_strFlag)
.Parameters.Append .CreateParameter("ReturnCode", adVarChar, adParamOutput, 1)
.Execute
If .Parameters("ReturnCode") = "0" Then
lblAccount.Visible = True
SetEnabled False
Else
lblVoucher(11).Caption = Left(lblVoucher(11).Caption, 2) & " "
lblAccount.Visible = False
End If
End With
Set f_cmdKeep = Nothing
这是调用的方法
Private Sub Command1_Click()
Dim com As New ADODB.Command
Dim con As New ADODB.Connectioncon.ConnectionString = "driver={SQL Server};server=10.50.165.11;" & _
"uid=sa;pwd=;database=Northwind"
con.Open
com.ActiveConnection = con
com.CommandText = "create proc sunny (@x int) as select * from orders where employeeid=@x "
com.CommandType = adCmdTextEnd Sub
以上就是写了一个存储过程到数据库中
Private Sub Command2_Click()
Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Dim rec As New ADODB.Recordset
Dim pa As Parametercon.ConnectionString = "driver={SQL Server};server=10.50.165.11;" & _
"uid=sa;pwd=;database=Northwind"
con.Opencom.ActiveConnection = con
com.CommandText = "sunny"
com.CommandType = adCmdStoredProc
Set pa = com.CreateParameter("@x", adInteger, adParamInput)
com.Parameters.Append paSet rec = com.Execute
Text1.Text = rec.Fields(0)end sub这就是调用存储过程,虽然此存储过程并没有什么意义,但他能说明vb中生成、调用存储过程
as
begin
select @name=customername from customers where customerid= @id
End
Dim cmd As ADODB.Command
Dim par1 As ADODB.Parameter
Dim par2 As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.CommandText = "test"
cmd.CommandType = adCmdStoredProc
Set par1 = cmd.CreateParameter("a", adInteger, adParamInput, , 3)
cmd.Parameters.Append par1
Set par2 = cmd.CreateParameter("b", adVarChar, adParamOutput, 20)
cmd.Parameters.Append par2
Set cmd.ActiveConnection = cnDatabase
cmd.Execute
Debug.Print par2.Value
Set cmd = Nothing
command.tex为要执行的存储过程
command.parameter为参数,她是一个数组,下标从1开始
然后就command.execute
你估计没有写你自己的存储过程