Anyone familiar with "Clubuttic" ADO parameters?



  • I'm trying to fix up some nasty inline SQL in a Classic ASP app (we're unable to port it right now) by using parameterized queries.  The database back-end is SQL Server 2005. My code looks lke the following (anonymized):

    I am prompting the user for a start/end date and validating that prior to creating the connection and command

    "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate BETWEEN ? AND ?"

    My ADODB.Command object looks like the following:

    objConnection.Open
         
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandText = strSql
    objCommand.Parameters.Append(objCommand.CreateParameter("@StartDate", adDBTimestamp, adParamInput, 0, strBeginDate))
    objCommand.Parameters.Append(objCommand.CreateParameter("@EndDate", adDBTimestamp, adParamInput, 0, strEndDate))
     
    Set objRS = objCommand.Execute

    The problem is that it's returning no rows - I've verified that rows exist for the test date ranges I'm trying out.  I had the page output the SQL and the problem seems to be that it's not substituting the parameters for the "?" in the query, i.e. the outputted code reads "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate BETWEEN ? AND ?" instead of "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate BETWEEN '9/19/2008' and '9/22/2008'

    Anyone know what's causing this?  Every example I've seen of using parameterized queries do it exactly like I have done here, but it doesn't seem to be working.



  • If you are using OLEDB and accessing a SQL Server database, use the actual parameter names instead of "?" in your commandText.  (w/ the @ symbol prefix of coruse)

    You need to use ? instead of named parameters for Jet (i.e., MS Access databases), but not for SQL Server.



  • I tried that and get the following error:

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

    [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@StartDate". 



  •  Ah, that's right, this is classic ADO/OLEDB.  (it's  been a while)

    When using the ? place holders, do you get an error, or just no results?  

    I noticed that based on your "str" prefix it appears that you are setting your parameter values to strings.  Are you sure that SQL Server is interpreting the formats/values of these date/string expressions as you expect?  It is always best to pass strongly-typed values to parameters to eliminate any implicit conversions.  Consider using the CDate() function in your ASP code to generate true DateTime values, and setting your parameters to those.

    (edit: fixed some typos)

     



  • I'd fire up SQL Profiler and see what is actually executing on the server as it might give an indication what's actually going on.  You can get to it from Sql Management Studio from the Tools --> SQL Server Profiler.  I typically put in filters for database name and the login for the user.  You can then go through the entries and see what is being executed (displayed at the bottom).  I use this all the time to help debug SQL.

    The only thought I have is that the driver might be doing something with the parameters if they are actually strings rather than dates as the str prefix indicates.



  • I'm getting no results, but when I output the SQL to debug it, the query contains the "?" placeholders instead of the parameter values I specified in code, as though it's not replacing them with the parameters (that might be the normal behavior, though).  If I hard-code the values as a string, then SQL Server converts it to a date, and gives me the desired results; it's only when I try to parameterize it that isn't working and giving me back no rows at all.  I tried specifying the parameter as a string (since SQL Server will automatically convert it to a datetime) but I get the same thing.

    I just tried using CDate, but the same thing happens.  The query that's displayed on the page (I'm displaying the SQL to make sure it's right) still shows the "?" placeholders instead of the values I'm specifying as params.

    EDIT: Okay, then printing the SQL statement on the page won't help me.  I figured as much.  I'll look at Profiler then and see what's up.



  • @ObiWayneKenobi said:

    I had the page output the SQL and the problem seems to be that it's not substituting the parameters for the "?" in the query, i.e. the outputted code reads "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate BETWEEN ? AND ?" instead of "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate BETWEEN '9/19/2008' and '9/22/2008'
     

    Also -- keep in mind that parameters do not work that way; the SQL statement at the client-side doesn't get altered to include the parameter values -- it is sent with parameter place holders to the server, and the parameter values are sent separately (which is why it is always safe from sql injection and you never need to worry about delimiters or date formats and the like)

    If you want to see the actual SQL statement executed on the server, you can use the SQL Server Profiler.



  • @ObiWayneKenobi said:

    I'm getting no results, but when I output the SQL to debug it, the query contains the "?" placeholders instead of the parameter values I specified in code, as though it's not replacing them with the parameters (that might be the normal behavior, though).  If I hard-code the values as a string, then SQL Server converts it to a date, and gives me the desired results; it's only when I try to parameterize it that isn't working and giving me back no rows at all.  I tried specifying the parameter as a string (since SQL Server will automatically convert it to a datetime) but I get the same thing.
     

    (see my other post as well ....)

    Use CDate() to convert your strings to date time values within ASP, and set breakpoints use logging or whatever you need to do to ensure that within your ASP code you are generating valid and accurate datetime values -- not strings -- and passing those values to your database via the parameters.  



  • I don't know what it was, but I've gotten it working.  Thanks a bunch!


Log in to reply