Check if row exists in database

Question

I have a database and want to check if a specific record already exists.

string sql = "SELECT COUNT(*) from tbl_student WHERE email = '" + mail + "' AND telephone= '" + telephone + "'";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
int userCount = (int)command.ExecuteScalar();
if (userCount > 0)
{
    lblMessage.Text = "Person already exists";
}

The line that is not working. The error message says it is an invalid cast. I have no clue, please help me.

int userCount = (int)command.ExecuteScalar();

Nice sql injection hole there. This is really really important: **DO NOT CONCATENATE VALUES INTO SQL**. Use parameters. Any other issue in the code is secondary to this huge huge huge massive problem. Frankly, the code is doing you a favor by not running successfully. Maybe it is a form of protest. — Marc Gravell, Apr 22 '14 at 09:40
what is `commnad3`? it should be `command` and didn't you heard about SqlInjection... — Sachin, Apr 22 '14 at 09:40
Do `var result =command.ExecuteScalar();` and check what is the value in result — huMpty duMpty, Apr 22 '14 at 09:42
actually `command.ExecuteScalar()` is returning null and you type casting it to int. — Hassan, Apr 22 '14 at 09:45
@NagarajS `int` is an alias for `Int32`. That is no different whatsoever. — Marc Gravell, Apr 22 '14 at 09:45
@HassanNisar if you mean `null`: that scenario throws a NRE, not an invalid cast. `DBNull` throws invalid cast, but `select count(*)` does not return `DBNull`. — Marc Gravell, Apr 22 '14 at 09:46
@MarcGravell Is [an extension](http://stackoverflow.com/a/34438198/2404470) helpful? — Zameer Ansari, Dec 23 '15 at 14:52
@student not really, no; there's a huge difference between data existing in a database and data existing in a `DataSet`. Also: unless it is 2005 again, you probably shouldn't be using `DataSet`. — Marc Gravell, Dec 24 '15 at 11:31
@MarcGravell Oops, I did not saw the question. Btw, what is the disadvantage of using `DataSet`s? — Zameer Ansari, Dec 24 '15 at 11:36
@student pretty much any alternative would be vastly preferable. ORMs and micro-ORMs make it trivial to map regular classes to database concepts. DataSet is horribly inefficient, almost always used inappropriately, and generally used very very inefficiently. There are very few scenarios where DataSet would be desirable, and that usually resolves around ad-hoc reporting / SQL tools where the column structure cannot be predicted in advance. — Marc Gravell, Dec 24 '15 at 12:53

score 2 · Accepted Answer · answered Apr 22 '14 at 15:56

Thank you guys for your help. I tried many of your suggestions and they did not work. I then took huMpty duMpty advice and stored it into a var variable. I then converted the var type to int.

Heres is my now working code:

var result = command.ExecuteScalar();
int i = Convert.ToInt32(result);    
if (i == 0)
{
    lblMessage.Text = "Person does not exist";
}
else
{
    lblMessage.Text = "Person exists";
}

score 1 · Answer 2 · answered Apr 22 '14 at 09:43

1

I suspect the issue is that it is returning a long or similar. Try using:

int userCount = Convert.ToInt32(command.ExecuteScalar());

If that doesn't work: ask what it is:

object val = command.ExecuteScalar()
Debug.WriteLine(val.GetType().FullName);

answered Apr 22 '14 at 09:43

Marc Gravell

1,026,079
266
2,566
2,900

out of curiosity: if the query returns `long` value why this statement `(int)command.ExecuteScalar();` does not work? this statement can convert/cast the long value into integer right? – Sudhakar Tillapudi Apr 22 '14 at 10:07
here http://stackoverflow.com/questions/1608801/difference-between-convert-toint32-and-int/5132368#5132368 : Your answer helped me, Thanks – Sudhakar Tillapudi Apr 22 '14 at 10:12
1

@SudhakarTillapudi the `(int)command.ExecuteScalar()` is an unbox operation; unbox must be to the correct type (or at least: the correct sized type, in the case of enums). You cannot unbox a `long` to an `int`. You *could*, however, do `=(int)(long)command.ExecuteScalar()` - i.e. unbox to a `long` and then cast the `long` to an `int`. – Marc Gravell Apr 22 '14 at 12:15

score 0 · Answer 3 · answered Apr 22 '14 at 10:25

0

Please try this piece of code,

object s = sql.ExecuteScalar();
if (s != null)
{
    Console.WriteLine("Query returned value.");
}
else
{
    Console.WriteLine("Query returned nothing.");
}

answered Apr 22 '14 at 10:25

UvarajGopu

27
9

It is unclear how this helps; from the exception message, we can deduce that this is not a `null` value (it would have been a `NullReferenceException` if that were the case) – Marc Gravell Apr 22 '14 at 12:16
I not getting your question clearly. Why the Exception comes into the picture. sql.ExecuteScalar() will return a object in case if the row exists based on SqlCommand we passed. It will return null, in case database doesn't contains row. – UvarajGopu Apr 22 '14 at 14:21
Sorry for my late answer. Your code does not work since s is never null (it always has value 0, 1, 2... depending on how many rows it finds). Thanks anyway for your help – user2939293 Apr 22 '14 at 15:34
Try after deleting all the rows in the table. That will give you null. I tried locally and the answered you. – UvarajGopu Apr 22 '14 at 18:21

score -2 · Answer 4 · answered Apr 22 '14 at 09:49

-2

command.ExecuteScalar();will return The first column of the first row in the result set, or a null reference if the result set is empty.

it could not result int value

do it like this

if(command.ExecuteScalar() != null){
lblMessage.Text = "Person already exists";
}

answered Apr 22 '14 at 09:49

sreenivasulu g

7
1
4

1

That's wrong. SELECT COUNT(*) returns zero if no match not null. – Steve Apr 22 '14 at 09:50
Yes that's correct, I tried the above code and it always returns a number (0,1,2...). – user2939293 Apr 22 '14 at 15:52

Check if row exists in database

4 Answers4