Solved

SQL 2012 PROC & Classic ASP

Posted on 2014-11-28
9
283 Views
Last Modified: 2014-11-28
I'm trying use a Stored Procedure on a Classic ASP page in Dreamweaver. I keep getting the following error.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
mypage.asp, line 25 (set rsST3 = cmdsST3.Execute)


I did some research and I'm not sure if this is the known bug in my case:
http://support.microsoft.com/kb/812916

They recommended:
To work around this bug, use early binding instead of late binding to run the SQL query. You can use the Excecute method of the ADODB.Command object to run the SQL query, as shown in the following code:
myRecordset = myCommand.Execute(, New Object() {myParameter})

If so, not sure how to implement it on my end.
set rsST3 = cmdsST3.Execute(, New Object() {@AGE},{@INS})

Any help appreciated.

MyPROC:
ALTER PROCEDURE [myPROC]

@AGE varchar(50),
@INS varchar(50)

As

SELECT

AgeGroup AS iAge,
InSeam AS iSeam

FROM myTable AS refDetails
WHERE (',' + RTRIM(AgeGroup) + ',') LIKE '%,%' + @AGE + ',%' AND (',' + RTRIM(InSeam) + ',') LIKE '%,%' + @INS + ',%' AND (MAKE_PUBLIC = 'yes' OR MAKE_PUBLIC = 'out')
ORDER BY BPRICE ASC

Open in new window


My Page:
<%
Dim cmdsST3__AGE
cmdsST3__AGE = "2"
if(Request("AGE") <> "") then cmdsST3__AGE = Request("AGE")

Dim cmdsST3__INS
cmdsST3__INS = "15"
if(Request("INS") <> "") then cmdsST3__INS = Request("INS")
%>
<%
set cmdsST3 = Server.CreateObject("ADODB.Command")
cmdsST3.ActiveConnection = My_STRING
cmdsST3.CommandText = "myPROC"
cmdsST3.CommandType = 4
cmdsST3.CommandTimeout = 0
cmdsST3.Prepared = true
cmdsST3.Parameters.Append cmdsST3.CreateParameter("@RETURN_VALUE", 3, 4)
cmdsST3.Parameters.Append cmdsST3.CreateParameter("@AGE", 200, 1,50,cmdsST3__AGE)
cmdsST3.Parameters.Append cmdsST3.CreateParameter("@INS", 200, 1,50,cmdsST3__INS)
set rsST3 = cmdsST3.Execute
rsST3_numRows = 0
%>

<% if rsST3.eof or rsST3.bof then %>
<p>Not found.</p>

<% else %> 

<%
While Not rsST3.EOF

strAGE = rsST3("iAge")
strINS = rsST3("iSeam")
%>

<div>
  AGE: <%=strAGE%><br />
 INSEAM: <%=strINS%><br />
</div>

<%
rsST3.MoveNext()
Wend
%>

<% end if %>

Open in new window

0
Comment
Question by:swaggerking
9 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40470922
comment out 14-15-16-17 and test it... will it run?

also, are you opening your db somewhere? maybe it is closed?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40470923
like this

set conn=Server.CreateObject("ADODB.Connection")
conn.Open My_STRING

set cmdsST3 = Server.CreateObject("ADODB.Command")
cmdsST3.ActiveConnection = conn

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40470948
Check the connection strings on this page: http://www.connectionstrings.com/sql-server/  You may need SQL Native Client 11 to connect to SQL Server 2012.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40470972
comment out line 17, you don't need to specify a return value, if anything is returned, it'll be in the recordset for you
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:swaggerking
ID: 40470974
@HainKurt

comment out 14-15-16-17 and test it... will it run?
I get a new error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near 'INSEAM'.
/balance-bikes/asp/search.asp, line 20 (set rsST3 = cmdsST3.Execute)


also, are you opening your db somewhere? maybe it is closed?
DB is open and running. I can and I'm currently running other PROCS that are live.

When I try this it gives me my original error.
set conn=Server.CreateObject("ADODB.Connection")
conn.Open My_STRING

set cmdsST3 = Server.CreateObject("ADODB.Command")
cmdsST3.ActiveConnection = conn

Open in new window


This PROC is a little more complex than what I usually run. I've added two additional fields in this table. AgeGroup and InSeam they both contain values separated by commas

Ie:
Product A:
AgeGroup: 2,3,4,5
InSeam:14,14.5,15,15.5,16,16.5

Product B:
AgeGroup: 3,4,5,6
InSeam:15,15.5,16,16.5,17

Each of my products belong to various age groups and inseam restrictions that may overlap from product to product. I want to allow my customers to search and find the product(s) that match their age and inseam.
0
 

Author Comment

by:swaggerking
ID: 40470978
@Big Monty
Commenting out 17 gives me the same error.
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40471192
comment out 14-15-16-17 and test it... will it run?
I get a new error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near 'INSEAM'.

good... now it is connecting to db, running it, and you get error from database, meaning your query is wrong...

now delete line 9 &12 in your sp (delete empty lines)...

and try again
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40471194
This PROC is a little more complex than what I usually run

:) it is simplest proc i ever seen... just one select... nothing else...
0
 

Author Closing Comment

by:swaggerking
ID: 40471278
@HainKurt
It's the simple things that gets us every time. I appreciate your help and sense of humor.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now