Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Query Issue

  1. #1
    New to the CF scene
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Issue

    I keep getting this error:

    error '80004005'
    /view_expense_report2x.asp, line 240

    PHP Code:
    "SELECT SUM(cost) AS [RecordSum] FROM expensereport WHERE mydate BETWEEN #" firstDate "# AND #" lastDate "# and team='teamname' AND names='"&Request.QueryString("names")&"';" 
    There is no problem when I do this, but I need to have it only include what querys from the names field
    PHP Code:
    "SELECT SUM(cost) AS [RecordSum] FROM expensereport WHERE mydate BETWEEN #" firstDate "# AND #" lastDate "# and team='teamname'" 
    Please let me know where I am going wrong.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Your string values must be quoted as you have it set up to do, but the = sign only works for one value, not multiple.

    You need an IN instead, and you need a little function to wrap the strings in quotes.
    Something like this. (look up replace syntax, you want to replace commas with quote comma quote)

    AND names in ('" & Replace(Request.QueryString("names"),",","','") & "')"

    I'm assuming names has values that are comma-separated.

    Do be careful with the possibility of the names having apostrophes in them. That kills straight sql like this. You may want to do an additional replace of a single quote with two single quotes or whatever your database uses as an escape character.

    And not checking the query string for sql injection attacks is begging for trouble.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New to the CF scene
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    names is actually a number field. I did not know you can only have 1 = sign in a query.

    I am going to look into an in statment.

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Oh, that's easier then
    No quotes at all needed.

    AND names in (" & Request.QueryString("names") & ")"

    Still beware sql injection attacks; this is a classic opening.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •