I'd would use a stand alone command object, you need to write a stored procedure to retrive the data, it needs to contain output parameters to pass values back the command object.
Sub Main()
'Dimension the command object
Dim Cm
'Setup the command passing the name of your sp to the GetCm function (see below)
Set Cm = GetCm("<sp_Name>")
'Set the parameters of the command (see function below)
SetCommandParameter Cm, "<Param Name>", <Param Value>
'Execute the command
Cm.Execute
'Read the output parameters into your ASP variable (see function below)
<Asp Variable> = ReadCommandParameter(Cm, "<Param Name>")
End Sub
Function GetCm(spName)
Dim Cm
Dim Cn
Set Cn = GetConnection()
If Not IsEmpty(Cn) Then
Set Cm = Server.CreateObject("ADODB.Command")
With Cm
.CommandType = 4
.CommandText = spName
.ActiveConnection = Cn
End With
End If
Set GetCm = Cm
End Function
Sub SetCommandParameter(Cm, ParameterName, ParameterValue)
On Error Resume Next
Cm.Parameters("@" & ParameterName).Value = ParameterValue
If Err.Number <> 0 Then
Cm.Parameters(ParameterName).Value = ParameterValue
End If
End Sub
Function ReadCommandParameter(Cm, ParameterName)
Err.Clear
On error resume next
ReadCommandParameter = Cm.Parameters("@" & ParameterName).Value
If Err.Count = 0 Then
Exit Function
End If
Err = 0
ReadCommandParameter = Cm.Parameters(ParameterName).Value
End Function
'The get connection function picks up the connection string from the Application collection, the connection string is set in the Global.asa ApplicationOnstart event.
'Example of Application OnStart() event
Sub ApplicationOnStart()
Application("ConnectString") = "Provider=SQLOLEDB;DRIVER=SQLs erver;SERVER=<ServerName>;uid=<SQLLogin>;pwd=<Password>;DATABASE=<DBName>"
End Sub
Function GetConnection()
Dim Cn
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.ConnectionString = Application("ConnectString")
Cn.Open
Set GetConnection = Cn
End Function
Enter your message below
Sign in or Join us (it's free).