2

For some reason, I can't open the Design View of the MS Access table in question; I can look at the data but not the desing, specifically, the length of columns.

When I try to insert a record into said table with this code:

using (var conn = new OleDbConnection(connStr))
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = 
            @"INSERT INTO tx_header (tx, site_no, xmlfile, collect_dttm, ccr_user, tx_memo, file_beg, file_end) 
              VALUES(@txval, @siteNum, @xmlfileName, Now(), @ccrUser, @TXMemo, @strfile_beg, @strfile_end)";
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@txval", tx);
        cmd.Parameters.AddWithValue("@siteNum", site_no);
        cmd.Parameters.AddWithValue("@xmlfileName", xmlfile);
        cmd.Parameters.AddWithValue("@ccrUser", ccr_user);
        cmd.Parameters.AddWithValue("@TXMemo", tx_memo);
        cmd.Parameters.AddWithValue("@strfile_beg", file_beg);
        cmd.Parameters.AddWithValue("@strfile_end", file_end);
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

...I get, "System.Data.OleDb.OleDbException was unhandled by user code HResult=-2147217833 Message=The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. Source=Microsoft Office Access Database Engine"

Rather than have to guess which column has too much data, it would be nice if I could programmatically determine which column is the problematic one. Can I? How?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    Have you looked into getting schema information through standard ADO.NET paths? Like the IDataReader.GetSchemaTable method? – Crono Mar 18 '14 at 18:02
  • I also must ask: why do you have to fetch your database specifications dynamically to begin with? Your code would be far more robust if it knew about database field types, lengths and constraints from the start! :p – Crono Mar 18 '14 at 18:14
  • @Crono: You're right, but, as mentioned, when I open the MDB file with Access, it doesn't allow me to see the design view; I don't know why. – B. Clay Shannon-B. Crow Raven Mar 18 '14 at 18:16
  • 1
    It's probably just locked. Have you tried opening up the Access file holding the Shift key? – Crono Mar 18 '14 at 18:19

2 Answers2

1

There's a pretty detailed explanation of how to query the underlying schema information in MSDN, starting at Retrieving Database Schema Information.

Disclaimer: I've never tried using that against an Access database.

Edmund Schweppe
  • 4,992
  • 1
  • 20
  • 26
1

After reading your comments above it looks clear to me that your Access file simply has its designer views locked down. Normally you should be able to unlock them by simply holding Shift, double-clicking the file and keep holding Shift until Access is up and running.

From then on you'll have complete access to tables, queries and the like, and along with it, your database specifications. That will be far better than trying to access that dynamically.

Crono
  • 10,211
  • 6
  • 43
  • 75
  • Thanks; that works (although I also, for some reason, have to right-click the file name and select Access, as opposed to Access via XP mode). So that solved my problem but, as Mr. Tonic Water answered the actual question... – B. Clay Shannon-B. Crow Raven Mar 18 '14 at 18:30
  • 1
    Edmund *did* answer the actual question and thus deserves the points. Still, the right call for you here should *not* get db details dynamically but rather enforce validation rules in your code according to hard-known specs. ;) – Crono Mar 18 '14 at 18:42