C# retrieve MS Access Memo Field

Question

I'm using C# (Visual Studio 2015) and communicating with MS Access database. My code below works fine, except that the _statusLadder variable is returning an empty value from a Memo data type field (I run the same query in MS Access and it pulls the correct memo value).

I have also tried:

string _statusLadder = "";

Can someone help me figure out how to retrieve a Memo data type field from MS Access?

See code:

private string retrieveJobByID(int xID)
    {
        connection.Open();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;

        command.CommandText = "SELECT TOP 1 [ID], [JOB_NUM], [CUSTOMER], [MODELNO], [CREATE_DATE], [MODEL_FAMILY], [VER], [COM_PROTOCOL], [STATUS_LADDER] FROM tbl_job_tables WHERE([ID] = ?)";
        command.Parameters.Add("@ID", OleDbType.Integer).Value = xID;

        OleDbDataReader reader = command.ExecuteReader();

        string _id = "", _jobnum = "", _customer = "", _modelFamily = "", _modelNum = "", _createDate = "", _ver = "", _comProtocol = "";
        var _statusLadder = "";

        while (reader.Read())
        {
            _id = reader["ID"].ToString();
            _jobnum = reader["JOB_NUM"].ToString();
            _customer = reader["CUSTOMER"].ToString();
            _modelFamily = reader["MODEL_FAMILY"].ToString();
            _modelNum = reader["MODELNO"].ToString();
            _createDate = reader["CREATE_DATE"].ToString();
            _ver = reader["VER"].ToString();
            _comProtocol = reader["COM_PROTOCOL"].ToString();
            _statusLadder = reader["STATUS_LADDER"].ToString();  //<-- This returns empty when it should be ~300 characters.
            Console.WriteLine("Status is: " + _statusLadder);
        }

        if (!reader.IsClosed)
        {
            reader.Close();
        }

        if (connection.State == ConnectionState.Open) { 
            connection.Close();
        }

        string result = _id + "|" + _jobnum + "|" + _customer + "|" + _modelFamily + "|" + _modelNum + "|" + _createDate + "|" + _ver + "|" + _comProtocol + "|" + _statusLadder;


        return result;
    }

EDIT - Here's some pictures of my MS Access 2010 setup (ACCDB):

(1) - The first shows the SQL query. I entered ID=116 as a parameter

(2) - The second picture shows the result from the query. You can see that "STATUS_LADDER" returns the proper value.

(3) - This is the setup of the table (in Design Mode) for the Field "STATUS_LADDER"

have you checked the value of **reader["STATUS_LADDER"].ToString();"** in debug mode to see if it has a value? — Wheels73, Jan 26 '17 at 14:44
It says the value is: `""`. But when I copy/paste the query into MS Access, I get the right values. — Sanya, Jan 26 '17 at 14:48
Just thinking outloud. Are you sure you're connecting to the correct database in Dev mode? I have unknowingly in the past been running a query in SQL Server (i know you're in access) to check the contents of a proc on a UAT database when my code was connecting to DEV. — Wheels73, Jan 26 '17 at 14:55
Have you tried removing the `TOP 1` from the query string to see if that makes any difference? — Gord Thompson, Jan 26 '17 at 15:07
Still nothing :(. I think it has something to do with me first declaring it as a string (even `var _statusLadder = ""` declares it as a string. I'm looking into trying to cast it as an OleDbType (LongVarWChar) but I can't figure out how — Sanya, Jan 26 '17 at 15:12
I can't reproduce this problem. Do you store something special in this field or just text? Do you use MDB or ACCDB file? — Steve, Jan 26 '17 at 15:14
its ACCDB. In MS Access, the field is set to "Memo" and I left the settings as default but here's some of the settings: Unicode Compression: Yes, IME Mode: No Control, Text Format: Plain Text, Append Only: No — Sanya, Jan 26 '17 at 15:16
I'm now contemplating splitting the Memo field into two Varchar(255) fields since I know I won't exceed 500 characters. Then have code put the first 255 characters into Field1 and the remaining into Field2... seems like a pain though — Sanya, Jan 26 '17 at 15:19
You could always try doing a Compact and Repair Database in Access to see if that helps any. — Gord Thompson, Jan 26 '17 at 15:42

score 0 · Answer 1 · answered Feb 16 '19 at 19:07

I had no issue reading a memo/long text field from an Access database. Try this code (I simplified it some).

using System.Data.OleDb;   //other standard namespaces needed also.   


public class AccessDataHelper
{
    string myConn;  //value set in this class constructor

     //Constructor
    public AccessDataHelper()
    {
        myConn = System.Configuration.ConfigurationManager.ConnectionStrings["LocalDB"].ToString();
        //If myConn is null, then a real problem.
    }

    public List<KAddress> GetUnvalidatedAddresses()
    {
        List<KAddress> kAddresses = new List<KAddress>();
        string sqlCommandText = String.Empty;

        using (OleDbConnection myOleDbConnection = new OleDbConnection())
        {

            if (myConn != null)
            {
                myOleDbConnection.ConnectionString = myConn;

                sqlCommandText = "SELECT tblAddresses.AddressId, " +
                    "tblAddresses.USPSValidated, tblAddresses.Notes, " +
                    "tblAddresses.Address, tblAddresses.City, tblAddresses.State, tblAddresses.ZIP " +
                    "FROM tblAddresses  " +
                    "WHERE ( ((IsNull(tblAddresses.USPSValidated)) Or ((tblAddresses.USPSValidated) <> 1)) ) ";
                    //Field USPSValidated is an integer 1=USPS valid address

                OleDbCommand oleDbCommand = new OleDbCommand(sqlCommandText, myOleDbConnection);
                oleDbCommand.Connection.Open();
                OleDbDataReader reader = oleDbCommand.ExecuteReader();

                while (reader.Read())
                {
                    KAddress kAddress = new KAddress();

                    kAddress.AddressID = Convert.ToInt32(reader["AddressID"]);
                    kAddress.Notes = reader["Notes"].ToString();    // Notes is a memo,long text field in Access

                    kAddress.Address = reader["Address"].ToString();
                    kAddress.City = reader["City"].ToString();
                    kAddress.State = reader["State"].ToString();
                    kAddress.Zip = reader["Zip"].ToString();

                    kAddresses.Add(kAddress);
                }
            }
        }

        return kAddresses;
    }

} //end of class

C# retrieve MS Access Memo Field

1 Answers1