Results 1 to 5 of 5

Thread: Trying to update ASP/VBScript code

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    3

    Trying to update ASP/VBScript code

    Hello,

    Using IIS 7.5 with SQL Server 2008. Have several older ASP Classic pages that have come to me for maintenance, and I'm finding several that don't have parameterized queries that really need to be re-written to tighten security. I'd also like to add an additional parameter to tighten the query.

    My problem is that I'm finding that it doesn't work (even before adding the extra parameter).
    The stored procedure currently has one parameter. Scripting stored procedure to CREATE TO gives:

    Code:
    CREATE PROCEDURE [dbo].[sp_claimpayments] @claim_id int = NULL AS
    select co.claim_id, co.clm_pmt from claim_objects co
    where co.claim_id = @claim_id
    Stored procedure runs fine if I build a SQL string and just call it using:

    Code:
       <% 
        Set objConnection = Server.CreateObject("ADODB.Connection")  
        objConnection.Open "DSN=MyDSN"
        SQLQuery = "exec sp_Claimpayments " & Request.querystring("claimid")
        Set rsCustomersList = objConnection.Execute(SQLQuery)
        %>
    But I'm trying to parameterize the claimid here.

    When I run it on the server, it crashes giving a 500 error. So, to debug it, I've transported it to VBA in Access (where I do the bulk of my report-writing anyway). The code looks like this:

    Code:
    Dim strCnxn As String
        strCnxn = "DSN=myLocalDSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=relocation;"
        'strCnxn = "DSN=myDSN"
        
        Dim objConnection
        Set objConnection = CreateObject("ADODB.Connection")
        
        With objConnection
            .Mode = adModeRead 'Ideally read-only
            .Open strCnxn
        End With
        
        'Assemble variables that will eventually be used in ASP page
        Dim strAdj As String
        Dim strLoss As String
        
        'Put together command and recordset objects
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        
        Dim rsPaymentsList
        Set rsPaymentsList = CreateObject("ADODB.Recordset")
        
        
        Dim clm As Integer
        Dim compType As String
        Dim e As Integer
                
        With cmd
            '.CommandText = "sp_ClaimpaymentsDev"
    		.CommandText = "sp_Claimpayments"
            .CommandType = adCmdStoredProc
            .ActiveConnection = objConnection
            '.Parameters.Clear
            .Parameters.Refresh
            ' sp_ClaimpaymentsDev takes two input parameters, an integer and a single character
            ' It returns integer
            'First param:
            .Parameters.Append .CreateParameter("@claim_id", adInteger, adParamInput, , clm)
            'Second param (not used yet):
            '.Parameters.Append .CreateParameter("@cust_type", adChar, adParamInput, 1, compType)
            'Output param (is this necessary?):
            .Parameters.Append .CreateParameter("@ErrorLevel", adInteger, adParamOutput)
        End With
        e = ErrorLevel
        
        ' This next line has the problem:
        rsPaymentsList.Open cmd, , adOpenForwardOnly, adLockReadOnly
    	
        If Err.Number > 0 Then
           Debug.Print (Err.Description)
           Exit Sub
        End If
        
        Do Until rsPaymentsList.EOF
            'Handle recordset here:
            Debug.Print ("------ END of RECORD --------")
            
            rsPaymentsList.MoveNext
        Loop
        rsPaymentsList.Close
        Set rsPaymentsList = Nothing
        Set objConnection = Nothing
        Set cmd = Nothing
    When I run this, I get
    Run-time erro '-2147217900 (80040e14)':
    Procedure or function sp_claimpayments has too many arguments specified.

    Well, the sp_claimpayments as it currently appears on the SQL server takes one input param and returns one output param. If I run it from the server, it prompts for one input. My code above (not counting the commented-out line) appends two params to the command object, one input and one output. But I've tried getting rid of the output to make sure there's only one. I just can't figure out why it thinks there are too many arguments. In fact, the only time it does not return the error is when I append no parameters at all. But then I don't get a recordset (or an empty one).

  2. #2

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    3

    Re: Trying to update ASP/VBScript code

    I ran a trace and watched the query hit the SQL Server. For the version in ASP, it doesn't even seem to be hitting the SQL Server, so I may not be getting very far at all. The problem is clearly it the VBScript as if I cut that chunk out, it runs fine. When it's in there, it gets 500 error.

    Code:
    <!DOCTYPE html>
    <%@ LANGUAGE = "VBScript" %>
    
    <html lang="en">
    
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta NAME="ROBOTS" CONTENT="NOINDEX">
        <title>Details</title>
    </head>
    <body>
    
        <h3 id="payments">Payments</h3>
        <%Dim strCnxn As String
        strCnxn = "DSN=myDSN"
        Dim objConnection
        Set objConnection = Server.CreateObject("ADODB.Connection")
        
        With objConnection
            .Mode = adModeRead 'Ideally read-only
            .Open strCnxn
        End With
        
        'Assemble variables that will eventually be used in ASP page
        Dim strAdj As String
        Dim strLoss As String
        
        'Put together command and recordset objects
        Dim cmd
        Set cmd = Server.CreateObject("ADODB.Command")
        
        Dim rsPaymentsList
        Set rsPaymentsList = Server.CreateObject("ADODB.Recordset")
        
        Dim clm As Integer
        Dim compType As String
        Dim e As Integer
                
        With cmd
            .CommandText = "sp_Claimpayments"
            .CommandType = adCmdStoredProc
            .ActiveConnection = objConnection
            '.Parameters.Clear
            .Parameters.Refresh
    		'
            ' sp_Claimpayments takes two parameters, an integer and a single character
            .Parameters.Append .CreateParameter("@claim_id", adInteger, adParamInput, , clm)
    		' Next line not used yet.  Plan to add additional parameters later.
            '.Parameters.Append .CreateParameter("@cust_type", adChar, adParamInput, 1, compType)
            .Parameters.Append .CreateParameter("@el", adInteger, adParamOutput)
        End With
        e = el
       
            ' This next line had the problem:
        rsPaymentsList.Open cmd, , adOpenForwardOnly, adLockReadOnly
       
        Do Until rsPaymentsList.EOF
            %><p>Line Data will go here</p><%
    rsPaymentsList.MoveNext
        Loop
        rsPaymentsList.Close
        Set rsPaymentsList = Nothing
        Set objConnection = Nothing
        Set cmd = Nothing
        %>
    
    </body>
    </html>
    For the VBA version, it is totally changing the way things run:

    The trace:
    Code:
    declare @p1 int
    set @p1=180150003
    declare @p3 int
    set @p3=8
    declare @p4 int
    set @p4=1
    declare @p5 int
    set @p5=2
    exec sp_ddopen @p1 output,N'sp_sproc_columns',@p3 output,@p4 output,@p5 output,N'sp[_]Claimpayments',NULL,NULL,NULL
    select @p1, @p3, @p4, @p5
    go
    exec sp_cursorfetch 180150003,2,1,1
    go
    exec sp_cursorfetch 180150003,2,1,1
    go
    exec sp_cursorfetch 180150003,2,1,1
    go
    exec sp_cursorclose 180150003
    go
    declare @p3 int
    set @p3=NULL
    exec sp_Claimpayments default,0,@p3 output
    select @p3
    go

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    3

    Re: Trying to update ASP/VBScript code

    Quote Originally Posted by CarlS2018 View Post
    I ran a trace and watched the query hit the SQL Server. For the version in ASP, it doesn't even seem to be hitting the SQL Server, so I may not be getting very far at all.
    OK, I decided to approach this from the ASP side again. I went line-by-line and it seems the problem there stems from assigning anything to the properties of the command object.

    as soon as I uncomment any or all of the cmd.property lines below I get a 500 error.

    Code:
    <!DOCTYPE html>
    <%@ LANGUAGE = "VBScript" %>
    <!--#include file="redirect.asp"-->
    <!-- Define Scripting Language -->
    <html lang="en">
    
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta NAME="ROBOTS" CONTENT="NOINDEX">
        <title>Details</title>
        <link rel="stylesheet" href="css/local.css" />
    </head>
    <body>
        <div class="container-fluid">
        <h3 id="payments">Payments</h3>
        <% 
    	Dim strCnxn 
    	strCnxn = "DSN=myDSN"
    	Dim objConnection
        Set objConnection = Server.CreateObject("ADODB.Connection")  
    	objConnection.Mode = acModeRead
    	objConnection.Open strCnxn
    	
    	Dim cmd
    	cmd = Server.CreateObject("ADODB.Command")
    
    	Dim rsPmts 
    	
        Set rsPmts = CreateObject("ADODB.Recordset")
    	'Set cmd.ActiveConnection = objConnection
    	'cmd.CommandType = adCmdStoredProc
    	'cmd.CommandType = 4
    	'cmd.CommandText = "spNewPayments"
    	'cmd.ActiveConnection = objConnection
    	'Set cmd.ActiveConnection = objConnection
    	'cmd.Parameters.Refresh
    	'cmd.Parameters.Append .CreateParameter("@claim_id", adInteger, adParamInput, , 162611)
    	
    	'rsPmts.Open cmd, , adOpenForwardOnly, adLockReadOnly
        %>
    
        
    	 <% 
    	 %>
            </tbody>
        </table>
    
    </body>
    </html>

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Trying to update ASP/VBScript code

    Code:
    Dim cmd
    Set cmd = Server.CreateObject("ADODB.Command")
    HTH

    Olaf

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Trying to update ASP/VBScript code

    Your problem is the .Parameters.Refresh followed by .Parameters.Append ... When you do a .Parameters.Refresh, it actually goes out to the database and queries the sproc to get the parameters... so it already them knows the parameters... you then append a parameter... so now your parameters collection contains TWO... not one as you're expecting. Do one or the other, but not both. .Parameters.Refresh is handy but it does take a performance hit because it does involve a performance hit since it involves a trip to the database. Personally I prefer the code to be explicit in the parameters and set them manually then if something changes, I know about it, and can make additional changes as needed. using .Refresh could potentially hide problems down the road.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width