Sql Count(*)
I keep getting an error when using this query
SQLtemp = "SELECT COUNT(*) FROM urlviews WHERE profilefor = '" & Request.Querystring("user") & "' "
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/HotListaUserNameSQL/profile_views.asp, line 171
Is the SQL syntax correct? (using asp)
kb posted this at 14:05 — 27th August 2007.
He has: 1,380 posts
Joined: Feb 2002
Are you trying to count the number of results returned? I've never seen "SELECT COUNT" before, but this is how I would structure a 'count results' query (in PHP, but the SQL is the same):
<?php$sql = mysql_query(\"SELECT * FROM urlviews WHERE profilefor = '$user'\");
$n_rows = mysql_num_rows($sql);
?>
The other issue may be, if your syntax is correct/valid, that the database/table/field you are referencing doesn't exist, or is spelled differently...
Finally, since I don't know how to do this in ASP and how it handles errors, does this mean that what you happened to be looking for doesn't exist? Meaning if you wanted profilefor = 'KyleBrady' and there is no username of 'KyleBrady'... does it return what you're getting?
Greg K posted this at 14:25 — 27th August 2007.
He has: 2,145 posts
Joined: Nov 2003
This is something I always recommend to people who get errors with a query in PHP:
Right before you execute the query, display the sql statement to make sure it looks formatted well. (when mixing in variables while building it, easy to miss a improper quote or missing comma, etc). Also you should validate the Request.Querystring("user") and make sure any quotes are escaped (not sure how to do this in ASP), say that Request.Querystring("user") was set to o'neil the single quote would mess you up. (even if your test value doesn't have one, being that it is something that a user can manually change by changing the URL, you wll want to protect against single quotes and semicolons if ASP/MSSQL let you stack multiple statements in one query execution
Imaging if I called it with a query string of bob'; DELETE FROM urlveiws WHERE profilefor = '% (or whichever character is the wildcard in MSSQL), the executed query would be:
SELECT COUNT(*) FROM urlviews WHERE profilefor = 'bob'; DELETE FROM urlveiws WHERE profilefor = '%'
As you can see... bye bye data...
If the sql statement does look formatted fine and everything, then take it and manually run it on the database (not sure what MS SQL has for manually running a SQL statement. In PHP with mySQL, you can sometimes get a more detailed error when running it directly on the database.
kb posted this at 15:48 — 27th August 2007.
He has: 1,380 posts
Joined: Feb 2002
Greg, that's why you use something to get rid of those characters, assuming you're accepting data from an outside source. Either a homegrown solution, or something like html_entities... converts things to their HTML char value instead of leaving them as they are.
If the source is internal, then I agree to your logic.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.