Data type mismatch in criteria expression(Convert.ToInt32(cmd.ExecuteScalar());)

Question

I am trying to Display a name in the textbox from the database if the ID entered by the user matches the record in the MS ACCESS DATABASE. I'm getting the error Data type mismatch in criteria expression at the line int count = Convert.ToInt32(cmd.ExecuteScalar());

The following is my aspx.cs code-

protected void Button1_Click(object sender, EventArgs e)
{
    clear();
    idcheck();

    DataTable dt = new DataTable();
    OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\dfg\fd\Visual Studio 2010\WebSites\WebSite21\App_Data\UPHealth.mdb");

    con.Open();

    str = "SELECT [DoctorName] FROM [DoctorInfo] WHERE DoctorID='" + TextBox1.Text.Trim() + "'";

    OleDbCommand cmd = new OleDbCommand(str, con);
    OleDbDataReader dr = cmd.ExecuteReader();
    if (dr.Read())
    {
        TextBox2.Text = dr["DoctorID"].ToString();
        dr.Close();
        con.Close();
    }
}

public void idcheck()
{
    OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\dfg\fd\Visual Studio 2010\WebSites\WebSite21\App_Data\UPHealth.mdb");
    con.Open();
    str = "SELECT count(DoctorName) FROM [DoctorInfo] WHERE DoctorID='" + TextBox1.Text.Trim() + "'";
    OleDbCommand cmd = new OleDbCommand(str, con);
    int count = Convert.ToInt32(cmd.ExecuteScalar());
    if (count > 0)
    {
        Label21.Text = "Doctor Name";
    }
    else
    {
        Label21.Text = "Id Does not Exist";
    }
}

void clear()
{
    TextBox2.Text = "";
}

Patrick Hofman Patrick Hofman · Accepted Answer · 2016-06-02T12:26:03

I guess that is because you as passing in an ID, which is usually a numeric value, as a text field:

DoctorID='" + TextBox1.Text.Trim() + "'

Which should be:

DoctorID=" + TextBox1.Text.Trim()

Another problem arises, since you are vulnerable to SQL injection. What if the text box contained 1; delete users? Then your entire users table would be empty. The lesson learned: use parameterized queries!

Then you can express the SQL as:

DoctorID= ?

And add the parameter to the request:

cmd.Parameters.AddWithValue("?", TextBox1.Text.Trim());

Data type mismatch in criteria expression(Convert.ToInt32(cmd.ExecuteScalar());)

1 Answers