Getting Data from Access into a text box in C# by clicking a button

Question

I have a table in MS Access that contain: (FoodID, FoodName, Price).

In C# I have three text boxes (txtId, txtName, txtPrice) and a button (btnSearch). My question is that, In C# I just type FoodID in (txtId) and then click on button Search It'll display FoodName and Price ( from table access) in txtName and txtPrice by itself. I got the source code from you but it error on (OleDbDataReader dr = cmd.ExecuteReader();) its message is "Data type mismatch in criteria expression" .

Please solve this problem for me. This is the whole source code that I got for you.

System.Data.OleDb.OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "your connection string";
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "select FoodName, Price from tablename where FoodID = '" + txtId + "' ";
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();//error this line!
while(dr.Read())
{
     txtName.Text = dr["FoodName"].ToString();
     txtPrice.Text = dr["Price"].ToString(); 
}
dr.Close();
conn.Close();

score 4 · Answer 1 · answered Jun 25 '11 at 03:12

I assume FoodID is int. You should remove single quotes in this case

cmd.CommandText = "select FoodName, Price from tablename where FoodID = " + txtId;

Even better - use parameters:

using (var connection = new OleDbConnection("your connection string"))
using (var command = connection.CreateCommand())
{
    command.CommandText = "select FoodName, Price from tablename where FoodID = @FoodID";
    command.Parameters.AddWithValue("FoodID", int.Parse(txtId.Text));
    connection.Open();
    var reader = command.ExecuteReader();
    while (reader.Read())
    {
        txtName.Text = reader["FoodName"].ToString();
        txtPrice.Text = reader["Price"].ToString();
    }
}

@Sophorn - please mark the answer if it worked. If it didn't work exaplain the problem. — Alex Aza, Jun 25 '11 at 23:43
the solution you have provided was exactly what I was looking for. thanks. — bucketblast, Mar 13 '13 at 18:18

score 0 · Answer 2 · answered Jun 25 '11 at 03:17

I think the FoodId is of Integer type in the database but over here in the query you have passed as string so convert the string to integer.

cmd.CommandText = "select FoodName, Price from tablename where FoodID = '" + int.Parse(txtId.Text) + "' " ;

There seems to be no problem with this line of code :

OleDbDataReader dr = cmd.ExecuteReader();// correct way

score 0 · Answer 3 · answered Jun 25 '11 at 03:34

I think the problem is in:

cmd.CommandText = "select FoodName, Price from tablename where FoodID = '" + txtId + "' ";

You need to use the .Text Propertie of the Textbox

cmd.CommandText = "select FoodName, Price from tablename where FoodID = '" + txtId.Text + "' ";

Getting Data from Access into a text box in C# by clicking a button

3 Answers3

Linked