Solved

check if a value exists in a column

Posted on 2011-03-23
4
209 Views
Last Modified: 2012-06-22
I want to check if a value exist in a column:
I'm very new to SQL and c#.
I suspect that there is some easier way to do this.
Right now i do this
SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo =" + customerNumber, conn);
                int count = (int)myCommand2.ExecuteScalar();
                if (count == 1)
                {
                    existing = true;
                }
                else
                {
                    existing = false;
                }
return existing;

Open in new window

0
Comment
Question by:llegar100
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35197066
I'd do it like this:

SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber", conn);

myCommand2.Parameters.AddWithValue("@CustomerNumber", customerNumber) 

return (boolean)myCommand2.ExecuteScalar();

Open in new window


First, parameterize your customerNumber - it protects you from SQL Injection attacks, and it's quicker for the query engine to parse.

Second, the result of your query will either be zero or something else, so you can convert it directly to a boolean value without doing your if statements - if it's zero, the boolean is zero, and if it's anything else, the boolean returns true (non-zero).
0
 
LVL 1

Expert Comment

by:Muralidharand
ID: 35197243

Please parameterize your query .

SqlCommand myCommand2 = new SqlCommand("SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber", conn);

myCommand2.Parameters.AddWithValue("@CustomerNumber", customerNumber)

return Convert.ToBoolead(myCommand2.ExecuteScalar());

0
 
LVL 13

Expert Comment

by:dwkor
ID: 35200409
Use the following SQL instead. you don't need to count actual rows to check existence
select case when exists (SELECT count(*) FROM Customers WHERE CustNo = @CustomerNumber) then 1 else 0 end

Open in new window

0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35210614
You probably meant this:

select case when exists (SELECT CustNo FROM Customers WHERE CustNo = @CustomerNumber) then 1 else 0 end

Open in new window


For some reason, I never think to use the EXISTS statement, but it's great - SQL will run until it finds a match and then quit - there's no need to get a count of rows here when you don't care about the count at all, just a yes/no.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question