locked
Need to check for null return on ExecuteNonQuery() RRS feed

  • 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();
                    }
    
                }
            }
    Tuesday, January 10, 2012 7:33 AM

Answers

  • 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;
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 10, 2012 7:47 AM

All replies

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

    Tuesday, January 10, 2012 7:38 AM
  • 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;
    }


    Tuesday, January 10, 2012 7:40 AM
  • 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,

    Tuesday, January 10, 2012 7:43 AM
  • 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;
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 10, 2012 7:47 AM
  • 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)

    Tuesday, January 10, 2012 7:48 AM
  • 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.

    Tuesday, January 10, 2012 7:53 AM