How to recognize empty result from SQL

Question

When my select command does not have any result, it throws an exception. Instead I want to continue with the process without exception. How to control this by checking whether there is data retrieved from the select statement?

Here is my query:

sqlid = new SqlCommand("SELECT TOP 1 Proj_id FROM Proj_details WHERE Front_end = '" + strfrontend + "'ORDER BY Date_time desc", con);
id = sqlid.ExecuteScalar().ToString();

I'm necroing this because people have pointed out that this is vulnerable to SQL Injection, but no one told the OP how to prevent it. Here's a good explanation of how to use parameterized queries to prevent SQL Injection. https://csharp-station.com/Tutorial/AdoDotNet/Lesson06 — ristonj, Mar 23 '20 at 12:59

score 4 · Answer 1 · edited May 23 '17 at 10:34

4

Using exceptions to control program flow (as suggested by other answers) is the wrong solution. If its acceptable to return no results, check the value returned from ExecuteScalar() before calling ToString() on it.

By definition, ExecuteScalar will return null if there are no results.

object val = sqlid.ExecuteScalar();
if( val != null ){
    id = val.ToString();
}else{
    // do something (or nothing) and continue
}

See also: ExecuteScalar throws NullReferenceException

edited May 23 '17 at 10:34

Community

1
1

answered Aug 08 '12 at 06:44

Tim Medora

51,363
11
111
149

hi tim, thanks for you explanation and the link to the other question, never thought to check the if the value was null... i guess that solves the problem as well. – wterbeek Aug 08 '12 at 06:56
Thanks Tim, the down vote was deserved as it was bad practice. – wterbeek Aug 08 '12 at 07:02

score 0 · Answer 2 · answered Aug 08 '12 at 06:45

Try to use ExecuteReader instead of ExecuteScalar and handle empty result this way:

SqlCommand sqlid = new SqlCommand("SELECT TOP 1 Proj_id FROM Proj_details WHERE Front_end = '" + strfrontend + "'ORDER BY Date_time desc", con);

SqlDataReader dr = sqlid.ExecuteReader();
if (dr.Read())
{
 id  = dr[0].ToString();
}
else
{
    // handle empty result here
}

score 0 · Answer 3 · answered Jul 24 '17 at 07:24

0

Will this suffice?

 SqlDataReader rd = cmdLoad4.ExecuteReader();
 if (rd.HasRows)
{
// input what you want.
}
else
{
//input the exception
}

answered Jul 24 '17 at 07:24

Noobster

93
10

score -1 · Answer 4 · answered Aug 08 '12 at 06:41

-1

Would a try-catch block not work?

    try
    {
        //Execute your SQL-statement here        
    }
    catch(SqlException)
    {
        //If you want something specific to happen when the
        //exception is thrown, put that here.
    }

http://msdn.microsoft.com/en-us/library/0yd65esw(v=vs.80).aspx

answered Aug 08 '12 at 06:41

user1582511

1
2

-1 using exceptions to control program flow is poor practice, especially when the exception is so easy to prevent. – Tim Medora Aug 08 '12 at 06:49

score -1 · Answer 5 · answered Aug 08 '12 at 06:43

-1

encapsulate it with a try/catch statement :

try
{
    sqlid = new SqlCommand("SELECT TOP 1 Proj_id FROM Proj_details 
                           WHERE Front_end = '" + strfrontend 
                           + "'ORDER BY Date_time desc", con
                           ); 
    id = sqlid.ExecuteScalar().ToString();
}
catch(exception)
{
   // do something with the exception
}

answered Aug 08 '12 at 06:43

wterbeek

431
9
26

1

-1 using exceptions to control program flow is poor practice, especially when the exception is so easy to prevent. – Tim Medora Aug 08 '12 at 06:48

How to recognize empty result from SQL

5 Answers5