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 ActiveUserAuthorID
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.Username, Members.Author_ID
FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
= tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" & lngLoggedInUserID &
"
AND tblBuddyList.Buddy_ID <> 2
ORDER BY Members.Username ASC;

Thanks in advance

Lee

 

[Aaron Bertrand [MVP]]

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>

 

[Bob Barrows]

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

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 ActiveUserAuthorID

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

ORDER BY m.Username ASC;

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?

It worked before modification from:

SELECT tblBuddyList.*, Members.Username, Members.Author_ID
FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
= tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" &
lngLoggedInUserID & "
AND tblBuddyList.Buddy_ID <> 2
ORDER BY Members.Username ASC;

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

Bob Barrows

 

[Rob Meade]

...

SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
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;

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]

Lee,

what value do u set to @LoggedInUserID?