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