vbscript调用存储过程(一)

VBScript代码可以直接放在.vbs文件中，双击直接运行。

1. 数据库采用SQL Server2005,新建一个数据库，创建表T_UserInfo和存储过程Proc_T_AddUser

?

-- 为测试建立的用户信息表

Create Table T_UserInfo

(

    UID         int identity(1,1) primary key,      -- 用户ID,主键，自动增长

    UName       varchar(20) not null,               -- 用户姓名

    UAge        int default 0,                      -- 年龄

    UMoney      int default 0,                      -- 存款

    InTime      datetime default getdate(),         -- 该记录的加入时间(默认取当前时间)

    Intro       varchar(500)                        -- 自我介绍

)

 

-- 添加用户

Create Procedure Proc_T_AddUser

(

    @UName      varchar(20),

    @UAge       int = 0,

    @UMoney     int = 0,

    @Intro      varchar(500)

)

as

begin

    Insert into T_UserInfo(UName,UAge,UMoney,Intro) values(@UName,@UAge,@UMoney,@Intro)

end

2. 新建一个aa.vbs,添加代码：

?

On error resume Next

 

Dim mu_ConnString, mu_SelectSql

Dim objConnection, objCommand, objRecordset

 

mu_ConnString = "driver={SQL Server};Server=xx.xx.xx.xx;database=TestFax;Uid=sa;pwd=xxx;"    '连接SQL Server2005

'mu_ConnString = "Provider=MSDAORA.1;Data Source=GSxx;User ID=GSxx;Password=GSxx"          '连接Oracle数据库

'mu_SelectSql = "Select count(1) as Num from T_UserInfo"

'mu_SelectSql = "Insert into T_UserInfo(UName,UAge,UMoney) values('陈天2',20, 3000)"

 

set objConnection = CreateObject("ADODB.Connection")

set objCommand = CreateObject("ADODB.Command")

'set objRecordset = CreateObject("ADODB.Recordset")

 

    '为Command设置参数

    objCommand.CommandType = 4  'adCmdStoredProc(不能写这个字符串，好像不认识)

    objCommand.CommandText = "Proc_T_AddUser"

 

    Dim p1, p2, p3, p4

    set p1 = CreateObject("ADODB.Parameter")

    p1.Name = "UName"

    p1.Type = 200       'adVarChar

    p1.Size = 20

    p1.Direction = 1

    p1.Value = "陈天2"

 

    set p2 = CreateObject("ADODB.Parameter")

    p2.Name = "UAge"

    p2.Type = 3         'adInteger

    p2.Direction = 1

    p2.Value = 21

 

    set p3 = CreateObject("ADODB.Parameter")

    p3.Name = "UMoney"

    p3.Type = 3

    p3.Direction = 1

    p3.Value = 21

 

    set p4 = CreateObject("ADODB.Parameter")

    p4.Name = "Intro"

    p4.Type = 200

    p4.Size = 500

    p4.Direction = 1

    p4.Value = "陈天2 Introduction"

 

    objCommand.Parameters.Append p1

    objCommand.Parameters.Append p2

    objCommand.Parameters.Append p3

    objCommand.Parameters.Append p4

 

objConnection.Open mu_ConnString                      '打开数据库

 

if(objConnection.State = 0) Then

    MsgBox "连接数据库失败"

else

    set objCommand.ActiveConnection = objConnection    '这句必须放在objConnection.Open之后，可能因为这是传值的

 

    objCommand.Execute

 

    objConnection.Close

    set objCommand = Nothing

    set objConnection = Nothing

end if

MsgBox "操作结束"

执行这个存储过程，就可以向表中插入一条数据了，因为设置了自动增长的字段作主键，所以这段代码可以重复运行!