Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

check if a value exists in a column

Posted on 2011-03-23
4
Medium Priority
?
213 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach 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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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