How can i preserve column order in a .dbf table created in c#?

Question

Hi

I am trying to create a .dbf table using visual c# because i need it to import data to another application. For that purpose I need the column order to be exactly right otherwise that application won't accept the table. It's been working fine so far, but recently I had to make some changes to the structure of the table, and I have added the columns in the exact position in the CREATE Statement. The problem that I am getting is that when I open the table after creation with Visual Fox Pro the new columns are at the end of the table, not where i put them.

My code is as follows:

region create dbf file

        //create the file again
        string strCreate = "CREATE TABLE " + strExportPath + "\\"+fisier+" " + 
            "(TIPD Numeric(1), "+
            " LN Numeric(2), " +
            " AN Numeric(4), "+
            " DEN_ANG Char(50), " +
            " CUI Char(13), " +
            " NR_CRT Numeric(6), "+
            " TIP_RECTIF Char(1), " +
            " NUME_ASIG Char(50), " +
            " CNP Char(13), "+
            " CNP_COPIL Char(13), "+
            " CAS_ASIG Char(2), "+
            " ZI_LUCRAT Numeric(2), "+
            " TOT_ZI_LUC Numeric(2), "+
            " SERIE_CCM Char(5), "+
            " NUMAR_CCM Char(10), "+
            " SERIE_CCM_ Char(5), "+
            " NUMAR_CCM_ Char(10), "+
            " DATA_ACORD DATE, " +
            " COD_INDEMN Char(2), " +
            " ZI_PRE_ANG Numeric(1), "+
            " ZI_FN Numeric(2), " +
            " ZI_PRE Numeric(2), "+
            " LOC_PRES Numeric(2), "+
            " SUM_ANG Numeric(18), "+
            " FN Numeric(18), " +
            " DATA_INC DATE, "+
            " DATA_SF DATE, "+
            " COD_URG Char(3), " +
            " COD_CONTAG Char(2), "+
            " BAZA_CALC Numeric(6), "+
            " ZI_BZ_CALC Numeric(3), "+
            " MZBCI Numeric(8,4), "+
            " ME_NR CHAR(10))";



        OleDbConnection con = new OleDbConnection(strConn);
        try 
        {
            con.Open();
        }
        catch //(Exception ex)
        {
            NMsgBox("Err", "", "Renunta");
            return;
        }

        OleDbCommand createCmd = new OleDbCommand(strCreate, con);
        try
        {
            createCmd.ExecuteNonQuery();
        }
        catch //(Exception ex)
        {
            NMsgBox("Err "+fisier, "", Renunta");
            return;
        }
        con.Close();

Columns like CNP_COPIL, DATA_ACORD and LOC_PRES are some of the new column, and are always put last, even if I execute the CREATE COMMAND in Visual Fox Pro

I can also mention that I CREATE an empty table everytime and fill it with data, I don't do alter table, because i don't need to. Every month there's a new table.

And I don't seem to get why it would put "new columns" last, even IF the table is a new one and i have deleted every previous one from that location

Answer 1

It of course makes zero sense that the columns would be re-ordered when the table is brand new. And that the order looks correct when you look at the table structure.

There's only one good explanation: whatever program you are using to look at the table has memorized the column order from a previous run. And puts the new columns at the end. This would be a very common and desirable feature, allowing the user to get back the view of the table just like it was the last time she looked at it. The layout data has to be stored in private data of the program, the .dbf file format cannot store it.

This is just a display artifact, it cannot affect proper operation in any way.

Answer 2

You can change the column order of a Foxpro Table within the Foxpro IDE.

USE table EXCLUSIVE
MODIFY STRUCTURE

Click on the button on left of the field and drag it up or down to where you need it.

EDIT: After reading the comment about it working on your boss's computer it sounds like the problem is that the foxuser.dbf is remembering the column order you browsed with last - since the columns weren't there the last time it just shows them at the end though they are really in the middle.

What exact command are you using to look at the table on your computer?