SELECT COUNT Query Error

Question

I've got this code, I'm trying to count Rows with specific payrollno in an accessdatabase datatable [Holiday].

string CountHolidayQuery = "SELECT COUNT FROM [Holiday] WHERE PayrollNo = @PayrollNo";
var CountHoliday = new OleDbCommand(CountHolidayQuery, conn);
CountHoliday.Parameters.AddWithValue("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
int TotalHolidays = Convert.ToInt32(CountHoliday.ExecuteNonQuery());

I'm getting this error but I thought I had added the parameters?

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll Additional information: No value given for one or more required parameters.

score 3 · Answer 1 · answered Mar 18 '16 at 14:05

3

The correct syntax for COUNT is

string CountHolidayQuery = @"SELECT COUNT(*) 
         FROM [Holiday] WHERE PayrollNo = @PayrollNo";
var CountHoliday = new OleDbCommand(CountHolidayQuery, conn);
CountHoliday.Parameters.Add("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
int TotalHolidays = Convert.ToInt32(CountHoliday.ExecuteNonQuery());

Notice also that I have changed the call to AddWithValue with the correct Add for the parameters passed. Consider that AddWithValue should be used with a lot of attention because it is know as a source for subtle bugs

answered Mar 18 '16 at 14:05

Steve

213,761
22
232
286

1

Posted a link to a famous blog article that explain the problematic behavior of AddWithValue. In essence. AddWithValue cannot know what is the datatype of the receiving field. So it uses the datatype of the parameter passed. Then the database engine need to convert it back to the datatype expected by the field. These conversions are all critical to get a correct result. Sometime they don't work as you think (particularly with dates and decimal/floating point values) – Steve Mar 18 '16 at 14:12

score 3 · Accepted Answer · edited Mar 18 '16 at 14:24

3

Try

string CountHolidayQuery = 
   @"SELECT COUNT(*) 
       FROM [Holiday] 
      WHERE PayrollNo = @PayrollNo";

int TotalHolidays;

using (var CountHoliday = new OleDbCommand(CountHolidayQuery, conn)) {
  CountHoliday.Parameters.Add("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
  TotalHolidays = Convert.ToInt32(CountHoliday.ExecuteNonQuery());
}

edited Mar 18 '16 at 14:24

Dmitry Bychenko

180,369
20
160
215

answered Mar 18 '16 at 14:07

Dewi Jones

785
1
6
18

SELECT COUNT Query Error

2 Answers2