none
SqlCommand.ExecuteNonQuery() always returns -1 RRS feed

  • Question

  • I am using these two methods

     public static int execNonQuery(string storedProcName, params SqlParameter[] sqlParameters)
        {
          SqlConnection cn = UseDBConnect();
          SqlTransaction trans = cn.BeginTransaction();
    
          try
          {
            SqlCommand cmd = new SqlCommand(storedProcName, cn, trans);
            cmd.CommandType = CommandType.StoredProcedure;
            
            foreach (SqlParameter _param in sqlParameters)
            {
              cmd.Parameters.Add(_param);
            }
    
            int rowsAffected = cmd.ExecuteNonQuery();
            trans.Commit();
    
            return rowsAffected;
          }
          catch (Exception ex)
          {
            trans.Rollback();
            throw ex;
          }
          finally
          {
            if (cn != null)
            {
              cn.Dispose();
            }
            if (trans != null)
            {
              trans.Dispose();
            }
          }
        }
    
        public static SqlParameter getSqlParameter(string paramName, object sqlDbValue, SqlDbType sqlDbType)
        {
          SqlParameter param = new SqlParameter();
          param.ParameterName = paramName;
    
          try
          {
            if (sqlDbValue != null)
            {
              param.Value = sqlDbValue;
            }
            else
            {
              param.Value = DBNull.Value;
            }
            param.SqlDbType = sqlDbType;
          }
          catch (Exception ex)
          {
            throw ex;
          }
          return param;
        }

    in this manner

     int x = Omega.Omega.ExecuteNonQuery("updateCommon",
                              Omega.Omega.GetSQLParameter("@siteTitle", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[2].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@siteDescr", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[3].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@siteKeywords", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[4].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@companyName", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[5].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@companySite", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[6].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@footerInfo", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[7].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@langCode", ((TextBox)grdCommon.Rows[e.RowIndex].Cells[8].Controls[0]).Text, SqlDbType.NVarChar),
                              Omega.Omega.GetSQLParameter("@id", Convert.ToInt16(grdCommon.DataKeys[0].Value.ToString()) , SqlDbType.Int));

    but the result I get is always -1, but the data are always updated in the database.

     

    Any ideas why?!?!?!?

     

    Friday, February 25, 2011 10:33 AM

Answers

  • please set NOCOUNT ON in your StoredProcedure
    【孟子E章】

    I believe you ment set nocount OFF!!!! Setting it to OFF I was able to retrieve the number of rows affected
    • Marked as answer by how_you_doing Friday, February 25, 2011 1:45 PM
    Friday, February 25, 2011 1:44 PM

All replies

  • please set NOCOUNT ON in your StoredProcedure
    【孟子E章】
    Friday, February 25, 2011 1:00 PM
  • its on by default...doesnt work
    Friday, February 25, 2011 1:21 PM
  • this is my sp

    USE [CRMLite]
    GO
    /****** Object: StoredProcedure [dbo].[updateCommon]  Script Date: 02/25/2011 15:22:43 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    
    GO
    -- ==========================================
    -=============================================
    ALTER PROCEDURE [dbo].[updateCommon] 
    	-- Add the parameters for the stored procedure here
    @siteTitle nvarchar(50), 
    @siteDescr nvarchar(50), 
    @siteKeywords nvarchar(50),
    @companyName nvarchar(50),
    @companySite nvarchar(50), 
    @footerInfo nvarchar(50), 
    @langCode nvarchar(3), 
    @id int
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    UPDATE [CRMLite].[dbo].[common]
    	  SET [site_title] = @siteTitle
    		 ,[site_description] = @siteDescr
    		 ,[site_keywords] = @siteKeywords
    		 ,[company_name] = @companyName
    		 ,[company_site] = @companySite
    		 ,[footer_info] = @footerInfo
    		 ,[lang_code] = @langCode
    		WHERE id = @id
    END
    
    

    Friday, February 25, 2011 1:40 PM
  • please set NOCOUNT ON in your StoredProcedure
    【孟子E章】

    I believe you ment set nocount OFF!!!! Setting it to OFF I was able to retrieve the number of rows affected
    • Marked as answer by how_you_doing Friday, February 25, 2011 1:45 PM
    Friday, February 25, 2011 1:44 PM