vb怎样调用sql的存储过程

dim myConn as object

dim myRecord aS OBJECT

Dim mySQL As String

Dim strSQL As String

SET myConn CreateObject("ADODB.Connection") '创建对象

SET myConn CreateObject("ADODB.Connection") '创建对象

Private Sub Form_Load()

Dim mySQL As String

Dim strSQL As String

'设定连接字符串

mySQL = "Provider=Microsoft.Jet.OLEDB.4.0Persist Security Info=False"

mySQL = mySQL + "Data Source=" &App.Path &"\Product.mdb"

myConn.ConnectionString = mySQL '设定连接字符串

'MsgBox mySQL

myConn.Open '打开连接

myRecord.ActiveConnection = myConn '设定RecordSeet的连接对象为Connection

strSQL = "select * from Products" '设定查询字符串

'打开myRecord

myRecord.Open strSQL, , adOpenDynamic, adLockOptimistic

'显示数据

ShowData

End Sub

Private Sub ShowData()

On Error Resume Next

'产品编号

Text1.Text = myRecord.Fields("ProductID").Value

'产品名称

Text2.Text = myRecord.Fields("ProductName").Value

'供应商编号

Text3.Text = myRecord.Fields("SupplierID").Value

'类别编号

Text4.Text = myRecord.Fields("CategoryID").Value

'单位数量

Text5.Text = myRecord.Fields("QuantityPerUnit").Value

'单价

Text6.Text = myRecord.Fields("UnitPrice").Value

'库存数量

Text7.Text = myRecord.Fields("UnitsInStock").Value

'订单数量

Text8.Text = myRecord.Fields("UnitsOnOrder").Value

'重订等级

Text9.Text = myRecord.Fields("ReorderLevel").Value

End Sub

'后一条按钮单击事件

Private Sub Command8_Click()

'移动记录到下一条的方法是MoveNext

'在调用MoveNext方法之前，首先需要判断，当前是不是已经超出尾纪录

'若超出尾记录，再执行MoveNext会出错

If Not myRecord.EOF Then

myRecord.MoveNext

Else

'超出尾记录，使用MoveLast恢复指向尾记录

myRecord.MoveLast

End If

'显示数据

ShowData

End Sub

'前一条按钮单击事件

Private Sub Command7_Click()

'判断是否超出头记录

'若超出头记录，再执行MovePrevious会出错

If Not myRecord.BOF Then

myRecord.MovePrevious

Else

'超出头记录

'使用MoveFirst恢复到指向第一条记录

myRecord.MoveFirst

End If

'显示数据

ShowData

End Sub

'尾记录按钮单击事件

Private Sub Command9_Click()

'调用MoveLast

myRecord.MoveLast

'显示记录

ShowData

End Sub

Private Sub Command5_Click()

'调用Recordset对象cancelUpdate方法取消数据保存

myRecord.CancelUpdate

myRecord.MoveFirst

'显示数据

ShowData

End Sub

'首记录按钮单击事件

Private Sub Command6_Click()

'调用MoveFirst移动

myRecord.MoveFirst

'显示记录

ShowData

End Sub

Private Sub Command4_Click()

On Error Resume Next

'保存数据，这里需要通过编程来实现

'产品编号

'产品编号不能为空

If Text1.Text = "" Then

MsgBox "产品编号不能为空!"

Text1.SetFocus

Exit Sub

End If

'其他文本框的判断类似，这里省略

'保存产品编号

myRecord.Fields("ProductID").Value = Val(Text1.Text)

'保存产品名称

myRecord.Fields("ProductName").Value = Text2.Text

'保存供应商编号

myRecord.Fields("SupplierID").Value = Val(Text3.Text)

'保存类别编号

myRecord.Fields("CategoryID").Value = Val(Text4.Text)

'保存单位数量

myRecord.Fields("QuantityPerUnit").Value = Text5.Text

'保存单价

myRecord.Fields("UnitPrice").Value = Val(Text6.Text)

'保存库存数量

myRecord.Fields("UnitsInStock").Value = Text7.Text

'保存订单数量

myRecord.Fields("UnitsOnOrder").Value = Val(Text8.Text)

'保存重订等级

myRecord.Fields("ReorderLevel").Value = Val(Text9.Text)

'调用Recordset对象Update方法保存数据

myRecord.Update

'显示数据

ShowData

End Sub

'引用Microsoft ActiveX Data Objects 2.8 Library

Dim Cnn As ADODB.Connection

Dim Rs As ADODB.Recordset

Dim Cnn_c As New ADODB.Command

Dim cnn_p As ADODB.Parameter

dim CourseID as string,CustomerID as string

Set Cnn = New ADODB.Connection

Set Rs = New ADODB.Recordset

'On Error Resume Next

Cnn.ConnectionString = ConnStr '连接字符串

With Cnn

    .CursorLocation = adUseClient

    .Open '连接

End With

Set Cnn_c.ActiveConnection = Cnn

With Cnn_c

    .CommandType = adCmdStoredProc '命令类型：存储过程

    .CommandText = "sy_select_course" '存储过程名

End With

Set cnn_p = Cnn_c.CreateParameter("ReTurn", adInteger, adParamReturnValue)

Cnn_c.Parameters.Append cnn_p '添加参数ReTurn(返回值)

Set cnn_p = Cnn_c.CreateParameter("CustomerID", adVarChar, adParamInput, 4, CustomerID)

Cnn_c.Parameters.Append cnn_p'添加参数CustomerID

Set cnn_p = Cnn_c.CreateParameter("CourseID", adVarChar, adParamInput, 4, CourseID)

Cnn_c.Parameters.Append cnn_pSet '添加参数CourseID

Rs = Cnn_c.Execute '执行

If Rs.RecordCount > 0 Then

    '这里处理查询

    Rs.Close'关闭记录集

End If

Cnn.Close'关闭连接

Set cnn_p = Nothing

Set Cnn_c = Nothing

Set Rs = Nothing

Set Cnn = Nothing

???? [jifen] [decimal](18, 2) NULL????) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]????GO????????ALTER TABLE [dbo].[users] WITH NOCHECK ADD???? CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED???? (???? [id]???? ) ON [PRIMARY]????GO????????????????存储过程select_users????CREATE PROCEDURE select_users @regname char(20), @numrows int OUTPUT????AS???? Select * from users???????? SELECT @numrows = @@ROWCOUNT???????? if @numrows = 0???? return 0???? else return 1????GO????????存储过程insert_users????CREATE PROCEDURE insert_users @truename char(20), @regname char(20),@pwd char(20),@sex char(20),@email char(20),@jifen decimal(19,2)????AS????insert into users(truename,regname,pwd,sex,email,jifen) values(@truename,@regname,@pwd,@sex,@email,@jifen)????GO????????????在VB环境中，添加DataGrid控件，4个按钮，6个文本框????代码简单易懂。????????‘引用microsoft active data object 2.X library????Option Explicit????Dim mConn As ADODB.Connection????Dim rs1 As ADODB.Recordset????Dim rs2 As ADODB.Recordset????Dim rs3 As ADODB.Recordset????Dim rs4 As ADODB.Recordset????????Dim cmd As ADODB.Command????Dim param As ADODB.Parameter????????'这里用第一种方法使用存储过程添加数据????Private Sub Command1_Click()????????Set cmd = New ADODB.Command????Set rs1 = New ADODB.Recordset????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????‘下面的类型需要注意，如果不使用adSingle,会发生一个精度无效的错误????Set param = cmd.CreateParameter("jifen", adSingle, adParamInput, 50, Val(txtjifen.Text))????cmd.Parameters.Append param????Set rs1 = cmd.Execute????????Set cmd = Nothing????Set rs1 = Nothing????????End Sub????????'这里用第二种方法使用存储过程添加数据????Private Sub Command2_Click()????Set rs2 = New ADODB.Recordset????Set cmd = New ADODB.Command????cmd.ActiveConnection = mConn????cmd.CommandText = "insert_users"????cmd.CommandType = adCmdStoredProc????????cmd.Parameters("@truename") = Trim(txttruename.Text)????cmd.Parameters("@regname") = Trim(txtregname.Text)????cmd.Parameters("@pwd") = Trim(txtpwd.Text)????cmd.Parameters("@sex") = Trim(txtsex.Text)????cmd.Parameters("@email") = Trim(txtemail.Text)????cmd.Parameters("@jifen") = Val(txtjifen.Text)????????Set rs2 = cmd.Execute????????Set cmd = Nothing????Set rs1 = Nothing????End Sub????????'这里用第三种方法使用连接对象来插入数据????Private Sub Command4_Click()???? Dim strsql As String???? strsql = "insert_users '" &Trim(txttruename.Text) &"','" &Trim(txtregname.Text) &"','" &Trim(txtpwd.Text) &"','" &Trim(txtsex.Text) &"','" &Trim(txtemail.Text) &"','" &Val(txtjifen.Text) &"'"???? Set rs3 = New ADODB.Recordset???? Set rs3 = mConn.Execute(strsql)???????? Set rs3 = Nothing????End Sub????????'利用存储过程显示数据????‘要处理多种参数，输入参数，输出参数以及一个直接返回值???? Private Sub Command3_Click()????Set rs4 = New ADODB.Recordset????Set cmd = New ADODB.Command????cmd.ActiveConnection = mConn????cmd.CommandText = "select_users"????cmd.CommandType = adCmdStoredProc????????'返回值????Set param = cmd.CreateParameter("RetVal", adInteger, adParamReturnValue, 4)????cmd.Parameters.Append param????'输入参数????Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))????cmd.Parameters.Append param????'输出参数????Set param = cmd.CreateParameter("numrows", adInteger, adParamOutput)????cmd.Parameters.Append param????????Set rs4 = cmd.Execute()????If cmd.Parameters("RetVal").Value = 1 Then????MsgBox cmd.Parameters("numrows").Value????Else????MsgBox "没有记录"????End If????????MsgBox rs4.RecordCount????Set DataGrid1.DataSource = rs4????DataGrid1.“refresh”????????End Sub????????'连接数据库????Private Sub Form_Load()????Set mConn = New Connection????mConn.ConnectionString = "Provider=SQLOLEDB.1Persist Security Info=FalseUser ID=saInitial Catalog=TestData Source=yang"????mConn.CursorLocation = adUseClient '设置为客户端????mConn.Open????End Sub????'关闭数据连接????Private Sub Form_Unload(Cancel As Integer)????mConn.Close????Set mConn = Nothing????End Sub????

---