home
-- accessing sql server Sps from ASP
Author Nigel Rivett
-- usage of inc file functions
dim objCmd, objRset
set objCmd = Server.CreateObject("ADODB.Command")
objCmd.Parameters.append DBParm(intParm1, adDecimal, "ID", 0)
objCmd.Parameters.append DBParm(intParm2, adDecimal, "ID", 0)
set objRset = DBOpenResultSet("SPName",objCmd)
'----------------------------------------------------------------------------
' creates result set from stored procedure name and adodb.command with populated parameters
Function DBOpenResultSet(sStoredProc, objCmd)
Dim objRSet, objConn
Set objConn = connectDB()
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = sStoredProc
Set objRSet = Server.CreateObject("ADODB.Recordset")
objRSet.CursorLocation=adUseClient
objRSet.Open objCmd,,adOpenForwardOnly,adLockReadOnly
set objRSet.activeConnection = nothing
Set objCmd = Nothing
Set objConn = Nothing
Set DBOpenResultSet = objRSet
End Function
'----------------------------------------------------------------------------
' Execs a stored procedure name and adodb.command with populated parameters - returns number of rows
'----------------------------------------------------------------------------
Function DBExec(sStoredProc, objCmd)
Dim objConn, NoRows
Set objConn = connectDB()
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = sStoredProc
objCmd.Execute NoRows
Set objCmd = Nothing
Set objConn = Nothing
DBExec = NoRows
End Function
'----------------------------------------------------------------------------
' Execs a stored procedure name and adodb.command with populated parameters - returns number of rows
' and command object for output parameters
'----------------------------------------------------------------------------
Function DBExecCmd(sStoredProc, objCmd)
Dim objConn, NoRows
Set objConn = connectDB()
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = sStoredProc
objCmd.Execute NoRows
Set objConn = Nothing
DBExecCmd = NoRows
End Function
'----------------------------------------------------------------------------
' Return parameter object from values.
'----------------------------------------------------------------------------
Function DBParm(pValue, pType, pSubType, pDirection)
Dim parm
set parm = Server.CreateObject("ADODB.Parameter") ' Create new parameter object
' Set parameter direction
Select Case pDirection
Case "RETURNVALUE"
parm.Direction = adParamReturnValue
Case "OUTPUT", "INPUTOUTPUT"
parm.Direction = adParamOutput
Case Else
parm.Direction = adParamInput
End Select
' set parameter to null if not set by caller
if isempty(pValue) or pvalue="" then
pValue = null
end if
' set parameter type
parm.Type = pType
' set value of parameter
select case pType
case adInteger, adDate, adGUID, adDBDate, adDBTimeStamp
parm.Value = pValue
case adVarChar
If VarType(pValue) = vbNull Or Len(pValue) = 0 Then
parm.Size = 1
Else
parm.Size = Len(pValue)
End If
parm.Value = pValue
case adDecimal
select case pSubType
case "ID"
parm.Precision = 11
parm.NumericScale = 0
parm.Value = pValue
case "AMOUNT"
parm.Precision = 28
parm.NumericScale = 4
parm.Value = pValue
case "YEAR"
parm.Precision = 4
parm.NumericScale = 0
parm.Value = pValue
end select
end select
' return parameter to caller
Set DBParm = parm
End Function
'----------------------------------------------------------------------------
'Connects to the database
Function connectDB()
Dim strConnect
Dim objDB
strConnect = "DSN=dbdsn;" &_
"UID=username;" & _
"PWD=password;" & _
"Database=dbname"
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open strConnect
Set connectDB = objDB
End Function
'----------------------------------------------------------------------------
home