asp.net oledbcommand return all rows

Question

I am using Oledbconnection to connect to a Microsoft Access database, and I am using OleDbCommand to retrieve some information. I have a query in the database called retrieveInfo, which retrieves 3 rows of data. There are some duplicates in the fields but that's how it's supposed to be. My data looks like this:

 Name          Email
 A             [email protected]
 B             [email protected]
 B             [email protected]

My C# code behind looks like this:

DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    string query = "SELECT * FROM retrieveInfo";
    try
    {
        conn.Open();
        DataTable info = new DataTable();
        OleDbCommand command = new OleDbCommand(query, conn);
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
        info.Clear();
        dataAdapter.Fill(info);
    }

I ran the query retrieveInfo in MS Access and it returned 3 rows like shown above. However when I run this command using C# and loaded the data into a datatable, it only shows 2 rows. The datatable only has 1st and 2nd row. I don't know if this has anything to do with the original table properties, or is my C# code wrong? I also tried using a data reader, execute reader and using a while loop to read data. But it also only return 2 rows.

Any help would be appreciated!

Thank you

So immediately after you do the .Fill is info.Rows.Count returning 2? — Gord Thompson
you're missing your closing } for your using btw.. what's your connection string look like.. check this site for how to configure C# Connection Strings look for MS Access info.Clear(); this in not even necessary.. — MethodMan
@MethodMan Thank you for the reminder. I have checked my connection string and it is correct. Because when I access it using query that doesnt return duplicate field it works perfectly fine. So that's why I was wondering if it has something to do with returning rows with duplicate values? — Michael Chi
This sounds very odd.. I have never experienced this before.. — MethodMan

EllieK EllieK · Accepted Answer · 2020-04-08T15:56:08

Chances are you are using a 'LIKE' statement somewhere in retrieveInfo's definition, either in that query itself or in one of its sub-queries. OleDbDataAdapter cannot resolve MS Access LIKE statement as it's written in Access SQL. You need to use ANSI LIKE (see link).

Stackoverflow Explanation

Now I use

Like "oldmcdonal%" Or Like "oldmcdonal*"

This provides the expected result set regardless of whether it's executed in MS Access or via .net code.

asp.net oledbcommand return all rows

1 Answers