select query does not work with parameters using Parameters.AddWithValue

Question

The following query in C# doesn't work, but I can't see the problem:

string Getquery = "select * from user_tbl where emp_id=@emp_id and birthdate=@birthdate";

cmdR.Parameters.AddWithValue("@emp_id", userValidate.emp_id);
cmdR.Parameters.AddWithValue("@birthdate", userValidate.birthdate);

OdbcCommand cmdR = new OdbcCommand(Getquery, conn);
OdbcDataReader Reader = cmdR.ExecuteReader();

Reader.HasRows returns no result but when I query it to my database I got data.

Answer 1

I'll assume your code is actually not quite as presented, given that it wouldn't currently compile - you're using cmdR before you declare it.

First, you're trying to use named parameters, and according to the documentation of OdbcCommand.Parameters, that isn't supported:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder.

Additionally, I would personally avoid using AddWithValue anyway - I would use something like:

string sql = "select * from user_tbl where emp_id = ? and birthdate = ?";
using (var connection = new OdbcConnection(...))
{
    connection.Open();
    using (var command = new OdbcCommand(sql, connection))
    {
        command.Parameters.Add("@emp_id", OdbcType.Int).Value = userValidate.EmployeeId;
        command.Parameters.Add("@birthdate", OdbcType.Date).Value = userValidate.BirthDate;
        using (var reader = command.ExecuteReader())
        {
            // Use the reader here
        }
    }
}

This example uses names following .NET naming conventions, and demonstrates properly disposing of resources... as well as fixing the parameter issue.

I do think it's slightly unfortunate that you have to provide a name for the parameter when adding it to the command even though you can't use it in the query, but such is life.