Link to home
Start Free TrialLog in
Avatar of Baub Eis
Baub EisFlag 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
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Hi,

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

Giannis
You cannot use parameters for the TOP clause.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.