Solved

check if a value exists in a column

Posted on 2011-03-23
4
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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