Syntax Problem

[Lee Mundie]

Hi,
Im trying to get his SQl to work but run thru a query analiser tells me
there is a syntax error and in the browser returns no results - any ideas?

SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAutho rID
FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE bl.Author_ID
= @LoggedInUserID AND bl.Buddy_ID <> 2
ORDER BY m.Username ASC;

It worked before modification from:

SELECT tblBuddyList.*, Members.Usernam e, Members.Author_ ID
FROM Members INNER JOIN tblBuddyList ON Members.Author_ ID
= tblBuddyList.Bu ddy_ID WHERE tblBuddyList.Au thor_ID=" & lngLoggedInUser ID &
"
AND tblBuddyList.Bu ddy_ID <> 2
ORDER BY Members.Usernam e ASC;

Thanks in advance

Lee

[Aaron Bertrand [MVP]]

Re: Syntax Problem

What is the EXACT error message (there are different kinds of syntax errors;
VBScript or from the database)? What is the EXACT code you are using? What
database and version are you using? Are you connecting via ODBC or OLEDB?

<followups set to asp.db only>

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"Lee Mundie" <lee.j.mundie@n tlworld.com> wrote in message
news:0GJvb.1965 $B03.1220806@ne wsfep2-win.server.ntli .net...[color=blue]
> Hi,
> Im trying to get his SQl to work but run thru a query analiser tells[/color]
me[color=blue]
> there is a syntax error and in the browser returns no results - any ideas?
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAutho rID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE[/color]
bl.Author_ID[color=blue]
> = @LoggedInUserID AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Usernam e, Members.Author_ ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ ID
> = tblBuddyList.Bu ddy_ID WHERE tblBuddyList.Au thor_ID=" & lngLoggedInUser ID[/color]
&[color=blue]
> "
> AND tblBuddyList.Bu ddy_ID <> 2
> ORDER BY Members.Usernam e ASC;
>
> Thanks in advance
>
> Lee
>
>[/color]

[Bob Barrows]

Re: Syntax Problem

I was going to ask if this was Access or SQL Server, but "@LoggedInUserI D"
leads me to lean more towards SQL Server. Please don't make us guess. :-)
The version of SQL Server may also be relevant.

Lee Mundie wrote:[color=blue]
> Hi,
> Im trying to get his SQl to work but run thru a query analiser
> tells me there is a syntax error and in the browser returns no
> results - any ideas?
>
> SELECT[/color]
m.Username,
m.Author_ID,
au.Author_ID AS ActiveUserAutho rID[color=blue]
> FROM tblBuddyList bl[/color]
INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID[color=blue]
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID[/color]
WHERE[color=blue]
> bl.Author_ID = @LoggedInUserID[/color]
AND bl.Buddy_ID <> 2[color=blue]
> ORDER BY m.Username ASC;[/color]

Hmm, so I'm assuming this is from a stored procedure? When you try to run it
in Query Analyzer, do you declare the @LoggedInUserID variable and give it a
value?
[color=blue]
>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Usernam e, Members.Author_ ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ ID
> = tblBuddyList.Bu ddy_ID WHERE tblBuddyList.Au thor_ID=" &
> lngLoggedInUser ID & "
> AND tblBuddyList.Bu ddy_ID <> 2
> ORDER BY Members.Usernam e ASC;[/color]

So I'm correctly assuming you're rewriting this to get away from dynamic
sql? Good!

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"

[Rob Meade]

Re: Syntax Problem

"Lee Mundie" wrote ...
[color=blue]
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAutho rID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE[/color]
bl.Author_ID[color=blue]
> = @LoggedInUserID AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;[/color]

Think it might be the @LoggedInUserId etc - dont you only have the @ when
you run it in SQL - ie, a stored procedure - I might be wrong - just the
first thing that my QA spotted...

Regards

Rob

[Guest]

Re: Syntax Problem

Lee,

what value do u set to @LoggedInUserID ?

"Lee Mundie" <lee.j.mundie@n tlworld.com> wrote in message
news:0GJvb.1965 $B03.1220806@ne wsfep2-win.server.ntli .net...[color=blue]
> Hi,
> Im trying to get his SQl to work but run thru a query analiser tells[/color]
me[color=blue]
> there is a syntax error and in the browser returns no results - any ideas?
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAutho rID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE[/color]
bl.Author_ID[color=blue]
> = @LoggedInUserID AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Usernam e, Members.Author_ ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ ID
> = tblBuddyList.Bu ddy_ID WHERE tblBuddyList.Au thor_ID=" & lngLoggedInUser ID[/color]
&[color=blue]
> "
> AND tblBuddyList.Bu ddy_ID <> 2
> ORDER BY Members.Usernam e ASC;
>
> Thanks in advance
>
> Lee
>
>[/color]