0

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?

codeFinatic
  • 171
  • 1
  • 5
  • 18
  • *(Not directly related to your problem)* [Your code could be vulnerable to SQL Injection attacks](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should parameterize your query. – Matthew Haugen Feb 07 '15 at 01:57
  • (Also not directly related but the last guy did it :) throwing an exception to handle a know case is bad form. Additionally, exceptions are expensive performance wise. – THBBFT Feb 07 '15 at 02:27
  • Well what would be the appropriate action then? Im curious now @THBBFT – codeFinatic Feb 07 '15 at 02:45
  • Without knowing the business logic ... I'd say go with either returning 0 (false) or 1 (true) and simply informing the user that they cannot create that account. – THBBFT Feb 07 '15 at 03:01

2 Answers2

2

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

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

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());

JAYARAJ SIVADASAN
  • 51
  • 6