select query does not work with parameters using Parameters.AddWithValue

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.

Jon Skeet
people
quotationmark

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.

people

See more on this question at Stackoverflow