CODEWITHSUNDEEP
c#mysql
user3463171
user3463171

Reputation: 81

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.

Upvotes: 3

Views: 4973

Answers (3)

David
David

Reputation: 1

I know this thread is old, but I wanted to share my solution for anyone else coming up on this.

I was having issues with the typical method that Jon posted. I have used it before, but for some reason with this new string I had it was not wanting to actually place the parameter correctly and was causing the reader to not work.

I ended up doing something like this instead, since in the end we are just replacing parts of a string.

string sql = "select * from user_tbl where emp_id = "+ var1 +" and birthdate = "+ 
var2""
OdbcCommand command = new OdbcCommand(sql);

This was easier for me to get to work. Be warned though, I am not sure if it has any specific drawbacks when compare to using the command parameter method.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1504172

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.

Upvotes: 5

sujith karivelil
sujith karivelil

Reputation: 29036

Use like this:

string Getquery = "select * from user_tbl where emp_id=? and 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();
while(Reader.Read())
{ 
  //Do something;
}

Upvotes: 0

Related Questions