ExecuteScalar?
-
-
I am using Execute Scalar for a COUNT on a query and on the C# side I get a count of 1. Using the same query on the SQL side I get 0 results and (1 row affected). How does ExecuteScalar work, I always thought it grabs only the result of the count...
Kuira wrote:
How does ExecuteScalar work, I always thought it grabs only the result of the count
ExecuteScalare creates a Data Reader, and gets the value of the first column of the first row. Then closes the data reader. If any other data is available it is ignored. If there is no data at all then it returns null, if the value of the first column of the first row is a null then System.DBNull.Value is returned. The query sent to ExecuteScalar can be any SELECT query that you like, although obviously only queries that return one row with one column are best. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
Kuira wrote:
How does ExecuteScalar work, I always thought it grabs only the result of the count
ExecuteScalare creates a Data Reader, and gets the value of the first column of the first row. Then closes the data reader. If any other data is available it is ignored. If there is no data at all then it returns null, if the value of the first column of the first row is a null then System.DBNull.Value is returned. The query sent to ExecuteScalar can be any SELECT query that you like, although obviously only queries that return one row with one column are best. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
I am using Execute Scalar for a COUNT on a query and on the C# side I get a count of 1. Using the same query on the SQL side I get 0 results and (1 row affected). How does ExecuteScalar work, I always thought it grabs only the result of the count...
Well, in theory anyway, your query worked. What you got back in the ExecuteScalar method is a dataset with two result sets. The first being a 1 row, 1 column table that returned the number of rows affected, 1. The second set, which ExecuteScalar always ignores, contained a 1 row, 1 column table with the value 0 in it. This is the actuall restult of your query. To fix this, make sure you include "SET NOCOUNT ON" in your SQL batch:
string mySqlStatement = @"SET NOCOUNT ON;SELECT COUNT(column) FROM table WHERE condition;SET NOCOUNT OFF";
If your executing a stored procedure to return the code, put the NOCOUNT statements in the procedure, not your code. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 0:33 Thursday 5th January, 2006