SQL COUNT Troubles

Basher · 27 Jan 2007 at 15:31

Hi guys,

I am writing some SQL to query a database to see if a username already exists, what I have so far is the following:

Code:

SELECT COUNT(login) as Expr1
FROM tblEmployee
WHERE login=@login

Now, this kind of works, for example if I enter a random username it will inform me that that is fine and I can carry on, and if I insert a username I know exists such as 'james' it tells me that already exists, so great.

But if I enter 'jamesjames' into the system, it also tells me that this exusts, when it doesn't!

Anyone any ideas on why this could be?

Kind Regards,

Basher

rpstewart · 27 Jan 2007 at 16:16

The only thing I can think of is that somehow the value for the where clause is being truncated. What's the field type & length for login?

JIMA · 27 Jan 2007 at 16:23

Do you need quotes around the @login string or are they already included in the string? I'm thinking that maybe the "where" is erroneously evaluating to true if the quotes aren't included.

Jim

Basher · 27 Jan 2007 at 16:32

I tried quotes, but that gave me errors,

the field type is nvarchar(8)

I've just tried some more random text, 'thisisnew' and now it's saying that this is already in the DB! (it isn't!)

Kind Regards,

James

rpstewart · 27 Jan 2007 at 16:57

How is the rest of your code interpreting the result of the query? 0=not in DB, 1=in DB?

Basher · 27 Jan 2007 at 17:32

Looking at it now it appears that when I enter something over the 8 chars, it will seem to think it's in the DB. I can solve this by validating entry so people cannot enter more than 8 chars.

Thanks for the help,

Regards,

James

Allen · 31 Jan 2007 at 21:57

hi,

may sound kind ov obvious but it isnt somehow keeping the variable "1" or "0"
is it? maybe try initialising this variable to null everytime through.

just a thaught and without the context i could be wrong.

chris