SQL is showing all records

[Jon]

Hi, I'm using:

SearchString = request.Form("SearchString") 'works fine
TodaysDate = Date() 'gives todays date correclt formatted

strsql = "SELECT * FROM tblListOwners WHERE (((listOwner1) Like '%" &
SearchString & "%') AND ((listCloseDate)>=" & TodaysDate & ")) OR
(((listOwner2) Like '%" & SearchString & "%') AND ((listCloseDate)>=" &
TodaysDate & "));"

However the sql is giving me all data even those where 'listCloseDate' is in
the past..

Any ideas where I've gone wrong please?

Thanks, Jon

[Bob Barrows]

Jon wrote:

> Hi, I'm using:
>
> SearchString = request.Form("SearchString") 'works fine
> TodaysDate = Date() 'gives todays date correclt formatted
>
> strsql = "SELECT * FROM tblListOwners WHERE (((listOwner1) Like '%" &
> SearchString & "%') AND ((listCloseDate)>=" & TodaysDate & ")) OR
> (((listOwner2) Like '%" & SearchString & "%') AND ((listCloseDate)>="
> & TodaysDate & "));"
>
> However the sql is giving me all data even those where
> 'listCloseDate' is in the past..
>
> Any ideas where I've gone wrong please?
>
> Thanks, Jon

We need to see the result of
response.write strsql

Actually, you should see the problem if you do that. Try copying and pasting
the result from the browser window into Query Analyzer and see if you can
see the problem. If not, look here: [url]http://www.aspfaq.com/show.asp?id=2040[/url]

If that doesn't help, post a reply showing the result of the response.write

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> strsql = "SELECT * FROM tblListOwners WHERE (((listOwner1) Like '%" &

> SearchString & "%') AND ((listCloseDate)>=" & TodaysDate & ")) OR
> (((listOwner2) Like '%" & SearchString & "%') AND ((listCloseDate)>=" &
> TodaysDate & "));"

What database / version are you using?

SQL Server requires ' around dates, and you should use YYYYMMDD format.

Access requires # around dates, and you should use YYYY-MM-DD format.

Also, try to avoid SELECT *. Finally, consider the following more legible
rewrite. I'm going to assume that you are using Access.

ss = "'%" & searchString & "%'"
dt = "#" & todaysDate & "#"
strsql = "SELECT columns FROM tblListOwners " & _
" WHERE listCloseDate >= " & dt & _
" AND (ListOwner1 LIKE " & ss & " " & _
" OR ListOwner2 LIKE " & ss & ")

(Removing the delimiters from the string itself make it much more readable,
and I also removed the redundant date check... you only need to check once,
not once within each OR condition.)

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Jon]

Aaron

Thank you, I learnt a lot in this one reply!

It was actually the #'s that was causing the problem - I should have spotted
that myself!!

I am using Access, 2000. I added the ;" at the end of string, but it still
failed. I then changed your columns back to an * and it worked just great -
any ideas why this might be - I get the right answer, I recall reading
elsewhere that the * is a bad idea... ?

Jon


"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:%23PspqfqtDHA.684@TK2MSFTNGP09.phx.gbl...

> strsql = "SELECT * FROM tblListOwners WHERE (((listOwner1) Like '%" &
> SearchString & "%') AND ((listCloseDate)>=" & TodaysDate & ")) OR
> (((listOwner2) Like '%" & SearchString & "%') AND ((listCloseDate)>=" &
> TodaysDate & "));"

What database / version are you using?

SQL Server requires ' around dates, and you should use YYYYMMDD format.

Access requires # around dates, and you should use YYYY-MM-DD format.

Also, try to avoid SELECT *. Finally, consider the following more legible
rewrite. I'm going to assume that you are using Access.

ss = "'%" & searchString & "%'"
dt = "#" & todaysDate & "#"
strsql = "SELECT columns FROM tblListOwners " & _
" WHERE listCloseDate >= " & dt & _
" AND (ListOwner1 LIKE " & ss & " " & _
" OR ListOwner2 LIKE " & ss & ")

(Removing the delimiters from the string itself make it much more readable,
and I also removed the redundant date check... you only need to check once,
not once within each OR condition.)

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows]

Jon wrote:

> Aaron
>
> Thank you, I learnt a lot in this one reply!
>
> It was actually the #'s that was causing the problem - I should have
> spotted that myself!!
>
> I am using Access, 2000. I added the ;" at the end of string, but it
> still failed. I then changed your columns back to an * and it worked
> just great - any ideas why this might be -

Hard to say without knowing what "failed" means: error message? wrong
results? Show us the sql statement that fails.

I suspect you may have used reserved words for your field names. See here
for the list of reserved words that should be avoided for your user-defined
object names: [url]http://www.aspfaq.com/show.asp?id=2080[/url]

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jon]

Bob, I have changed some names since the last post
--------------
SearchString = request.Form("SearchString")
TodaysDate = Date()
ss = "'%" & SearchString & "%'"
dt = "#" & TodaysDate & "#"

strsql = "SELECT columns FROM tblListOwners " & _
" WHERE wishlistCloseDate >= " & dt & _
" AND (wishlistOwner1 LIKE " & ss & " " & _
" OR wishlistOwner2 LIKE " & ss & ");"
response.write strsql 'testing
response.End 'testing
rsuser.open strsql,conn,1,2

Gives:

SELECT columns FROM tblListOwners WHERE wishlistCloseDate >= #30/11/2003#
AND (wishlistOwner1 LIKE '%test text%' OR wishlistOwner2 LIKE '%test
text%');

~~~~~~~~~~~~~
strsql = "SELECT columns FROM tblListOwners " & _
" WHERE wishlistCloseDate >= " & dt & _
" AND (wishlistOwner1 LIKE " & ss & " " & _
" OR wishlistOwner2 LIKE " & ss & ");"
'response.write strsql
'response.End
rsuser.open strsql,conn,1,2

Gives:
a.. Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/found_a_list.asp, line 85

(line 85 is: rsuser.open strsql,conn,1,2 )

Changing columns to * and all is fine!

Jon

------------------

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eY0YZ4ttDHA.556@TK2MSFTNGP11.phx.gbl...
Jon wrote:

> Aaron
>
> Thank you, I learnt a lot in this one reply!
>
> It was actually the #'s that was causing the problem - I should have
> spotted that myself!!
>
> I am using Access, 2000. I added the ;" at the end of string, but it
> still failed. I then changed your columns back to an * and it worked
> just great - any ideas why this might be -

Hard to say without knowing what "failed" means: error message? wrong
results? Show us the sql statement that fails.

I suspect you may have used reserved words for your field names. See here
for the list of reserved words that should be avoided for your user-defined
object names: [url]http://www.aspfaq.com/show.asp?id=2080[/url]

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

Replace "columns" with the actual columns you want, e.g.

SELECT wishlistCloseDate, wishListOwner1, wishListOwner2 FROM ...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:bqd1kc$q91$1@titan.btinternet.com...

> Bob, I have changed some names since the last post
> --------------
> SearchString = request.Form("SearchString")
> TodaysDate = Date()
> ss = "'%" & SearchString & "%'"
> dt = "#" & TodaysDate & "#"
>
> strsql = "SELECT columns FROM tblListOwners " & _
> " WHERE wishlistCloseDate >= " & dt & _
> " AND (wishlistOwner1 LIKE " & ss & " " & _
> " OR wishlistOwner2 LIKE " & ss & ");"
> response.write strsql 'testing
> response.End 'testing
> rsuser.open strsql,conn,1,2
>
> Gives:
>
> SELECT columns FROM tblListOwners WHERE wishlistCloseDate >= #30/11/2003#
> AND (wishlistOwner1 LIKE '%test text%' OR wishlistOwner2 LIKE '%test
> text%');
>
> ~~~~~~~~~~~~~
> strsql = "SELECT columns FROM tblListOwners " & _
> " WHERE wishlistCloseDate >= " & dt & _
> " AND (wishlistOwner1 LIKE " & ss & " " & _
> " OR wishlistOwner2 LIKE " & ss & ");"
> 'response.write strsql
> 'response.End
> rsuser.open strsql,conn,1,2
>
> Gives:
> a.. Error Type:
> Microsoft JET Database Engine (0x80040E10)
> No value given for one or more required parameters.
> /found_a_list.asp, line 85
>
> (line 85 is: rsuser.open strsql,conn,1,2 )
>
> Changing columns to * and all is fine!
>
> Jon
>
> ------------------
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eY0YZ4ttDHA.556@TK2MSFTNGP11.phx.gbl...
> Jon wrote:

> > Aaron
> >
> > Thank you, I learnt a lot in this one reply!
> >
> > It was actually the #'s that was causing the problem - I should have
> > spotted that myself!!
> >
> > I am using Access, 2000. I added the ;" at the end of string, but it
> > still failed. I then changed your columns back to an * and it worked
> > just great - any ideas why this might be -

>
> Hard to say without knowing what "failed" means: error message? wrong
> results? Show us the sql statement that fails.
>
> I suspect you may have used reserved words for your field names. See here
> for the list of reserved words that should be avoided for your

user-defined

> object names: [url]http://www.aspfaq.com/show.asp?id=2080[/url]
>
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

[Bob Barrows]

Jon wrote:

> Changing columns to * and all is fine!
>

This leave two possibilities:
a) "columns" is a reserved keyword. Let me see ... no, I don't see it in
the list. But that does not mean it's not reserved. To find out, treat it as
if it was a reserved keyword and surround it with brackets.
b) "columns" is not the name of a column (field) in your table. Wait a
minute. You're not thinking that you can simply replace "*" with "columns"
are you? In a SELECT statement, you either need a comma-delimited list of
the column names you wish the query to return (strongly recommended), or the
"*" wildcard (strongly discouraged).

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jon]

Ahh... Left hand right hand...

Yes I took you're statement literally!! Doh!

I'm used to writing queries in Access, I'm new to hand coding sql, so I
didn't know any different!!!!

OK so I'll type out all my 'columns' !

I'm very happy to learn, just wondered why "* wildcard (strongly
discouraged)" ?

Jon

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23Hyolq1tDHA.3532@TK2MSFTNGP11.phx.gbl...
Jon wrote:

> Changing columns to * and all is fine!
>

This leave two possibilities:
a) "columns" is a reserved keyword. Let me see ... no, I don't see it in
the list. But that does not mean it's not reserved. To find out, treat it as
if it was a reserved keyword and surround it with brackets.
b) "columns" is not the name of a column (field) in your table. Wait a
minute. You're not thinking that you can simply replace "*" with "columns"
are you? In a SELECT statement, you either need a comma-delimited list of
the column names you wish the query to return (strongly recommended), or the
"*" wildcard (strongly discouraged).

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> just wondered why "* wildcard (strongly

> discouraged)" ?

[url]http://www.aspfaq.com/2096[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Jon]

This is weird now.. working locally on my PC - so date format shouldn't (?)
be an issue, however is UK English so DD/MM/YYYY:

SELECT wishlistID, wishlistType, wishlistOwner1, wishlistOwner2,
wishlistEvent, wishlistEventDate, wishlistCloseDate FROM tblListOwners WHERE
wishlistCloseDate >= #30/11/2003# AND (wishlistOwner1 LIKE '%test%' OR
wishlistOwner2 LIKE '%test%');

Hides two wishlistCloseDate : 25/11/2003 and 21/11/2003

But

SELECT wishlistID, wishlistType, wishlistOwner1, wishlistOwner2,
wishlistEvent, wishlistEventDate, wishlistCloseDate FROM tblListOwners WHERE
wishlistCloseDate >= #01/12/2003# AND (wishlistOwner1 LIKE '%test%' OR
wishlistOwner2 LIKE '%test%');

Fails to hide the above dates!!

Any ideas???

The sql is now produced with:

SearchString = request.Form("SearchString")
TodaysDate = Date()
ss = "'%" & SearchString & "%'"
dt = "#" & TodaysDate & "#"

'ss is entered as test - which finds all records as they contain that word

strsql = "SELECT wishlistID, wishlistType, wishlistOwner1, wishlistOwner2,
wishlistEvent, wishlistEventDate, wishlistCloseDate FROM tblListOwners " & _
" WHERE wishlistCloseDate >= " & dt & _
" AND (wishlistOwner1 LIKE " & ss & " " & _
" OR wishlistOwner2 LIKE " & ss & ");"

I've run this with Access Query Analyzer from
[url]http://www.sliver.com/dotnet/AccessQueryAnalyzer/[/url] and get the same results.

[Aaron Bertrand - MVP]

> This is weird now.. working locally on my PC - so date format shouldn't
(?)

> be an issue, however is UK English so DD/MM/YYYY:

STOP USING THAT FORMAT!

As I suggested previously,

WHERE wishlistCloseDate >= #2003-11-30#

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Phillip Windell]

"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:bqg7lm$bu3$1@titan.btinternet.com...

> This is weird now.. working locally on my PC - so date format

shouldn't (?)

> be an issue, however is UK English so DD/MM/YYYY:

Use the data format Aaron suggested of YYYY-MM-DD. Part of its purpose
is to avoid geographical issues from contry to country and to prevent
the computer from confusing the month and day. I don't have the link
to the page on his site but it shouldn't be hard to find.

--

Phillip Windell [CCNA, MVP, MCP]
WAND-TV (ABC Affiliate)
[url]www.wandtv.com[/url]

[Jon]

Aaran, Thanks for the help so far, I can set the format of the data any
which way in my Access db, yyyy-mm-dd as you suggest but I can't seem to
force the asp date to match, eg the:

TodaysDate = Date()
dt = "#" & TodaysDate & "#"

Still produces #dd/mm/yyyy#

Jon

My access database has not got the format set,
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23Q1DVlEuDHA.2260@TK2MSFTNGP09.phx.gbl...

> This is weird now.. working locally on my PC - so date format shouldn't

(?)

> be an issue, however is UK English so DD/MM/YYYY:

STOP USING THAT FORMAT!

As I suggested previously,

WHERE wishlistCloseDate >= #2003-11-30#

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Aaron Bertrand - MVP]

Why do you think Date() would be able to read your mind?
[url]http://www.aspfaq.com/2313[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

> Aaran, Thanks for the help so far, I can set the format of the data any
> which way in my Access db, yyyy-mm-dd as you suggest but I can't seem to
> force the asp date to match, eg the:
>
> TodaysDate = Date()
> dt = "#" & TodaysDate & "#"
>
> Still produces #dd/mm/yyyy#

[Jon]

I'm trying [url]http://www.aspfaq.com/show.asp?id=2260[/url]

:)

"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:bqgco3$f7b$1@hercules.btinternet.com...
Aaran, Thanks for the help so far, I can set the format of the data any
which way in my Access db, yyyy-mm-dd as you suggest but I can't seem to
force the asp date to match, eg the:

TodaysDate = Date()
dt = "#" & TodaysDate & "#"

Still produces #dd/mm/yyyy#

Jon

My access database has not got the format set,
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23Q1DVlEuDHA.2260@TK2MSFTNGP09.phx.gbl...

> This is weird now.. working locally on my PC - so date format shouldn't

(?)

> be an issue, however is UK English so DD/MM/YYYY:

STOP USING THAT FORMAT!

As I suggested previously,

WHERE wishlistCloseDate >= #2003-11-30#

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Aaron Bertrand - MVP]

2260 is meant to resolve *DISPLAY* issues (e.g. users who want to see
dd/mm/yyyy or mm/dd/yyyy on their web page). You should still be sending
YYYY-MM-DD to Access to avoid confusion and ambiguity in the software, which
doesn't care how a date *looks.*

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:bqgddc$h71$1@hercules.btinternet.com...

> I'm trying [url]http://www.aspfaq.com/show.asp?id=2260[/url]
>
> :)

[Jon]

OK so I'm using

Function dbDate(dt)
dbDate = year(dt) & "/" & left("00",2-len(month(dt))) &_
month(dt) & "/" & left("00",2-len(day(dt))) & day(dt)
End Function

(it wouldn't work without the & "/" &)

Can I be sure that in
WHERE wishlistCloseDate >= #2003/11/30#

my wishlistCloseDate will be in the correct format, do I need to change the
database field to match this?

Jon


"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:bqgco3$f7b$1@hercules.btinternet.com...
Aaran, Thanks for the help so far, I can set the format of the data any
which way in my Access db, yyyy-mm-dd as you suggest but I can't seem to
force the asp date to match, eg the:

TodaysDate = Date()
dt = "#" & TodaysDate & "#"

Still produces #dd/mm/yyyy#

Jon

My access database has not got the format set,
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23Q1DVlEuDHA.2260@TK2MSFTNGP09.phx.gbl...

> This is weird now.. working locally on my PC - so date format shouldn't

(?)

> be an issue, however is UK English so DD/MM/YYYY:

STOP USING THAT FORMAT!

As I suggested previously,

WHERE wishlistCloseDate >= #2003-11-30#

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Aaron Bertrand - MVP]

> (it wouldn't work without the & "/" &)

What does "wouldn't work" mean? What happens when you use - instead of / ?

> my wishlistCloseDate will be in the correct format, do I need to change

the

> database field to match this?

No, this is what I've been trying to explain. The column is a *DATE*
column, not a *TEXT* column. It evaluates dates as dates, not as strings.
It does not necessarily store the date in the same format it presents to you
in the GUI.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows]

Jon wrote:

> OK so I'm using
>
> Function dbDate(dt)
> dbDate = year(dt) & "/" & left("00",2-len(month(dt))) &_
> month(dt) & "/" & left("00",2-len(day(dt))) & day(dt)
> End Function
>
> (it wouldn't work without the & "/" &)
>
> Can I be sure that in
> WHERE wishlistCloseDate >= #2003/11/30#
>
> my wishlistCloseDate will be in the correct format, do I need to
> change the database field to match this?
>

You are missing the point. Dates are not stored with any format. In Access a
date/Time is stored as a Double, with the whole number portion representing
the number of days since the seed date, and the decimal portion representing
the time of day (0=midnight, .5 = noon, etc.).

So changing the Access-defined Format property of this date/time field will
have no effect on anything except how the dates are displayed _within
Access_.

According to the Access online help, literal dates in queries should be
formatted as US-style short dates: m/d/yyyy. However, we have discovered
that Jet has no problem dealing with dates in ISO format:
yyyy-mm-dd

I doubt that Jet will be able to handle this non-standard format you are
attempting to use for some reason.

All this silliness could be avoided if you used a saved parameter query. A
Google search should provide several examples of creating and using saved
parameter queries: many of them from me.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"