...

View Full Version : Query Issue



rparish
12-15-2006, 08:52 PM
I keep getting this error:

error '80004005'
/view_expense_report2x.asp, line 240



"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


"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.

nikkiH
12-15-2006, 10:32 PM
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. ;)

rparish
12-15-2006, 11:06 PM
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.

nikkiH
12-15-2006, 11:47 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum