以下存储过程,在VB中,怎样将窗体各个控件中的数据传递给存储过程,而让数据插入到数据库中去呢?
CREATE PROCEDURE sp_ins_loca
@lc_code varchar(10),
@lc_arcode_ar varchar(5),
@lc_name varchar(30),
@lc_type bit,
@lc_opendate smalldatetime,
@lc_opentime smalldatetime,
@lc_regdate smalldatetime,
@lc_rs bit
AS
INSERT lc_location
(lc_code,
lc_arcode_ar,
lc_name,
lc_type,
lc_opendate,
lc_opentime,
lc_regdate,
lc_rs)
VALUES
(@lc_code,
@lc_arcode_ar,
@lc_name,
@lc_type,
@lc_opendate,
@lc_opentime,
@lc_regdate,
@lc_rs)
GO
CREATE PROCEDURE sp_ins_loca
@lc_code varchar(10),
@lc_arcode_ar varchar(5),
@lc_name varchar(30),
@lc_type bit,
@lc_opendate smalldatetime,
@lc_opentime smalldatetime,
@lc_regdate smalldatetime,
@lc_rs bit
AS
INSERT lc_location
(lc_code,
lc_arcode_ar,
lc_name,
lc_type,
lc_opendate,
lc_opentime,
lc_regdate,
lc_rs)
VALUES
(@lc_code,
@lc_arcode_ar,
@lc_name,
@lc_type,
@lc_opendate,
@lc_opentime,
@lc_regdate,
@lc_rs)
GO
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim Rs As New ADODB.Recordset
Public Rst As New ADODB.Recordset
Public var_result As Integer
Public var_ErrorInfo As String
Public int_ErrorCount As Double
Dim dir
'====================================================
'# 初始化时,与数据库建立连接
'====================================================
Private Sub Class_Initialize()
conn.ConnectionString = "driver=sql server;server=ytyf;database=entrees;UID=sa;PWD="
conn.Open
comm.ActiveConnection = conn
End Sub
' ===============添加需排产的部门==================
Public Function flowdepartadd(ByVal orderform As String )
comm.Parameters.Refresh
comm.CommandText = "dbo.flowdepartadd"
comm.CommandType = adCmdStoredProc
comm.Parameters.Append comm.CreateParameter("@result", adInteger, adParamReturnValue)
comm.Parameters.Append comm.CreateParameter("@orderform", adVarChar, adParamInput, 15, orderform)
comm.Execute
var_result = comm.Parameters("@result")
If var_result = 1 Then
int_ErrorCount = 50000
var_ErrorInfo = "增加成功"
Else
int_ErrorCount = 50001
var_ErrorInfo = " 增加失败"
End If
End Function
Private strSql As String'执行添加存储过程
Private Sub cmdExecAdd_Click()
'声明并实例化一个命令对象
Dim cmd As New ADODB.Command
'声明并实例化参数对象
Dim proInput1 As New ADODB.Parameter
Dim proInput2 As New ADODB.Parameter
Dim proInput3 As New ADODB.Parameter
Dim proOutput As New ADODB.Parameter
'设置命令对象
With cmd
.ActiveConnection = mycn
.CommandText = "pr_add"
.CommandType = adCmdStoredProc
End With
'设置第一个输入参数
With proInput1
.Type = adChar
.Size = 10
.Direction = adParamInput
.Value = Text1.Text
End With
'将参数加入到Command对象的Parameters集合中
cmd.Parameters.Append proInput1
'设置第二个输入参数
With proInput2
.Type = adChar
.Size = 10
.Direction = adParamInput
.Value = Text2.Text
End With
'将参数加入到Command对象的Parameters集合中
cmd.Parameters.Append proInput2
'设置第三个输入参数
With proInput3
.Type = adChar
.Size = 10
.Direction = adParamInput
.Value = Text3.Text
End With
'将参数加入到Command对象的Parameters集合中
cmd.Parameters.Append proInput3
'设置输出参数
With proOutput
.Type = adInteger
.Direction = adParamOutput
End With
'将参数加入到Command对象的Parameters集合中
cmd.Parameters.Append proOutput
'执行命令
cmd.Execute
If proOutput.Value = 0 Then
MsgBox "ok!"
Call ShowData
Else
MsgBox "失败了"
End If
End Sub'执行条件查询存储过程
Private Sub cmdExecSel_Click()
MSHFlexGrid1.Clear
strSql = "select * from stu"
Set rs = ExecuteSQL(strSql)
Set MSHFlexGrid1.DataSource = rs
End SubPrivate Sub cmdExit_Click()
Unload Me
End SubPrivate Sub Form_Load()
Dim strCN As String
'建立数据库连接
strCN = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=student;Data Source=."
Call LinkDB(strCN)
Call ShowData
End SubPrivate Sub ShowData()
strSql = "select * from stu"
Set rs = ExecuteSQL(strSql)
Set MSHFlexGrid1.DataSource = rs
End Sub