vb和sql存储过程的问题

[lincaicong]

如何在vb中利用ado给存储过程传递参数

[ukyoxh]

给你一个例子

Append 和 CreateParameter 方法范例
该范例使用 Append 和 CreateParameter 方法执行具有输入参数的存储过程。

Public Sub AppendX()

Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String

' 打开连接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
cnn1.CursorLocation = adUseClient

' 使用一个参数打开命令对象。
Set cmdByRoyalty = New ADODB.Command
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc

' 获取参数值并追加参数。
intRoyalty = Trim(InputBox("Enter royalty:"))
Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _
adInteger, adParamInput)
cmdByRoyalty.Parameters.Append prmByRoyalty
prmByRoyalty.Value = intRoyalty

' 通过执行命令创建记录集。
Set cmdByRoyalty.ActiveConnection = cnn1
Set rstByRoyalty = cmdByRoyalty.Execute

' 打开 Authors 表以获取作者姓名进行显示。
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", cnn1, , , adCmdTable

' 打印记录集中的当前数据，从 Authors 表中添加作者姓名。
Debug.Print "Authors with " & intRoyalty & " percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print " " & rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop

rstByRoyalty.Close
rstAuthors.Close
cnn1.Close

End Sub

[daisy8675]

mark下。好象沒有這麼復雜噢

[lincaicong]

那给个简单的

[yuanyuan0]

晕哦~怎么这么大堆哦？我头晕了晕了~不知道怎么做了~~
你是要在VB中执行存储过程吗？

[online]

datagrid,5个文本框，两个按钮

Option Explicit
Private mConn As Connection

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "insert_users"
cmd.CommandType = adCmdStoredProc

Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text))
cmd.Parameters.Append param

Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))
cmd.Parameters.Append param

Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text))
cmd.Parameters.Append param

Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text))
cmd.Parameters.Append param

Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text))
cmd.Parameters.Append param

Set rs = cmd.Execute

Command2_Click

End Sub

Private Sub Command2_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "select_users"
cmd.CommandType = adCmdStoredProc

mConn.CursorLocation = adUseClient '设置为客户端

Set rs = cmd.Execute()

MsgBox rs.RecordCount
Set DataGrid1.DataSource = rs
DataGrid1.Refresh

End Sub

Private Sub Form_Load()
'open the connection
Set mConn = New Connection

mConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TestUser;Data Source=yang"
mConn.Open
Dim rs As New ADODB.Recordset
'Set rs = New ADODB.Recordset
rs.Open "users", mConn, adOpenStatic, adLockPessimistic

MsgBox rs.RecordCount

End Sub



Private Sub Form_Unload(Cancel As Integer)

mConn.Close
Set mConn = Nothing
End Sub

[online]

存储过程
CREATE PROCEDURE insert_users @truename char(20), @regname char(20),@pwd char(20),@sex char(20),@email char(20)
AS
insert into users(truename,regname,pwd,sex,email) values(@truename,@regname,@pwd,@sex,@email)
GO

CREATE PROCEDURE select_users
AS
select * from users
GO

[zjcxc]

Sub test()
Dim iCmd As ADODB.Command
Dim iRe As ADODB.Recordset
Dim count1&, count2&, count3&, count4&

Set iCmd = New ADODB.Command
With iCmd
.ActiveConnection = iConc 'iConc是数据库连接字符串
.CommandType = 4 '类型为存储过程adCmdStoredProc

.CommandText = "p_qry" '调用的存储过程名
.Parameters.Refresh
.Parameters("@userid ") = 12 '为输入参数赋值

'得到输出参数的结果
.Execute
count1 = .Parameters("@count1")
count2 = .Parameters("@count2")
count3 = .Parameters("@count3")
count4 = .Parameters("@count4")

'得到记录集的输出结果
Set iRe = .Execute
End With
End Sub

[csdnmzk]

好热闹，该说的你们都说了，其实，你可以参考一下msdn，哪里更详细。

[lincaicong]

我知道msdn里有，可我在网上找了 大半天也没找到下载的。工作又在别人的机器上，没办法只好来求助各位高手