reading multi valued columns from access into c#

Question

I would like to use multivalued in access to make it easier for the user.

But when reading it into c# using oledb I get the first value followed by garbage using columnname.value gets me multiple rows of the exact same data and only that column is different which seems like a waste to me.

Is there a better way?

I know multi values are not supported in sql nor Oracle but it would make it much easier for the user so I would like to keep it if possible.

Answer 1

Is there a better way?

Yes: ODBC.

When it comes to handling multi-valued fields (created by the "Lookup Wizard" in Access), Odbc seems to do a better job than OleDb does. For test data in a table named [multiValueTest]

+----+-------------+------------+
| ID | Description | Attributes |
+----+-------------+------------+
|    |             | attribute1 |
|  1 | foo         | attribute2 |
|    |             | attribute4 |
+----+-------------+------------+

the query

SELECT Attributes FROM multiValueTest WHERE ID=1

does indeed return garbage characters when retrieved by an OleDbDataReader object.

However, that same query retrieved by an OdbcDataReader object, specifically

using (OdbcConnection con = new OdbcConnection())
{
    con.ConnectionString =
            @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
            @"Dbq=C:\Users\Public\Database1.accdb;";
    con.Open();
    using (OdbcCommand cmd = new OdbcCommand())
    {
        cmd.Connection = con;
        cmd.CommandText = "SELECT Attributes FROM multiValueTest WHERE ID=1";
        OdbcDataReader rdr = cmd.ExecuteReader();
        rdr.Read();
        Console.WriteLine(rdr[0]);
        rdr.Close();
    }
    con.Close();
}

returns a single string value like this

attribute1;attribute2;attribute4

which can then be split on the semi-colon (;) character and handled appropriately.