Question

VB中如何运行SQL语句

我的SQL中有一个过程文件MM，在VB环境下，怎么才能运行这个过程文件

Answer 1

sql中有一个过程文件？？
我孤陋寡闻，没听说过。。。
是不是存储过程？
以下是vb访问存储过程的代码，从网上copy过来的。。。
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.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=yang"

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

mConn.Open

End Sub

' 关闭数据连接

Private Sub Form_Unload(Cancel As Integer)

mConn.Close

Set mConn = Nothing

End Sub