CODEWITHSUNDEEP
c#sql-serverselect
codeFinatic
codeFinatic

Reputation: 171

Getting Return Count of Select SQL Server

I am trying to get the return count of a query. In my case it always be either 0 or 1. I am inserting an customer into my database, I am whether or not they exist already, if they do I throw an exception, if not I insert. This is what I have:

string selectQuery = "select ([Cust_Num]) from [TBL_Customer] where [Cust_Num] = '" + myTable.Rows[i][custNum] + "'";
SqlCommand sqlCmdSelect = new SqlCommand(selectQuery, sqlConn);
sqlCmdSelect.Connection.Open();
sqlCmdSelect.ExecuteNonQuery();

//checks if query returned anything
int queryCount = Convert.ToInt32(sqlCmdSelect.ExecuteScalar());
sqlCmdSelect.Connection.Close();

Right now, queryCount returns 0 if it doesn't exist in my table. But it returns the customer number if it does exist. So instead of returning 1 it returns 123456 which is the customers number.....

Does anyone know why this is happening?

Upvotes: 0

Views: 223

Answers (2)

JAYARAJ SIVADASAN
JAYARAJ SIVADASAN

Reputation: 51

A few points here. Why you are using ExecuteNonQuery and ExecuteScalar in your code ? If you just need the count the above answer given by @NoDisplayName is perfect.

Example: This example will return you the count of employees with the last anme Coleridge.

SqlCommand Cmd = new SqlCommand("SELECT COUNT(1) FROM Employee WHERE LastName='Coleridge'"); int result = Convert.ToInt32(Cmd.ExecuteSaclar());

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93764

Instead use count aggregate 

select count(Cust_Num) from [TBL_Customer] where [Cust_Num] = ..

for the given cust_num Count aggregate will count how many times cust_num is present. If cust_num exists it will return the count else 0

or even 1 as hardcoded value

select 1 from [TBL_Customer] where [Cust_Num] =..

here if cust_num exists then it will return 1 else nothing will be returned

Upvotes: 1

Related Questions