SQL 2012 PROC & Classic ASP
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:
My Page:
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 ASCMy 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 %>
like this
set conn=Server.CreateObject("ADODB.Connection")
conn.Open My_STRING
set cmdsST3 = Server.CreateObject("ADODB.Command")
cmdsST3.ActiveConnection = conn
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.
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
ASKER
@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.
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.
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,
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.
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.
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 = connThis 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,
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.
ASKER
@Big Monty
Commenting out 17 gives me the same error.
Commenting out 17 gives me the same error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This PROC is a little more complex than what I usually run
:) it is simplest proc i ever seen... just one select... nothing else...
ASKER
@HainKurt
It's the simple things that gets us every time. I appreciate your help and sense of humor.
It's the simple things that gets us every time. I appreciate your help and sense of humor.
also, are you opening your db somewhere? maybe it is closed?