Avatar of Baub Eis
Baub Eis
Flag for United States of America asked on

Problem with paramertized SQL statement in Dreamweaver cs6

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
%>

Open in new window


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
Microsoft SQL ServerASPAdobe Dreamweaver

Avatar of undefined
Last Comment
Baub Eis

8/22/2022 - Mon
Ioannis Paraskevopoulos

Hi,

Could it be because you are declaring it as double and not integer?

Giannis
Anthony Perkins

You cannot use parameters for the TOP clause.
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Baub Eis

ASKER
thanks acperkins, I was able to make it work doing the same thing you were.  Just didn't know that parameter wasn't allowed in the TOP section.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23