CODEWITHSUNDEEP
c#oledb
Amvin Cinco
Amvin Cinco

Reputation: 11

"Data type mismatch in criteria expression" in C#

I have a numeric "userID" in a string variable. I am converting it to int to get "username" and "roles" from the database which has a "user_ID" field with an AutoNumber datatype. The converted userID is assigned to the variable "value". But I am getting "Data type mismatch in criteria expression" in the line:

OleDbDataReader roleReader = command.ExecuteReader();

Here is the full code

class HomeClass
{
    string userID;
    string role;
    string username;

    public HomeClass(string myUserID)
    {
        userID = myUserID;
    }

    public string checkRole()
    {
        int value;
        value = Int32.Parse(userID);
        string query = "SELECT role FROM userAccounts WHERE user_ID = '" + value + "'";
        ConnectDatabaseString myConnectionString = new ConnectDatabaseString();
        OleDbConnection connection = new OleDbConnection();
        connection.ConnectionString = myConnectionString.connect();
        connection.Open();

        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = query;
        OleDbDataReader roleReader = command.ExecuteReader();
        roleReader.Read();
        role = roleReader["roles"].ToString();
        return role;
    }

    public string getUsernameToBeShown()
    {
        int value;
        value = Int32.Parse(userID);
        string query = "SELECT username FROM userAccounts WHERE user_ID = '" + value + "'";
        ConnectDatabaseString myConnectionString = new ConnectDatabaseString();
        OleDbConnection connection = new OleDbConnection();
        connection.ConnectionString = myConnectionString.connect();
        connection.Open();

        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = query;
        OleDbDataReader usernameReader = command.ExecuteReader();
        usernameReader.Read();
        username = usernameReader["username"].ToString();
        return username;
    }
}

What is the root cause of this error?

Upvotes: 0

Views: 8910

Answers (2)

Justin
Justin

Reputation: 3397

You are using a string literal in your SQL query. EG 'value' ' means the datatype is a string. Remove this and you shouldn't have any issues.

Upvotes: 1

SLaks
SLaks

Reputation: 887453

In your SQL statement, you're comparing user_ID to a string literal (in quotes).

You want to compare it to a number.

Upvotes: 0

Related Questions