Data type mismatch

Question

I got this error when I tried to select userid from a database to datatable. The first userid is an autonumber, the second USERID is a number, and the database is a MS Access DB.

private void ()
{
    OdbcDataAdapter ad = new OdbcDataAdapter("select userid from userinfo where BadgeNumber='" + UserID + "'", this.FM.Cn);
    DataTable t = new DataTable();
    ad.Fill(t);
    ad.Dispose();
    if (t.Rows.Count > 0)
    {
        OdbcCommand cmd = new OdbcCommand();
        cmd.Connection = this.FM.Cn;

        string id = t.Rows[0][0].ToString();
        //Check Date
        OdbcDataAdapter add = new OdbcDataAdapter("Select USERID from checkinout where Userid='" + id + "'", this.FM.Cn);
        DataTable tc = new DataTable();
        add.Fill(tc); // <- I gotta error here.
        add.Dispose();
    }
}

Answer 1

Change your query to:

"Select USERID from checkinout where Userid=" + id

In SQL queries, strings literals (or chars) are required to be enclosed within a pair of single quotes ', which are used to delimiter the string. A delimiter is pretty much a character used to identify boundaries - in the case of a string, the single quotes specify where the string starts and where it ends.

Because of the nature of numbers (integers for example), it is not necessary to indicate a delimiter such as single quotes. Your code was failing because when the database engine saw the single quotes, it was expecting a string, but your column was a number datatype, and this is why you obtained a Data type mismatch error when executing your query.