OleDb Exception, too many characters in string

Question

So I have been using oledb to create an Access table and insert data into it but I recently hit a brick wall when the data I was trying to insert became longer than 255 characters in some fields. This is apparently not possible to do.

I ran into this problem in a past program when doing the same type of thing but with Excel. The solution I found was to use Microsoft.Office.Interop.Excel. This actually made my program a lot shorter and simplified, so I figured I could do it with an Access database as well.

Only problem is, there is almost no documentation or example code for this reference so I have no idea how to complete my task. Can someone provide me with some example code on how to even just generally use Microsoft.Office.Interop.Access, or even more helpful would be some example code on how to create a table in an existing database, and fill that table with data?

UPDATE: Here's my oledb code that is not working:

        var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", Db);
        var cnn = new OleDbConnection(cnnStr);
        cnn.Open();

        //make new access table
        using (OleDbCommand command = new OleDbCommand())
        {
            command.Connection = cnn;
            command.CommandText = String.Format("CREATE TABLE [{0}] ([Tag] string, [Text] string)", oldTable + "_combined");
            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {
                //table already exists, so delete it and remake it
                command.CommandText = String.Format("DROP TABLE [{0}]", newTable + "_combined");
                command.ExecuteNonQuery();
                command.CommandText = String.Format("CREATE TABLE [{0}] ([Tag] string, [Text] string)", oldTable + "_combined");
                command.ExecuteNonQuery();
            }
        }

        //fill access table
        using (OleDbCommand command = new OleDbCommand())
        {
            command.Connection = cnn;
            command.CommandText = String.Format("INSERT INTO [{0}] (Tag, [Text]) VALUES (?, ?)", oldTable + "_combined");
            command.Parameters.Add(new OleDbParameter("Tag", ""));
            command.Parameters.Add(new OleDbParameter("Text", ""));
            for (int i = 0; i < (comboText.Length - 1); i++)
            {
                command.Parameters["Tag"].Value = comboTag[i].Replace("\"", "");
                comboText[i] = comboText[i].Replace("\r\n", "");
                command.Parameters["Text"].Value = comboText[i].Replace("\"", "");
                command.ExecuteNonQuery();
            }
        }

        cnn.Close();

When my code reaches the 19th element in the comboText array (this element has 273 characters), the code errors and stops on command.ExecuteNonQuery() with the following error message: "OleDbException was unhandled: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

score 1 · Accepted Answer · answered Jul 02 '14 at 20:46

So I have been using oledb to create an Access table and insert data into it but I recently hit a brick wall when the data I was trying to insert became longer than 255 characters in some fields. This is apparently not possible to do.

Nonsense. The following C# code

static void Main(string[] args)
{
    string myConnectionString =
            @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=C:\Users\Public\Database1.accdb;";
    using (var con = new OleDbConnection(myConnectionString))
    {
        con.Open();
        using (var cmd = new OleDbCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = 
                    "CREATE TABLE MemoTest (" +
                        "Id COUNTER PRIMARY KEY, " +
                        "MemoField MEMO " +
                    ")";
            cmd.ExecuteNonQuery();

            string lorem =
                    "Lorem ipsum dolor sit amet, consectetur adipisicing elit, " +
                    "sed do eiusmod tempor incididunt ut labore et dolore magna " +
                    "aliqua. Ut enim ad minim veniam, quis nostrud exercitation " +
                    "ullamco laboris nisi ut aliquip ex ea commodo consequat. " +
                    "Duis aute irure dolor in reprehenderit in voluptate velit " +
                    "esse cillum dolore eu fugiat nulla pariatur. Excepteur sint " +
                    "occaecat cupidatat non proident, sunt in culpa qui officia " +
                    "deserunt mollit anim id est laborum.";
            cmd.CommandText = "INSERT INTO MemoTest (MemoField) VALUES (?)";
            cmd.Parameters.AddWithValue("?", lorem);
            cmd.ExecuteNonQuery();
            Console.WriteLine(String.Format("Wrote {0} characters to memo field.", lorem.Length));
        }
        con.Close();
    }
}

prints the following to the console

Wrote 446 characters to memo field.

and when I open the table in Access I see this:

Please refer to my updated question. Looks to be using the same methods as your code you found. Only difference is the connection string I suppose. What's the difference between ACE and Jet? — Mikkel Bang, Jul 02 '14 at 21:08
@MikkelBang In your CREATE TABLE statement you are creating your fields as `string`. Without a length qualifier (e.g., `string(50)`) `string` will create `Text` fields with a maximum size of 255 characters. I created my field as `MEMO` so it can store text values that are longer than 255 characters. — Gord Thompson, Jul 02 '14 at 21:24

OleDb Exception, too many characters in string

1 Answers1