MS Access Query "WHERE" Exception

Question

I am writing an application in C# that reads info from a .mdb file using the System.Data.OleDb namespace. The table I am reading from contains information about a sample (number, index, date, etc). The number and date are required entries when a new item is added to the table, but the index is optional, so some samples do not have an index. I can run a query just fine using the following code:

string id = "99";
OleDbConnection myConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\...\Database\data.mdb");
myConnection.Open();
OleDbCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT SampNo, Index, DateTime FROM Sample WHERE SampNo=" + id;
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
    Console.WriteLine("\t{0}\t{1}\t{2}", myReader["SampRef"], myReader["Index"], myReader["DateTime"]);
}

However, I actually want to query the table and retrieve samples based on an index value, so I am trying to do so by substituting the query line with:

myCommand.CommandText = "SELECT SampNo, Index, DateTime FROM Sample WHERE Index=" + id;

But when I run it, it throws an OleDbException at the "myCommand.ExecuteReader()" line that says "Data Type mismatch in criteria expression".

I am not super fluent with database queries, so I am assuming there is another way to run this query that will work. Any thoughts??

Answer 1

Is the Index field a number or text field?

If it's a text field, you would need to do this:

myCommand.CommandText = "SELECT SampNo, Index, DateTime FROM Sample WHERE Index='" + id + "'";

You're better off using a parameterized query instead though, as this opens you up to problems with indexes containing apostrophes, or even worse, injection attacks.

Answer 2

The error, "Data Type mismatch in criteria expression" means that you have a data type in the query that does not match the type of the field.

If the field is type Text (and say it contains numbers) and you had a query saying:

SELECT * FROM myTable WHERE myField=2;

...you would get this error. you would have to run it with quotes around the number:

SELECT * FROM myTable WHERE myField='2';

The opposite case (searching for text in a field of type Number) would give you the same error.

Answer 3

Both Index and DateTime are reserved words. If there are really field (column) names, you should, even must, put them in square brackets ([]) and try to change them at the earliest opportunity.