CODEWITHSUNDEEP
sqlvisual-studio-2010where-clause
Narges
Narges

Reputation: 1355

where clause does not work

I am using visual studio2010, working with asp.net. I want a query to work like example but mine doesn't work!

I have these rows in database:

Fullname=joe.address=earth,work=5656,home=23,mobile=55
Fullname=Michel.address=earth,work=5,home=2343,mobile=5435

User just enter:

Fullname=joe ,address=earth

The program should return JUST the first row. 0 is my default value for textBoxes.

SELECT fullName, address, work, home, mobile, registrationNo
  FROM contacts
 WHERE (fullName = @fullName OR fullName = '0')
   AND (address = @address OR address = '0')
   AND (work = @work OR work = '0')
   AND (home = @home OR home = '0')
   AND (mobile = @mobile OR mobile = '0')

Upvotes: 0

Views: 178

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239784

You're checking the columns to be equal to '0', when I think you meant to check the parameters:

SELECT fullName, address, work, home, mobile, registrationNo
FROM   contacts
WHERE  (fullName = @fullName OR @fullName = '0')
AND    (address = @address   OR @address = '0')
AND    (work = @work         OR @work = '0')
AND    (home = @home         OR @home = '0')
AND    (mobile = @mobile     OR @mobile = '0')

As pointed out in the comments though, you really ought to use NULL for the absence of a value - that's the main reason it exists in SQL - and also means you can use appropriate data types (where necessary), rather than always treating things as strings. (In this case, however, everything does appear to be a string).

Upvotes: 6

Related Questions