3

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             A@gmail.com
 B             A@gmail.com
 B             C@gmail.com

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

MethodMan
  • 18,625
  • 6
  • 34
  • 52
Michael Chi
  • 31
  • 1
  • So immediately after you do the `.Fill` is `info.Rows.Count` returning 2? – Gord Thompson Sep 02 '16 at 23:21
  • 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](http://www.connectionstring.com) look for MS Access `info.Clear();` this in not even necessary.. – MethodMan Sep 02 '16 at 23:44
  • @Gord Thompson Yes Rows.Count only return 2. – Michael Chi Sep 03 '16 at 00:32
  • @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 Sep 03 '16 at 00:34
  • what if you set a breakpoint on this line `dataAdapter.Fill(info);` use the QuickWatch in the debugger and see if the rows are all returned when you hover over `info` – MethodMan Sep 03 '16 at 01:01
  • Can you post the connection string.What Jet Library you use? – M.Hassan Sep 03 '16 at 17:47
  • @MethodMan I have tried debugging the application using a breakpoint. After the datatable was filled I added 'info' to the watch. It only has 2 rows, but the same sql statement in access returns 3. – Michael Chi Sep 04 '16 at 15:29
  • 1
    This sounds very odd.. I have never experienced this before.. – MethodMan Sep 05 '16 at 00:17
  • @MichaelChi Did you ever find resolution for this issue? I am experiencing it today. – EllieK Apr 03 '20 at 12:59

1 Answers1

0

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.

EllieK
  • 259
  • 4
  • 14