Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: Is this possible / how can it be done?

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    %>
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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
    Last edited by gvee; 09-03-09 at 09:57. Reason: removed sensitive connection string stuff

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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)
    %>
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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)
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hey gvee,

    thanks for this, I shall give it a try.

    One question though, whats spaghetti code?

    Regards
    MG

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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

  12. #12
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    Last edited by Teddy; 09-03-09 at 12:16.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    OUTPUT parameters! How did I not think of that?

    MG, where does oRS get declared and instantiated (SET oRS = ...)?
    George
    Home | Blog

  15. #15
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    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
    Last edited by mind_grapes; 09-03-09 at 13:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •