Reputation: 171
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
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
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