-
Sep 11th, 2018, 02:25 PM
#1
Thread Starter
New Member
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).
-
Sep 12th, 2018, 11:54 AM
#2
Thread Starter
New Member
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
-
Sep 14th, 2018, 01:19 PM
#3
Thread Starter
New Member
Re: Trying to update ASP/VBScript code
Originally Posted by CarlS2018
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>
-
Sep 18th, 2018, 11:32 PM
#4
Re: Trying to update ASP/VBScript code
Code:
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
HTH
Olaf
-
Sep 19th, 2018, 07:02 AM
#5
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|