not in statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 27 May 2006 14:24 GMT

Hey gang. I have searched this, because i thought i was doing it right. what
i read says i am doing it right, but still getting an error. here is the
statement:

set schedule11 = conn.execute("select distinct(team_name) from teams where
not in (select teama, teamb from schedule where week = '" & var1 & "')")
do while not schedule11.eof
var_bye = schedule11.fields.item("team_name").value
schedule11.movenext
loop
what this SHOULD do, is get the only team_name that doesn't exist in the
teama or teamb fields in the schedule table. but i am getting an error
instead:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'in'.

/team_schedule_current.asp, line 106

this is mssql db

I tried not exists but that didn't work either. it doesn't return a value. i
put response write on the var_bye variable, and it is blank on the output.
so i am guessing that it isn't finding a value. i did a response.write on
the statement itself, and it is correct, showing the current week.

any ideas??

Reply to this Message

Jeff - 27 May 2006 14:31 GMT

Ok, i changed it a little, but still doesn't work.
i changed it to

set schedule11 = conn.execute("select distinct(team_name) from teams where
not in (select teama, teamb from schedule where week = '" & var1 & "')")

because any team_name in the teams table will be distinct anyway.

> Hey gang. I have searched this, because i thought i was doing it right.
> what i read says i am doing it right, but still getting an error. here is
[quoted text clipped - 24 lines]
>
> any ideas??

Reply to this Message

Jeff - 27 May 2006 14:42 GMT

please ignore... i went in a different direction, and made changes in the
DB..

> Hey gang. I have searched this, because i thought i was doing it right.
> what i read says i am doing it right, but still getting an error. here is
[quoted text clipped - 24 lines]
>
> any ideas??

Reply to this Message

Bob Barrows [MVP] - 27 May 2006 14:43 GMT

> please ignore... i went in a different direction, and made changes in
> the DB..

too late, but read my message anyways

Signature

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"

Reply to this Message

Bob Barrows [MVP] - 27 May 2006 14:43 GMT

> Hey gang. I have searched this, because i thought i was doing it
> right. what i read says i am doing it right, but still getting an
> error. here is the statement:
>
> set schedule11 = conn.execute("select distinct(team_name) from teams
> where not in

A comparison, like a sentence, requires both a subject and an object. You
have only supplied the object. Go back to the source you got this from. You
will see the syntax is:

where <column/expression> in <list or select statement>

> (select teama, teamb from schedule where week = '" &
> var1 & "')") do while not schedule11.eof
[quoted text clipped - 14 lines]
> I tried not exists but that didn't work either. it doesn't return a
> value.

It doesn't need to. There are a couple ways to tackle this: outer joins and
WHERE EXISTS. You should test each option in Query Analyzer to see which
provides the best performance.
Try these in Query Analyzer (always test/debug your queries in QA before
attempting to run them in a client application. This leads to the other best
practice of using stored procedures.)

select distinct(team_name) from teams t
left join schedule ta on t.team_name=ta.teama
left join schedule tb on t.team_name=tb.teamb
where not (ta.teama is null or tb.teamb is null)

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from schedule
where teama=t.team_name) AND
NOT EXISTS (select * from schedule
where teamb=t.team_name)

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from schedule
where teama=t.team_name OR teamb=t.team_name)

PS. Your practice of using dynamic sql is exposing you to the risk of having
your site attacked by hackers using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

If you are dead-set against using stored procedures, you can still protect
yourself by using parameters via strings containing ODBC parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e

If I can convince you to use stored procedures, here is an easy technique
for passing parameters to them without using dynamic sql:
http://tinyurl.com/jyy0

Signature

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"

Reply to this Message

Bob Barrows [MVP] - 27 May 2006 14:57 GMT

> select distinct(team_name) from teams t
> left join schedule ta on t.team_name=ta.teama
[quoted text clipped - 10 lines]
> WHERE NOT EXISTS (select * from schedule
> where teama=t.team_name OR teamb=t.team_name)

Just because I've got a little time on my hands, here's a couple more
options:

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from
(SELECT teama as team from schedule
union
SELECT teamb from schedule) q
WHERE q.team = t.team_name)

This will probably be the worst performer (the use of IN is not
recommended):
select distinct(team_name) from teams t
WHERE NOT team_name IN (
SELECT teama as team from schedule
union
SELECT teamb from schedule)

Signature

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"

Reply to this Message

Jeff - 27 May 2006 14:59 GMT

thanks bob as always.
so using stored procedures is that much more efficient that coding it in
asp?

>> Hey gang. I have searched this, because i thought i was doing it
>> right. what i read says i am doing it right, but still getting an
[quoted text clipped - 63 lines]
> for passing parameters to them without using dynamic sql:
> http://tinyurl.com/jyy0

Reply to this Message

Bob Barrows [MVP] - 27 May 2006 15:19 GMT

> thanks bob as always.
> so using stored procedures is that much more efficient that coding it
> in asp?

Usually, but not always.
However, the real benefit comes from :
1. encapsulation - when multiple steps need to be done in a database,
especially tasks where a step depends on the outcome of a previous step, it
is much more efficient to do all the steps in a single stored procedure,
rather than sending multiple statements to the database one after the other
2. re-use - do you find yourself creating the same sql statement in multiple
pages? Why not just create a single stored procedure and call that whenever
it is needed? This get even more valuable when you make a change somewhere
that requires a change to the sql statement. Would you rather change it once
in the procedure? Or many times in all the places it is used (granted, if
the change requires a change to the code that handles the results of the sql
statement, you'll probably have to visit all those pages anyways ... )
3. bandwidth conservation - which requires more bandwidth? Sending a long
sql statement (or multiple batched statements) over the wire? Or sending
the name of a stored procedure with the parameter values?
4. SQL 7+ creates and caches query plans for dynamically created sql
statements that meet certain requirements, so the pre-compilation benefit
for stored procedures is not as great as it was in sql 6.5. Some experts do
say the sql query optimizer does a better job of optimizing stored
procedures than dynamic sql statments, but unfortunately I've seen no
benchmarks to support this contention.

As SQL BOL says: bring the processing to the data rather than the data to
the processing

There are those who advise against the use of stored procedures. You should
google Frans Bouma's blog entry on this topic to see the arguments against
them.

Bob Barrows

Signature

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"

Reply to this Message

Bob Barrows [MVP] - 27 May 2006 15:59 GMT

>> thanks bob as always.
>> so using stored procedures is that much more efficient that coding it
[quoted text clipped - 24 lines]
> optimizing stored procedures than dynamic sql statments, but
> unfortunately I've seen no benchmarks to support this contention.

Oops! I forgot the main benefit:
It forces you to make sure the procedure/statement works before attempting
to run it from a client application. This is a big benefit when problems
occur because you know where debugging needs to occur.

Bob Barrows

Signature

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"

Reply to this Message

Jeff - 27 May 2006 15:38 GMT

very informative Bob. Thanks a bunch for that, and for the code examples
above.

> thanks bob as always.
> so using stored procedures is that much more efficient that coding it in
[quoted text clipped - 67 lines]
>> for passing parameters to them without using dynamic sql:
>> http://tinyurl.com/jyy0

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2006

not in statement