在vb中动态创建 调用存储过程

'定义变量
Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

'打开连接
cnn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=maindb;Data Source=jim"

cnn.CursorLocation = adUseClient
cnn.Open

If cnn.State <> 1 Then MsgBox "失败”

Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText

 '''''''''''''''检查存取过程是否存在，若有则删除
 Set rst = Nothing
 rst.Open "SELECT name FROM sysobjects WHERE name = 'proc_name1' AND type = 'P'", cnn
 If rst.RecordCount > 0 Then
   cmd.CommandText = "DROP PROCEDURE proc_name1 "
   cmd.Execute
 End If
 ''''''''''''''''''''''''''''''

 Set rst = Nothing
 rst.Open "SELECT name FROM sysobjects WHERE name = 'proc_name1' AND type = 'P'", cnn
 If rst.RecordCount > 0 Then
   cmd.CommandText = "DROP PROCEDURE proc_name1 "
   cmd.Execute
 End If
 ''''''''''''''''''''''''''''''
'创存储过程proc_name1 (@var_item_no 为输入参数)  employee为当前库中已知表
cmd.CommandText = "Create proc proc_name1 @var_item_no varchar(40) as Select *  From employee  where emp_id like @var_item_no"
cmd.Execute

'通过传递参数值来执行存取过程
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_name1"

cmd.Parameters.Refresh
cmd.Parameters(1).Value = Text1.Text
Set rst = Nothing
Set rst = cmd.Execute()

posted on 2005-09-26 15:27 木子的blog 阅读(658)   

--------------------next---------------------