none
OLEDB CREATE TABLE Field too long RRS feed

  • Question

  • I would like to create a table in an Excel spreadsheet that has one column that is not restricted by a 255 character limit. Right now I do:

            using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Log](product CHAR(64),variant CHAR(64),message CHAR(255),detail CHAR(512))", candidateDebugConnection))
            {
              cmd.ExecuteNonQuery();
            }
    
    

    And I get the error:

    System.Data.OleDb.OleDbException: Size of field 'detail' is too long.
      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
      at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
    

    I would like to store exception detail on this column an with a stack trace this would easily exceed 255 characters. Is there a way around this or should I consider a different data type?

    Also I would like to store a DateTime in the spreadsheet. Creating the table with a column of type 'dateTime' like I would do for SQL gives me a syntax error. Am I better off just working with a string in this case?

    Thank you.


    Kevin Burton
    Monday, January 31, 2011 1:16 AM

Answers

All replies

  • CHAR is limited to 255 characters. Not sure if this works for Excel, but the Jet/ACE data type is MEMO. Synonyms are LONGTEXT, LONGCHAR, NOTE, NTEXT.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, January 31, 2011 2:45 PM
  • Defining the column as NTEXT(512) still results in an exception. However TEXT (with no length) doesn't generate an exception. Can I assume that this means I can put vitually an unlimited length string into that column (what ever the limitation of TEXT is, something like 2 Gb)?
    Kevin Burton
    • Edited by KevinBurton Monday, January 31, 2011 3:43 PM Update
    Monday, January 31, 2011 3:30 PM
  • I don't believe that the number of characters should be specified. I used LONGTEXT and it allowed me to create the Worksheet.

    CREATE TABLE [Log] (product CHAR(64),variant CHAR(64),message CHAR(255),detail LONGCHAR)
    

    Keep in mind that Excel technically is not a database so you may be limited as to the data types (and paramters) that are actually supported.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, January 31, 2011 3:58 PM
  • Hi,

    Yes, I can repro the issue, but I am not 100% sure about the column width limitation here.  It seems that 255 is the limitation in the data provider.  I only find 255 characters limitation for column width and header/footer in Excel here, http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=5&origin=HP005199291.   For a single cell, it may contain 32767 characters. 

    I used Open XML SDK and Office COM object to input text to a cell and 255 is not the limitation. 
    http://msdn.microsoft.com/en-us/library/cc861607%28office.14%29.aspx
    http://support.microsoft.com/kb/302084

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 31, 2011 4:04 PM
    Moderator
  • I am finding that although the following does result in an error when the table is created (I am using

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
                    using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Log](partner CHAR(64), base CHAR(64), product CHAR(64), variant CHAR(64), message CHAR(255), detail TEXT, occurred CHAR(64))", candidateDebugConnection))
                    {
                        cmd.ExecuteNonQuery();
                    }

    I do get an error from the following code

                        using (OleDbCommand cmd = new OleDbCommand("INSERT INTO [Log] (partner, base, product, variant, message, detail, occurred) " +
                                                                    "VALUES (@patner, @base, @product, @variant, @message, @detail, @occurred)", candidateDebugConnection))
                        {
                            cmd.Parameters.Add(new OleDbParameter("@partner"OleDbType.VarChar, 64));
                            cmd.Parameters.Add(new OleDbParameter("@base"OleDbType.VarChar, 64));
                            cmd.Parameters.Add(new OleDbParameter("@product"OleDbType.VarChar, 64));
                            cmd.Parameters.Add(new OleDbParameter("@variant"OleDbType.VarChar, 64));
                            cmd.Parameters.Add(new OleDbParameter("@message"OleDbType.VarChar, 255));
                            cmd.Parameters.Add(new OleDbParameter("@detail"OleDbType.LongVarChar));
                            cmd.Parameters.Add(new OleDbParameter("@occurred"OleDbType.VarChar, 64));

                            if (candidateDebugConnection.State != ConnectionState.Open) candidateDebugConnection.Open();

                            cmd.Parameters["@partner"].Value = string.IsNullOrEmpty(partner) ? string.Empty : partner.Substring(0, partner.Length > 64 ? 64 : partner.Length);
                            cmd.Parameters["@base"].Value = string.IsNullOrEmpty(baseCatalog) ? string.Empty : baseCatalog.Substring(0, baseCatalog.Length > 64 ? 64 : baseCatalog.Length);
                            cmd.Parameters["@product"].Value = string.IsNullOrEmpty(productId) ? string.Empty : productId.Substring(0, productId.Length > 64 ? 64 : productId.Length);
                            cmd.Parameters["@variant"].Value = string.IsNullOrEmpty(variantId) ? string.Empty : variantId.Substring(0, variantId.Length > 64 ? 64 : variantId.Length);
                            cmd.Parameters["@message"].Value = string.IsNullOrEmpty(message) ? string.Empty : message.Substring(0, message.Length > 255 ? 255 : message.Length);
                            cmd.Parameters["@detail"].Value = string.IsNullOrEmpty(detail) ? string.Empty : detail;
                            cmd.Parameters["@occurred"].Value = time.ToShortTimeString();
                            int rowsAffected = cmd.ExecuteNonQuery();

    When I try to insert a row in the "table".

    System.Data.OleDb.OleDbException: Operation must use an updateable query.
      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
      at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
      at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
      at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
    

    Kevin Burton
    Monday, January 31, 2011 4:11 PM
  • This exception seems unrelated to the column length. I was able to get over the 'not updateable' exception but I am still getting an exception that the column is too short (even when defining the column type as TEXT).
    Kevin Burton
    Monday, January 31, 2011 4:31 PM
  • Lingzhi Sun,

    I am a little confused by the limitation of 255 for a column and 32,767 for a cell. Wouldn't a cell lie underneath a column? Since this is a managed ADO.NET forumn how do I create a table and then at runtime fill a given column with 32,767 characters (rather than the "limit" of 255).


    Kevin Burton
    Monday, January 31, 2011 5:27 PM
  • Hi Kevin,

    After some research, I believe that 255 characters is the limit when performing an insert into an Excel column using data access drivers.  It may not be related to Excel's self-settings. 

    As a workaround, could you use Open XML SDK or Office COM Interop to input string values more than 255 characters? 
    http://msdn.microsoft.com/en-us/library/cc861607%28office.14%29.aspx
    http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by KevinBurton Tuesday, February 1, 2011 5:19 PM
    Tuesday, February 1, 2011 2:39 AM
    Moderator
  • I have posted a question on the OpenXML forum. I am not clear how to get a sheet by name and then how to append a row to that sheet. If you readily have a solution would you mind sharing it? I am obviously very new to OpenXML and Excel.
    Kevin Burton
    Wednesday, February 2, 2011 3:01 PM
  • Hi Kevin,

    I believe you mean this thread in Open XML SDK forum, http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/42a09596-a7c1-4ca2-8214-1354c986f1ed.   Bruce has followed up on this thread and he provided solutions in both Open XML and Office COM.  Please check it. 

    If you have any questions, please feel free to let me know.

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 4, 2011 6:30 AM
    Moderator