Need to check for null return on ExecuteNonQuery()

Question

User-780944082 posted

I need a way to check if a record exists in a table, so I can use that to run some logic on my page (See code below)

protected void GalleryGrid_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sqlConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string selectLikes = "SELECT LikedBy, picURL " + "FROM piclikes " + "WHERE LikedBy=@LikedBy and picURL=@picURL";
        string likedBy = Page.User.Identity.Name.ToString();
        string picURL = ((Label)GalleryGrid.SelectedItem.FindControl("picurlLabel")).Text;

        using (SqlConnection sqlConn = new SqlConnection(sqlConnString))
        {
            using (SqlCommand selectCommand = new SqlCommand(selectLikes, sqlConn))
            {
                try
                {
                    
                    selectCommand.Parameters.Add(new SqlParameter("@picURL", SqlDbType.NVarChar, 50));
                    selectCommand.Parameters["@picURL"].Value = picURL;
                    selectCommand.Parameters.Add(new SqlParameter("@LikedBy", SqlDbType.NVarChar, 50));
                    selectCommand.Parameters["@LikedBy"].Value = likedBy;
                    sqlConn.Open();
                    // IF a a record matching the query string is not found, do this
                    if (selectCommand.ExecuteNonQuery() != 0) 
                    {
                        LikeButton.Enabled = false;
                    }

                    //if a record is found, do this
                    else
                    {
                        LikeButton.Enabled = true;
                    }

                    selectCommand.Connection.Close();
                    sqlConn.Dispose();
                }
                catch (SqlException ex)
                {
                    StatusLabel.Text = "There was an error: " + ex.ToString();
                }

            }
        }

Answer 1

User-1199946673 posted

ExecuteNonQuery returns the number of records affected by an action query (INSERT, UPDATE, DELETE), a SELECT query will always return -1

If you want to check if a record exists, use

string selectLikes = "SELECT COUNT(*) FROM piclikes WHERE LikedBy=@LikedBy and picURL=@picURL";

.....

int recordcount = (int)cmd.ExecuteScalar();
       

Answer 2

User-451260051 posted

I think you have your logic backwards in the if/else.  ExecuteNonQuery() will return the number of rows affected, so your if should read:

if (selectCommand.ExecuteNonQuery() == 0) 
                    {
                        LikeButton.Enabled = false;
                    }
else
{
  LikeButton.Enabled = true;
}

Answer 3

User-1704326042 posted

Hello anogio,

does your code work ?

I cannot see the issue with it.

I correct myself guys, really crazy, don't know what I was drinking while writing, ExecuteNonQuery() is for query with no results. WoW I can't beleive it. Sorry everyone, really. This is to correct the false statement below.

ExecuteNonQuery() returns the number of rows affected by the query, so if 0 then, there are no records as a result of your query, if not, then there is. I would use ExecuteNonQuery() > 0 as a verification.

Another option, is to use the count function with ExecuteScalar()

I'm not sure I fully understand the context.

Kind Regards,

Answer 4

User-1935546128 posted

ExecuteNonQuery will always return -1 for SELECT statement (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). If you only want to check if any row with such conditions exists, it will be better if you use following query (it will aslo have better efficency on db side):

SELECT COUNT(1) FROM PICLIKES WHERE LikedBy = @LikedBy AND picURL = @picURL

Now you can use ExecuteScalar to achieve your goal:

//At least one row exists
if (Convert.ToInt32(selectCommand.ExecuteScalar()) != 0) 
    LikeButton.Enabled = false;
//No rows exists
else
    LikeButton.Enabled = true;

Answer 5

User-780944082 posted

I think you have your logic backwards in the if/else.  ExecuteNonQuery() will return the number of rows affected, so your if should read:

if (selectCommand.ExecuteNonQuery() == 0) 
                    {
                        LikeButton.Enabled = false;
                    }
else
{
  LikeButton.Enabled = true;
}

Ah but no, I want the button disabled if there is a record (I dont want people liking stuff twice)

Answer 6

User-780944082 posted

ExecuteNonQuery will always return -1 for SELECT statement (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). If you only want to check if any row with such conditions exists, it will be better if you use following query (it will aslo have better efficency on db side):

SELECT COUNT(1) FROM PICLIKES WHERE LikedBy = @LikedBy AND picURL = @picURL

Now you can use ExecuteScalar to achieve your goal:

//At least one row exists
if (Convert.ToInt32(selectCommand.ExecuteScalar()) != 0) 
    LikeButton.Enabled = false;
//No rows exists
else
    LikeButton.Enabled = true;

This was perfect. worked first time. Thank you, and everyone else who tried to help.