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 "操作结束"

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