troubleshooting Question

Problem with paramertized SQL statement in Dreamweaver cs6

Avatar of Baub Eis
Baub EisFlag for United States of America asked on
Microsoft SQL ServerASPAdobe Dreamweaver
4 Comments1 Solution657 ViewsLast Modified:
I have a parameterized SQL statement I am using in Dreamweaver.  When I test the record set in dreamweaver it produces the correct outcome.  but when I load to website it gives me a

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

/state_email_send.asp, line 29

Here is the code from dreamweaver:

<%
Dim rsState__MM_ColParam
rsState__MM_ColParam = "10"
If (Request.QueryString("numberofemails")    <> "") Then 
  rsState__MM_ColParam = Request.QueryString("numberofemails")   
End If
%>
<%
Dim rsState__MM_ColParam1
rsState__MM_ColParam1 = "Il"
If (Request.QueryString("state")   <> "") Then 
  rsState__MM_ColParam1 = Request.QueryString("state")  
End If
%>
<%
Dim rsState
Dim rsState_cmd
Dim rsState_numRows

Set rsState_cmd = Server.CreateObject ("ADODB.Command")
rsState_cmd.ActiveConnection = MM_durgorama_STRING
rsState_cmd.CommandText = "SELECT TOP ? * FROM dbo.state_emails WHERE datesent IS NULL AND state = ?" 
rsState_cmd.Prepared = true
rsState_cmd.Parameters.Append rsState_cmd.CreateParameter("param1", 5, 1, -1, rsState__MM_ColParam) ' adDouble
rsState_cmd.Parameters.Append rsState_cmd.CreateParameter("param2", 200, 1, 255, rsState__MM_ColParam1) ' adVarChar

Set rsState = rsState_cmd.Execute
rsState_numRows = 0
%>

Its got to be something simple.  I am just trying to set the number of records that I want to pull from the TOP statement.  If I remove the parameters and just use standard SQL with request variables it works....

thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros