28,409
社区成员




Dim Conn ,rs, sql,i
Sub OpenConn()
if IsObject(Conn) then exit sub
Dim ConnStr
ConnStr = "Provider = Sqloledb; User ID =XXXX; Password =XXXX; Initial Catalog =XXXX; Data Source=." '填上自己的数据库信息
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open ConnStr
End Sub
Sub CloseConn()
If IsObject(Conn) Then
Conn.Close
Set Conn = Nothing
End If
End Sub
'the Content below added by wang 2008-3-13
'准备参数
private function parpareCmd(sql,ps)
OpenConn
dim p, cmd, rs , pnum
set cmd = Server.CreateObject("Adodb.Command")
cmd.CommandText = sql
cmd.ActiveConnection = Conn
pnum = ParamNum(sql)
'检查参数个数是否小于必需数目。
'CreateParameter([Name] , [type] , [Direction] , [Size] , [Value] )
if (not isnull(ps)) then
if (ubound(ps)+1) < pnum then
Response.write "参数个数应该是 " & pnum & ". 但实际只传递了" & (ubound(ps)+1)
Response.end
end if
for i = 0 to pnum-1
set p = cmd.CreateParameter( , 130, ,chkLength(Ps(i)), Ps(i))
'response.write "P" & I & "=" & Ps(i) & "L:" &chkLength(Ps(i)) & "<br />"
cmd.Parameters.Append p
next
end if
set parpareCmd = cmd
end function
'通过sql中'?'号的个数得知参数个数
function ParamNum(sql)
ParamNum = 0
for i=1 to LEN(sql)
if mid(sql,i,1)="?" then ParamNum = ParamNum + 1
next
end function
'执行更新查询
function ExecNonQuery(sql,ps)
dim cmd,rs
set cmd = parpareCmd(sql,ps)
set rs = cmd.Execute
set cmd=nothing
set rs=nothing
end function
'返回单个整数,如果为空则当作0处理
function ExecInt(sql, ps)
dim cmd,rs
set cmd = parpareCmd(sql,ps)
set rs = cmd.Execute
ExecInt=TurnInt(Rs(0))
set rs=nothing
set cmd=nothing
end function
'返回单个字符串,如果为空则当作空串处理
function ExecStr(sql, ps)
dim cmd,rs
set cmd = parpareCmd(sql,ps)
set rs = cmd.Execute
if isnull(Rs(0)) then
ExecStr = ""
else
ExecStr=CStr(Rs(0))
end if
set rs=nothing
set cmd=nothing
end function
'返回一个记录集
function ExecRs(sql, ps)
dim cmd,rs
set cmd = parpareCmd(sql,ps)
set rs = cmd.Execute()
set ExecRs=rs
set cmd=nothing
end function
function chkLength(s)
if isnull(s) then
chkLength = 2
else
chkLength = max(2,len(s))
end if
end function
'数字转换,将null值视为0处理
function TurnInt(c)
if isnull(c) or c="" or not IsNumeric(c) then
TurnInt=0
else
TurnInt=CLng(c)
end if
end function