• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Select all Records using VBS/Query in Sql

I'm trying to do something similar to : strSQL = "SELECT * FROM dbo.ActivityLog Where Username = '*'  and Action = '*' and GroupName = '*' Order BY [TrueDate] DESC".

Leaving out  "where username = ..." is not an option.  Maybe there is a better way to do this, but I'll try to explain why I need this...

I have a web page where a user can select different criteria, based on what they'd like to see from the query. There is an option of 'Group name' , 'username', and 'action'. I would like to include all data, if an option is not selected, and only the data that is selected.
2 Solutions
Just change the '*' to '%'.
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
there is no "star" option ( * ) when assigning a value to a column in a where clause, one just leaves out that column.

why can't you leave out the username column when no options are selected?
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If you are using sql server it would be

Where Username like '%'

because the percent sign is the wild card.  

However, having multiple wildcards can slow things down.  If you don't need them, then don't use them.  It does not make sense to use where username like '%' when leaving it does the same thing.

What you probably should be doing is building your sql statement on the fly.   As example:

strSQL =SELECT * FROM dbo.ActivityLog Where 

if request.form("username")<>"" then ' no we know there is data
    ' next it is always good to clean your data input, never trust it
    ' below code just removes single quotes as an example

      strSQL=strSQL&" Username like '"&username&"' "
end if

Open in new window

From there, keep building your sql statement. Notice that if we don't get username input, we just don't put it in the WHERE clause.  This will speed things up on the data end.
Darkejo1Author Commented:
Thanks for your response.

I Just tested and that does not work.

To clarify, I am running MS SQL Server 2005 and I used this query to test:

The table name is "ActivityLog" If I try

Select * From ActivityLog Where [Username] = '%'

0 fields are returned.

Correction..It looks like I need to change the = to Like.
Darkejo1Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now