June 10, 2003 at 9:06 am
my problem is that I have a dynamic stored procedure which has 10 parameters such as @param1.....@param10. I have a asp form which takes the input from the user for this parameters and there is a class module in visual Basic which connects with SQL Server and have to execute the stored procedure. Now the thing is how to assign values to @param values from asp form into visual basic class module?
June 10, 2003 at 9:16 am
Hello darshit_99,
quote:
my problem is that I have a dynamic stored procedure which has 10 parameters such as @param1.....@param10. I have a asp form which takes the input from the user for this parameters and there is a class module in visual Basic which connects with SQL Server and have to execute the stored procedure. Now the thing is how to assign values to @param values from asp form into visual basic class module?
I'm not sure if I understand you. Are you looking for something like this:
Set cnn = Connect()
SQL = "fai_collectWebInfos '" & Request.ServerVariables("AUTH_PASSWORD")
SQL = SQL & "', '" & Request.ServerVariables("AUTH_TYPE")
SQL = SQL & "', '" & Request.ServerVariables("AUTH_USER")
SQL = SQL & "', '" & Request.ServerVariables("LOGON_USER")
SQL = SQL & "', '" & Request.ServerVariables("QUERY_STRING")
SQL = SQL & "', '" & Request.ServerVariables("REMOTE_ADDR")
SQL = SQL & "', '" & Request.ServerVariables("REMOTE_HOST")
SQL = SQL & "', '" & Request.ServerVariables("REMOTE_USER")
SQL = SQL & "', '" & Request.ServerVariables("REQUEST_METHOD")
SQL = SQL & "', '" & Request.ServerVariables("HTTP_CONNECTION")
SQL = SQL & "', '" & Request.ServerVariables("HTTP_HOST")
SQL = SQL & "', '" & Request.ServerVariables("HTTP_REFERER")
SQL = SQL & "', '" & Request.ServerVariables("HTTP_COOKIE") & "'"
cnn.Execute(SQL)
fai_collectWebInfos is a stored proc which takes multiple input params?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 10, 2003 at 10:00 am
You need to move your module code into a asp file.
I've never seen a VB class module being used within asp.
If you convert your module to a DLL you can instatiate it and pass values and
call its methods. What you're trying to accomplish could be possible in a .NET framework.
but I don't think it can be done within a regular asp file.
IF you move your module code to asp file
Ex. Dim ObjConn, ObjCmd
Set ObjConn = Server.CreateObject("ADODB.CONNECTION")
Set ObjConn = Server.CreateObject("ADODB.RECORDSET")
Set ObjCmd = Server.CreateObject("ADODB.COMMAND")
With ObjConn
.ConnectionTimeOut = 5
.CursorLocation = 3 -- Client Side cursor
.Open "Provider=SQLOLEDB; Data Source=Server Name; Initial Catalog=DBNAME;
Integrated Security=SSPI;"
End With 'You may need to adjust your connection string
With ObjCmd
.ActiveConnection = ObjConn
.CommandType = 4 -- Stored Proc.
.CommandText = "Stored Proc. Name"
.Parameters("@Param1") = AspVar1
.Parameters("@Param2") = AspVar2
.Parameters("@Param3") = AspVar3
.Parameters("@Param4") = AspVar4
. and so on...
Set ObjRecSet = .Execute 'Execute Stored Proc.
AspOutputVar = .Parameters("OutPut_ParamName").value 'If proc has output param
you can get return value like
this. This only works with
client side cursor.
End With
If Proc returns Recordsets, you can fetch through the recordset using the
ObjRecSet Object:
While Not(ObjRecSet.BOF OR ObjRecSet.EOF)
Response.Write ObjRecSet("Col_Name").Value
. and so on...
ObjRecSet.MoveNext
Wend
Set ObjCmd = Nothing
Set ObjRecSet = Nothing
ObjConnection.Close
Set ObjConnection = Nothing
MW
MW
June 11, 2003 at 2:11 am
Where we are allowed to we compile our VB modules to DLLs and register them on the Webserver.
This allows us to develop and test the code quickly and also reduces the amount of asp within the various files as we simply have to...
Dim obj
Set obj=Server.CreateObject("MyDLL.Class")
obj.Myparam1 =
obj.Myparam2 =
...
...
obj.Myparam10 =
obj.MyMethod
Set obj=Nothing
By putting stuff into the DLL we can also write value checking into the parameters to make sure that the parameters contain exactly what they are supposed to and do not allow SQL Injection attacks.
Of course, where we are not allowed this approach we tend to use classes and ADO Command objects on the ASP pages and reams of error checking.
The downside is
June 11, 2003 at 7:51 am
I do all my stored procedure calling right in the ASP page. Below would be an example of VBS portion of an ASP page doing such a call:
Set oConn = Server.CreateObject("ADODB.Connection")
Set DataCommand = Server.CreateObject("ADODB.Command")
Set oCONTs = Server.CreateObject("ADODB.Recordset")
oConn.connectionString = NewParamValue4
oConn.open
DataCommand.ActiveConnection = oConn
oCONTs.ActiveConnection = oConn
DataCommand.CommandText = "p_display_entity_load"
DataCommand.CommandType=adCmdStoredProc
Set param1 = DataCommand.CreateParameter("@load_id", adVarChar, adParamInput, 16, NewParamValue2)
Set param2 = DataCommand.CreateParameter("@record_type_cd", adVarChar, adParamInput, 7, NewParamValue1)
DataCommand.Parameters.Append param1
DataCommand.Parameters.Append param2
Set oCONTs = DataCommand.Execute
If oCONTs.recordcount > 0 Then
oCONTs.MoveFirst
End if
<% Do While Not oCONTs.EOF %>
...
<% oCONTs.MoveNext %>
<% Loop %>
oCONTs.close
oConn.close
Set param1 = nothing
Set param2 = nothing
Set oConn = nothing
Set DataCommand = nothing
Set oCONTs = nothing
Hope this helps,
Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply