Is this possible / how can it be done?

mind_grapes · #1 (**permalink**) 09-03-09, 06:21

Hi all, are you well? So a new day a new question, hope someone can help me with this.

I was wondering if it is possible to assign a variable the value of a stored procedure, for instance:

Dim X

X = sSQL = "exec spGetLevels "

I imagine this is a bad attempt at an exmaple, but hope you can understand what im trying to ask?

Also, if it is possible, how would I code it up?

Any help would be brilliant, thanks once again for everyones help so far, much obliged.

Kind regards
MG

gvee · #2 (**permalink**) 09-03-09, 06:48

Yes it is indeed possible!

Code:

CREATE PROCEDURE dbo.some_proc (
   @param1 int
 , @param2 char(1)
)
AS
  BEGIN
    SET NOCOUNT ON

      IF @param1 = 1 AND @param2 = 'a'
        BEGIN
          RETURN 1
        END
      ELSE
        BEGIN
          RETURN 0
        END

    SET NOCOUNT OFF
  END
GO

Code:

<%
Dim conn, cmd, returnvalue

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd  = Server.CreateObject("ADODB.Command")

conn.Open <connection string>

Set cmd.ActiveConnection = conn

  cmd.CommandType = adCmdStoredProc
  cmd.commandtext = "dbo.some_proc"
  cmd.Parameters(1) = 1
  cmd.Parameters(2) = "a"
  cmd.Execute
  
  returnvalue = cmd.parameters(0)

Set cmd = Nothing

conn.close

Set conn = Nothing
%>

mind_grapes · #3 (**permalink**) 09-03-09, 07:43

Hi gvee, thanks for the reply,

The code I have (in my ASP page) is a little different as all the connections are made in one file which is then called everytime at the top of the page, like this:

Code:

<%

'#############
'OpenDB 
'CheckUserRights
'CloseDB
'###########
 %>

Due to my lack of knowledge im not entirely sure what i need from your example and what i can get rid of, but i shall look into it.

I think, and could be wrong, that a lot of the code is for connection purposes?

I'm new so apologise if im asking silly questions

Kind regards
MG

gvee · #4 (**permalink**) 09-03-09, 08:16

Can you provide the code you currently use to execute sprocs and I will see what needs to be changed to get this to work for you

mind_grapes · #5 (**permalink**) 09-03-09, 08:52

Hi gvee,

that brill, i shall post some for you to look at, and the page structure so you have better understanding of how it all fits.

Will get it together and post ASAP.

Regards
MG

mind_grapes · #6 (**permalink**) 09-03-09, 09:49

Hello,

So the page structure (as set by my former manager) is:

'ASP Page structure
' Declare Variables
' Set Variables
' Reference Includes
' Call Functions
' Define Functions

The following is an ASP page that is reprsentative of the all pages. Things have been edited to save space.

Code:

<%@Language="VBScript" %>
<% 
Option Explicit

'#####   PageVariables  ######
Dim x ,sDebugMode, sPageAction 
Dim  iDept, iUser ,iStoreID, iZoneID

'### Form Field Values
Dim sOpensMonday ,sClosesMonday,sOpensTuesday,sClosesTuesday etc..

'calling procedures
PopulateFormValues
ProductForms
'######################
%>

<!--#include file="../../i/comms.asp"-->

(This is comms page, it has been copied and pasted from the comms.asp page)
<%

Dim conn, oRS, oDB, sSQL

Function OpenDB()
    conn = Application("CONN_STRING")
    Set oDB = Server.CreateObject("ADODB.Connection")
    oDB.Open(conn)
End Function

Function CloseDB()
    oDB.Close
    SET oDB = nothing
End Function
%>

<!--#include file="../../i/security.asp"--> (this is where I'd like my current query to go)
<!--#include file="../../i/header.asp"--> (mainly CSS stuff)

<%
'### THIS IS THE PAGE STUFF ####
OpenDB
displayPage
CloseDB
'#############
 %>

<%
Function checkValue (rawData)
    if isNull(rawData) Then rawData = 0
    if Len(Trim(rawData)) = 0 then rawData = 0
    if NOT IsNumeric(rawData) Then rawData = 0
    if rawData < 0 or rawData > 1000 then rawData = 0
    checkValue = rawData    
End function

Function displayPage()
    SELECT CASE sPageAction   'these case swtiches are all interlinked with the add page options AND they are requested upon hitting certain buttons.
       CASE "doAddStore"   
          AddNewStoreRow
          AddNewStoreDetailsRow
          DisplayDefaultPage
          
     ' these case swtiches are all interlinked with the updating of stores that are currently in the database.   
      CASE "update"   
            UpdateStoreRow
            UpdateStoreDetailsRow
            DisplayDefaultPage
    END SELECT

End Function

(currently working on this, the current query is to be used as user access levels)
Function DisplayDefaultPage()
    If sUserLevel="EDIT" THEN
       DisplayModifyPage
    Else
       DisplayReadOnlyPage
    End If
End Function

Function DisplayReadOnlyPage()
%>

<form id="Form1" action="/admin/stores/index.asp?1=1<%=sdebugMode %>" method="post">
<input type="hidden" value="search" name="pageaction" id="Hidden1" />
<fieldset id="Fieldset1"><legend>Page Actions</legend>SELECT STORE <select name="storeID" id="Select1">
    <option value="0" > - select a store - </option>

 This is an example of my Stored Procedure - this just retrieves the infomation
<%

sSQL = "exec spGetStores"
SET oRS = oDB.Execute(sSQL)
response.Write iStoreID & "-"
If Not oRS.EOF Then
    While Not oRS.EOF
        %><option value="<%=oRS("storeid")%>" <%
        If cint(checkValue(iStoreID)) = oRS("storeID") Then
            response.write " selected "
        End If
         %>><%=oRS("storeNumber") & " - " & oRS("storeName")%></option><%    
        oRS.MoveNext
    Wend
End If
 %></select> <input type="submit" value="GO!" onclick="document.forms[0].pageaction.value='search'" /> 
 </fieldset>
 <fieldset>
    
 This is an example of my Stored Procedure - used to populate certain form elements
<%
    'GET STORE DETAILS
    sSQL = "Exec spGetFullStoreDetails " & iStoreID
    SET oRS = oDB.Execute(sSQL)
    If Not oRS.EOF Then
     %>
    <legend>Store Details</legend>
    <h2>Contact Details</h2>
    <label for="storeNumber">Store Number : </label><%=oRS("storeNumber") %><br />
    <%=oRS("socialProfileAddress") %><br />
    <label for="postalAddress">Address : </label><%=oRS("postalAddress") %><br />
etc..

    <br />
    <h2>Store Fronts</h2>
    <!--opens  another window to allow for store window to be picked-->
    <script type="text/javascript">
    function openWinWindow()
    {
        var sURL = '/admin/stores/selectStoreFront.asp?w=' + document.forms[0].winID.value;
        
        var winWindow = window.open(sURL ,'mywin','menubar=no,resizable=no,width=600,height=300,scrollbars=1');
        winWindow.focus();
    }
    </script>
    <input type="hidden" id="Hidden2" name="winID" value="<%=oRS("windowID") %>" />
    <label for="winBut" >Window : </label><input type="button" onclick="openWinWindow();" value="SELECT WINDOW" name="winBut" id="Button1" />
    
    <%
    Else
        Response.write "<strong class=Error>NO STORE DETAILS FOUND</strong>"
    End If
    %>

End Function

Function DisplayModifyPage()
' all details within this form, allow you to modify 
%>

<form id="frmMain" action="/admin/stores/index.asp?1=1<%=sdebugMode %>" method="post">
<input type="hidden" value="search" name="pageaction" id="pageaction" />
<!-- NAV -->
    <fieldset id="subNav"><legend>Page Actions</legend>SELECT STORE <select name="storeID" id="storeID">
        <option value="0" > - select a store - </option>
    <%
    sSQL = "exec spGetStores"
    SET oRS = oDB.Execute(sSQL)
    response.Write iStoreID & "-"
    If Not oRS.EOF Then
        While Not oRS.EOF
            %><option value="<%=oRS("storeid")%>"  <%
            If cint(checkValue(iStoreID)) = oRS("storeID") Then
                response.write " selected "
            End If
             %>><%=oRS("storeNumber") & " - " & oRS("storeName")%></option><%    
            oRS.MoveNext
        Wend
    End If
     %></select> 
     <input type="submit" value="GO!" onclick="document.forms[0].pageaction.value='search'" /> 
     or 
     <input type="submit" value="ADD NEW STORE" onclick="document.forms[0].pageaction.value='AddNewStore';" />
     </fieldset>
   <br />        
   <input id="savebtn" type="submit" value="SAVE DETAILS" onclick="document.forms[0].pageaction.value='update';" />
 
    <%
    Else
        Response.write "<strong class=Error>NO STORE DETAILS FOUND</strong>"
    End If
    %>
</form>
<%
End Function

Function ShowAddStorePage()
%>
<form name="frmAddRetailer" action="/admin/stores/index.asp?1=1" method="post">
    <input type="hidden" value="doAddStore" name="pageaction" id="pageaction" />
    <fieldset><legend>Page Actions</legend>
        <input type="submit" value="go back" onclick="document.forms[0].pageaction.value='';" />
        <input type="reset" value="reset form" onclick="document.forms[0].pageaction.value='';" />
    </fieldset>

    <fieldset>
        <legend>Add Store</legend>
        <h2>Contact Details</h2>
        <label for="storeNumber" >Store Number : </label><input type="text" name="storeNumber" value="" size="5" maxlength="4" /><br />
        <label for="storeName" >Store Name : </label><input type="text" name="storeName" value="" size="30" maxlength="100" /><br />
 
 <h2>Window Details</h2>
 <br />

<input type="submit" value="SAVE DETAILS" onclick="document.forms[0].pageaction='doAddStore';" />
    </fieldset>                    <!--   SAVE BUTTON  -->
</form>
<%
End Function

Sub PopulateFormValues()
    iStoreNumber = request.Form("storeNumber")
    
    iWinID = request.Form("winID")
    if iWinID < "1" Then iWinID = "1"
    
    iFloorID = request.Form("floorID")
    if iFloorID< "1" Then iFloorID = "1"
    
End Sub

'This sub procedure adds the new store details to the database
Sub AddNewStoreRow()
    sSQL = "EXEC spAddStore " & iStoreNumber & ",'" & sStoreName & "'"
    SET oRS = oDB.execute(sSQL)
    iStoreID = oRS("storeID")
End Sub

Sub UpdateStoreRow()
    sSQL = "EXEC spUpdateStore " & iStoreID & "," & iStoreNumber & ",'" & sStoreName & "'"
    oDB.execute(sSQL)
End Sub
    
Sub AddNewStoreDetailsRow()
    sSQL = "EXEC spAddStoreDetails " & iStoreID & ",'" & sOpensMonday & "'" &_
            ",'" & sClosesMonday & "'" &_
            ",'" & sOpensTuesday & "'" &_     
            ",'" & iWinID & "'"&_
            ","& iFloorID
    oDB.execute(sSQL)
End Sub

Sub UpdateStoreDetailsRow()
    sSQL = "EXEC spUpdateStoreDetails " & iStoreID & ",'" & sOpensMonday & "'" &_
            ",'" & sClosesMonday & "'" &_
            "," & iWinID & ""&_
            ","& iFloorID
    oDB.execute(sSQL)
End Sub
 %>

The link to the database is mage in the global.asa file

Code:

<script language="vbscript" runat="server">

sub Application_OnStart

  Application("CONN_STRING") = "Provider=sqloledb;server=XXXX;database=XXXX;uid=XXXX;pwd=XXXX;"
  Application("DEFAULT_URL") = "http://localhost"
  Application("CURRENT_USERS") = 0
  Application("ADMIN_MESSAGE") = 0
  SET Application("oDBConn") = Server.CreateObject("ADODB.Connection")
  Application("oDBConn").open(Application("CONN_STRING"))
end sub

sub Application_OnEnd

end sub

sub Session_OnStart
  Session("TEST_VARIABLE") = ""
  Session("DEBUG_MODE_ON") = False
  Session("LOGGED_ON_USER_ARRAY") = Split(Request.ServerVariables("LOGON_USER"), "\")
  
  SET Session("LOGGED_ON_USER_ARRAY") = Application("oDBConn").Execute("EXEC spGetUserDetails '" & Right(Request.ServerVariables("LOGON_USER"), LEN(Request.ServerVariables("LOGON_USER")) - INSTR(Request.ServerVariables("LOGON_USER"),"\")) & "'")
  
  Application("CURRENT_USERS") = Application("CURRENT_USERS") + 1
end sub

sub Session_OnEnd
  Application("CURRENT_USERS") = Application("CURRENT_USERS") - 1
end sub
</script>

I think this is probably everything you need. If there is something else please say.

Regards
MG

gvee · #7 (**permalink**) 09-03-09, 10:22

Ugh, I hate spaghetti code...

Here you go:

Code:

<%
Function hereYaGo(param1, param2)

  Dim cmd, returnvalue

  Set cmd  = Server.CreateObject("ADODB.Command")

  Set cmd.ActiveConnection = oDB

    cmd.CommandType = adCmdStoredProc
    cmd.commandtext = "dbo.some_proc"
    cmd.Parameters(1) = param1
    cmd.Parameters(2) = param2
    cmd.Execute
    
    hereYaGo = cmd.parameters(0)

  Set cmd = Nothing
  
End Function
%>

Code:

<%
Dim sprocReturnValue

sprocReturnValue = hereYaGo(1, "a")

Response.Write (sprocReturnValue)
%>

gvee · #8 (**permalink**) 09-03-09, 10:27

Alternatively:

Code:

CREATE PROCEDURE dbo.some_proc (
   @param1 int
 , @param2 char(1)
)
AS
  BEGIN
    SET NOCOUNT ON

      IF @param1 = 1 AND @param2 = 'a'
        BEGIN
          SELECT 1 As returnvalue
        END
      ELSE
        BEGIN
          SELECT 0 As returnvalue
        END

    SET NOCOUNT OFF
  END
GO

Then you can access it this way:

Code:

<%
Dim sprocReturnValue

sSQL = "exec dbo.some_proc " & param1 & ", " & param2
SET oRS = oDB.Execute(sSQL)

If Not oRS.EOF Then
  sprocReturnValue = oRS("returnvalue")
End If
%>

This is more in line with your current structure but is susceptible to SQL injection because of the lack of parameterisation (green highlighted)

mind_grapes · #9 (**permalink**) 09-03-09, 10:48

Hey gvee,

thanks for this, I shall give it a try.

One question though, whats spaghetti code?

Regards
MG

gvee · #10 (**permalink**) 09-03-09, 11:22

Spaghetti code - Wikipedia, the free encyclopedia

mind_grapes · #11 (**permalink**) 09-03-09, 11:25

Hi gvee,

I have tried to implement what you gave, but get an error. I tried the second one. The error i get is:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required
/i/security.asp, line 30

line 30 is: SET oRS = oDB.Execute(sSQL)

I have implemented it like this:

Code:

<%

Dim sUserName, sprocReturnValue, param1 ,param2
Dim userNameTest

'username tests
sUserLevel = "EDIT" 
userNameTest = Mid(request.ServerVariables("AUTH_USER"), InStr(request.ServerVariables("AUTH_USER"), "\")+1)
response.write(userNameTest & " ")
%>

<%

'#############
OpenDB
'CheckUserRights
CloseDB
'###########
 %>
 
<%
sSQL = "exec dbo.some_proc " & param1 & ", " & param2
SET oRS = oDB.Execute(sSQL)

If Not oRS.EOF Then
  sprocReturnValue = oRS("returnvalue")
End If
%>

<%

If Request.QueryString("debugMode") ="on" Then 
    Session("DEBUG_MODE_ON") = True
Else
    Session("DEBUG_MODE_ON") = False
End If

Function displayDebugInfo()

    If Session("DEBUG_MODE_ON") AND lcase(Session("LOGGED_ON_USER_ARRAY")("FolderName")) = "bussys" Then
        Response.Write "<hr/>"
        For each x in request.QueryString()
            response.Write x & " - " & request.QueryString(x) & "<br/>"
        next
        Response.Write "<hr/>"
        For each x in request.form()
            response.Write x & " - " & request.form(x) & "<br/>"
        next
        
        Response.Write "<hr/>"
        For each x in request.ServerVariables()
            response.Write x & " - " & request.ServerVariables(x) & "<br/>"
        next
        
        Response.Write "<hr/>"
        Response.Write Application("DEFAULT_URL") & "<br/>"
        Response.Write Application("CONN_STRING") & "<br/>"
        Response.Write "CURRENT SITE USERS : " & Application("CURRENT_USERS") & "<br/>"
        Response.Write "SESSION TEST VARIABLE: " & Session("TEST_VARIABLE") & "<br/>"

        For x = 0 to Session("LOGGED_ON_USER_ARRAY").Fields.Count -1
            response.Write Session("LOGGED_ON_USER_ARRAY")(x).name & " - " & Session("LOGGED_ON_USER_ARRAY")(x) & "<br/>"
        next

    End If
End Function

Function CheckUserRights()  
 
%>

<%
response.write"hello"    
%>
 
<%
End Function
%>

I thought at first it was to do with the connection, but clearly i was wrong. Any help would be great.

Thanks and regards
MG

mind_grapes · #12 (**permalink**) 09-03-09, 11:29

haha, i can relate to the line that says:

"Spaghetti code can be caused by several factors, including inexperienced programmers"

Im new and very inexperienced. But im learning i suppose.

Regards
MG

Teddy · #13 (**permalink**) 09-03-09, 12:09

If you're only after a scalar retun value, I would suggest using an output parameter instead.

You can read about output parameters as pertains to stored procedures in BOL.

To consume them via ADODB, do something like this:

Use Stored Procedures with Output Parameters

You could also specify a return value in the sproc and do it that way, though I prefer to avoid overriding return values in general as I'd rather give the DB a chance to tell me something went wrong. If you want to explore that direction anyway, again RETURN can be found in BOL and you can use a very similar approach to the output parameter deal, except specify that the parameter is a return param instead of an input/output param.

gvee · #14 (**permalink**) 09-03-09, 12:35

OUTPUT parameters! How did I not think of that?

MG, where does oRS get declared and instantiated (SET oRS = ...)?

mind_grapes · #15 (**permalink**) 09-03-09, 13:09

Hi gvee,

Well at least you forgot about them, i dont even know about them - i still have so much to learn arrgghhh!! :-( lol.

This is whats in comms.asp

Code:

<%
Dim conn, oRS, oDB, sSQL

Function OpenDB()
    conn = Application("CONN_STRING")
    Set oDB = Server.CreateObject("ADODB.Connection")
    oDB.Open(conn)
End Function

Function CloseDB()
    oDB.Close
    SET oDB = nothing
End Function
%>

And this is the only time it appears when delcaring it. When its used in other asp pages, its always as one of the following:

Code:

SET oRS = oDB.Execute(sSQL)

OR

<%=oRS("storeid")%>

This is how my manager set everything up before he left.

Hope that helps with what you were after?

Regards
Mg

Internet Services

Web Development

Digital Marketing

Consumer Tech