• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2500
  • Last Modified:

How to return the number of rows affected by a stored procedure

The stored procedure executes in SQL Manager and returns 30 rows.

However int intRowsReturned = cmd.ExecuteNonQuery();
returns -1.

What am I missing?

Thanks


SqlCommand cmd = new SqlCommand("usp_getPriceIncreases", conStockSelect);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "usp_getPriceIncreases";
        cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = strFromDate;
        cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = strToDate;
        cmd.Parameters.Add("@Increases", SqlDbType.Int).Value = intIncreases;
        cmd.Parameters.Add("@MarketID", SqlDbType.Int).Value = lstMarket.SelectedValue;
        cmd.Connection = conStockSelect;
        int intRowsReturned = cmd.ExecuteNonQuery();
 
stored Procedure
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT SYM.SymbolID,SYM.SymbolName,SYM.SecName  
	, Count(*) AS Increases  
	FROM StockHist AS HIST INNER JOIN
    Symbol AS SYM ON HIST.SymbolID = SYM.SymbolID 
	WHERE 
	SYM.MarketID = @MarketID  
	AND HIST.ClosePrice >=ClosePricePrev 
	AND HIST.QuoteDate >= @FromDate 
	AND HIST.QuoteDate <= @ToDate 
	GROUP BY SYM.SymbolID,SYM.SymbolName,SYM.SecName  
	Having count(*) > @Increases  
	ORDER BY Increases DESC 
	END

Open in new window

0
Dovberman
Asked:
Dovberman
  • 10
  • 8
  • 3
  • +2
1 Solution
 
Ken FayalCommented:
ExecuteNonQuery is only for Inserts updates and deletes. You need to run ExecuteReader.

The query shown doesn't really "affect" records, it's only reading them.  So you need to return the data with ExecuteReader.

Hope that helps.
0
 
DhaestCommented:
You can still use the ExecuteNonQuery.
You need to adjust your stored procedure that it returns the number of affected records:
Stored procedure:
update ...
Return @@Rowcount
0
 
DhaestCommented:
Previous remark was for updating, deleting and inserting, but apparantly you perform a select :)

Do you want to retrieve the data or do you just want to know how many records there are ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
havj123Commented:
Generally ExecuteNonQuery() used for Insert and Update statement. It always return -1 when you use it with Select statement.

Either use ExecuteReader() or return @@RowCount in your stored procedure.
0
 
DovbermanAuthor Commented:
I have a GridView that is based on a DataSource control which is based on the stored procedure.

The GridView displays the proper number of rows.

I need the rowcount for a label text to inform users of the number of rows returned.

Is there a way to get the rowcount as a dataset or gridview property or method?

How do I return @@RowCount in my stored procedure?

Thanks,

0
 
DhaestCommented:
If you have everything in a dataset, you can just take the count :)

DataSet.Tables("YourTableName").Rows.Count
0
 
Ken FayalCommented:
You can also do GridView.Rows.Count
0
 
OmerFarukZCommented:
change  SET NOCOUNT ON; to  SET NOCOUNT OFF;
0
 
DovbermanAuthor Commented:
OK, I can ExecuteReader

int rowsSelected
SqlDataReader rdrStockHist;
       
        rdrStockHist = cmd.ExecuteReader();
        rdrStockHist.Read();

     intRowsSelected = ???

What is the syntax for returning the number of rows selected.
0
 
Ken FayalCommented:
You can't get that from the rdrStockHist.  The best way to do it is to populate your gridview and then get the number of rows from the gridview control and put it in the text of your label control.

[your GridView name].Rows.Count

0
 
DovbermanAuthor Commented:
The gridview is paged.

[your GridView name].Rows.Count returns only the number of rows per page.

I need to loop thru the DataReader

rdrStockHist.Read();
What is the proper syntax for looping thru the rows?
int intCtr = 0
?? for each row
          intCtr = intCtr +=1




0
 
DhaestCommented:
From where does you data come into the gridview ? If it's coming from a dataTable, you can get the number of rows from there ! (DataSet.Tables("YourTableName").Rows.Count)
0
 
DovbermanAuthor Commented:
<<I have a GridView that is based on a DataSource control which is based on the stored procedure.>>

I believe that the data is comming from a datareader.
  // Try to open database and read information.
        SqlCommand cmd = new SqlCommand("usp_getPriceIncreases", conStockSelect);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "usp_getPriceIncreases";
        cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = strFromDate;
        cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = strToDate;
        cmd.Parameters.Add("@Increases", SqlDbType.Int).Value = intIncreases;
        cmd.Parameters.Add("@MarketID", SqlDbType.Int).Value = lstMarket.SelectedValue;
        cmd.Connection = conStockSelect;

        SqlDataReader rdrStockHist;
       
        rdrStockHist = cmd.ExecuteReader();
        int intRowCounter = 0;
        if (rdrStockHist.HasRows)
        {
            while (rdrStockHist.Read())
            {
                intRowCounter = intRowCounter + 1;
            }
        }

The value of intRowCounter is only 11. The actual number of rows returned by the Stored Procedure is 24.

Does this mean that I must use a DataSet?
0
 
DhaestCommented:
Did you check the following member of rdrStochHist:

//Execute reader
rdrStockHist = cmd.ExecuteReader();

//Get RecordsAffected
RecordsAffected = rdrStockHist .RecordsAffected;
0
 
DovbermanAuthor Commented:
rdrStockHist .RecordsAffected;
Returns -1 as expected.
0
 
DhaestCommented:
I was afraid for that ...

The problem is that I don't see any problem with the code you have written ...
if (rdrStockHist.HasRows)
        {
            while (rdrStockHist.Read())
            {
                intRowCounter = intRowCounter + 1;
            }
        }
0
 
DhaestCommented:
A reader doesn't have a count. You will need to read all the records and
keep track of count yourself.

Better to use a Command.ExecuteScalar() or to use a dataAdaptor and load
your records into a DataSet or Table and get your count there.

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("SELECT Count(*) FROM MyTable", cn))

{

cn.Open();

cm.CommandType = CommandType.Text;

int i = (int)cm.ExecuteScalar();

cn.Close();

return i;

}
0
 
OmerFarukZCommented:
int rowsSelected
SqlDataReader rdrStockHist;
       
        rdrStockHist = cmd.ExecuteReader();
        rdrStockHist.Read();

     intRowsSelected = ???


change code to following code

int rowsSelected
SqlDataReader rdrStockHist;
       
        rowsSelected = cmd.ExecuteReader();
        rdrStockHist.Read();

and open your stored procedure for changing "set nocount on" line to  "set nocount off"


cool coding.


0
 
DovbermanAuthor Commented:
adpStockHist.SelectCommand = cmd;
        adpStockHist.Fill(dstStockHist, "QuoteHistory");
        int intRowCounter = dstStockHist.Tables["QuoteHistory"].Rows.Count;

Also returns 11.

How do I loop thru the rows of the dataset?
0
 
DhaestCommented:
Where did you get the value of 24 than ? Because on 2 ways you find 11 :)


DataTable dp= dataset1.Tables["Table1"];
 
foreach (DataRow da in dp.Rows)
{
        MessageBox.Show(da["datacolumn"].ToString());
 
}

Open in new window

0
 
DovbermanAuthor Commented:
I get 54 when I run the stored procedure in SQL Server Manager using the same parameter values.
0
 
DovbermanAuthor Commented:
Here is what I have when I run the stored procedure
RowCount.jpg
0
 
DovbermanAuthor Commented:
It is possible that an agregate query does not support the use of the rows.Count property. I decided to ask for a sql statment that returns only a single row that displays the number of stocks that meet the criteria.

sharath 123 supplied the syntax.

SELECT COUNT(Increases) Count_Increases FROM (
SELECT SYM.SymbolID,SYM.SymbolName,SYM.SecName  
      , Count(*) AS Increases  
      FROM StockHist AS HIST INNER JOIN
    Symbol AS SYM ON HIST.SymbolID = SYM.SymbolID
      WHERE
      SYM.MarketID = @MarketID  
      AND HIST.ClosePrice >=HIST.ClosePricePrev
      AND HIST.QuoteDate >= @FromDate
      AND HIST.QuoteDate <= @ToDate
      GROUP BY SYM.SymbolID,SYM.SymbolName,SYM.SecName  
      Having count(*) > @Increases ) t1
0
 
DovbermanAuthor Commented:
The row counting snippets suggested that loop thru the rows work.
Please excuse the confusion.

I was testing a date range in the code that was 3 days different than given when executing the stored procedure in SQL Server Manager.

This works:
        {
            while (rdrStockHist.Read())
            {
                intRowCounter = intRowCounter + 1;
            }
        }
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 10
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now