Geek Answers Handbook

Writing from datatable to foxpro via oledb fails after X records

Unhandled exception: System.Data.OleDb.OleDbException: SQL: column "Q578P5" not found ...

I wrote an application in VS2010 C # .net that reads data from an SQL table into a datatable and then writes it to foxpro tables.

In the inventory table, if this is not performed, as indicated above, in entry 578. In the Customer table, this is not performed when writing "Q617P78"

I tested data problems by deleting some records from the SQL table, but the error still occurs with the same record number, even though this record number is not the same record.

I tried writing data records to CSV and it works fine. This seems to be a problem with FoxPro tables.

Inventory records are shorter than customer records. Therefore, I suspect a memory problem. Everything works fully, as expected, before writing the number X.

Any suggestions appreciated

namespace PLADO
{
class Program
{
    static void Main(string[] args)
    // CUSTOMERS
    {   // Create 2 tables - one for SQL and one for Vision
        DataTable VisionCustomerResultSet = new DataTable();
        DataTable SQLCustomerResultSet = new DataTable();

        // read data from INI
        string INIFilePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DBCS\\PLExe.ini";
        var ThisAppINIFile = new IniFile(INIFilePath.Trim());

        var SQLServer = ThisAppINIFile.Read("Glo:SQLServerInstance", "Preserved");
        var SQLDatabase = ThisAppINIFile.Read("Glo:SQLDatabase", "Preserved");
        var SQLTrustedConnection = ThisAppINIFile.Read("Glo:TrustedConnection", "Preserved");
        var SQLUsername = ThisAppINIFile.Read("Glo:SQLUsername", "Preserved");
        var SQLUserPassword = ThisAppINIFile.Read("Glo:SQLUserPassword", "Preserved");
        var SQLConnectionString = "Server=" + SQLServer + ";Database=" + SQLDatabase + ";User ID=" + SQLUsername + ";Password=" + SQLUserPassword + ";";
        var ADOConnectionString = ThisAppINIFile.Read("Glo:ADOConnectionString", "Preserved");

        // Open the SQL database
        SqlConnection sqlCon = new SqlConnection(SQLConnectionString);
        sqlCon.Open();

        // Open the Foxpro database
        OleDbConnection oleDbConnection1 = new OleDbConnection(ADOConnectionString);
        oleDbConnection1.Open();

        // read the SQL values into DataTAble
        string commandString = "SELECT [uniqueid],[ledgerno],[accountno],[sortcode],(clipped for readability)...[zgrouping],[zclegacy],[zmarket] FROM [PrimeLaundry].[dbo].[Vision_Customer]";
        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
        SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
        sda.Fill(SQLCustomerResultSet);                     // read the select statement results into the dataTable


        // cycle through DataTable
        foreach (DataRow row in SQLCustomerResultSet.Rows)
        {   // read a matching record from Foxpro
            Console.WriteLine(row["AccountNo"]);
            string selectStatement = "select accountno from Customer where accountno = '" + row["AccountNo"] + "'";  
            string insertStatement = "INSERT INTO CUSTOMER ([uniqueid],[ledgerno],[accountno],[sortcode],[title], (clipped for readability)...,[zclegacy],[zmarket])"
            + " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            string updateStatement = "UPDATE CUSTOMER SET sortcode = ?,title = ?,periods = ?,groupno = ? (clipped for readability)... ?,ordnoreq = ?,zrunno = ?,zgrouping = ?,zclegacy = ?,zmarket = ? where Accountno = '" + row["AccountNo"] + "'";
            OleDbCommand selectCommand = new OleDbCommand(selectStatement, oleDbConnection1);
            OleDbCommand insertCommand = new OleDbCommand(insertStatement, oleDbConnection1);
            OleDbCommand updateCommand = new OleDbCommand(updateStatement, oleDbConnection1);
            String selectQueryResult = (String)selectCommand.ExecuteScalar();
            if (string.IsNullOrEmpty(selectQueryResult))
            {
                insertCommand.Parameters.Add("uniqueid", OleDbType.VarChar).Value = row["uniqueid"];
                insertCommand.Parameters.Add("ledgerno", OleDbType.Numeric).Value = row["ledgerno"];
                insertCommand.Parameters.Add("accountno", OleDbType.VarChar).Value = row["accountno"];
                insertCommand.Parameters.Add("sortcode", OleDbType.VarChar).Value = row["sortcode"];
                (Clipped for readability)
row["zgrouping"];
                insertCommand.Parameters.Add("zclegacy", OleDbType.VarChar).Value = row["zclegacy"];
                insertCommand.Parameters.Add("zmarket", OleDbType.VarChar).Value = row["zmarket"];

                int count = insertCommand.ExecuteNonQuery();
            }
            else
            {
                updateCommand.Parameters.Add("Sortcode", OleDbType.VarChar, 2).Value = row["sortcode"];
                updateCommand.Parameters.Add("title", OleDbType.VarChar).Value = row["title"];
                updateCommand.Parameters.Add("periods", OleDbType.Numeric).Value = row["periods"];
                updateCommand.Parameters.Add("groupno", OleDbType.Numeric).Value = row["groupno"];    (Clipped for readability)
     updateCommand.Parameters.Add("zclegacy", OleDbType.VarChar).Value = row["zclegacy"];
                updateCommand.Parameters.Add("zmarket", OleDbType.VarChar).Value = row["zmarket"];

                int count = updateCommand.ExecuteNonQuery();
            }       // end of if (string.IsNullOrEmpty...
        }       // end of foreach look


        // INVENTORY
        // Create 2 tables - one for SQL and one for Vision
        DataTable VisionInventoryResultSet = new DataTable();
        DataTable SQLInventoryResultSet = new DataTable();

        // read the SQL values into DataTAble
        commandString = "SELECT [uniqueid],[ledgerno],[accountno],[sortcode],[title],[periods],[groupno],[taxcode],[taxcode2],[leadtime],[reorder],[binno],[alternate],[remarks],[salesunit],[purchunit],[weight],[ctryorigin],[commodity],[spratio],[price1],(Clipped for readability)...[kitcomp],[useredit],[lastdeldat],[maxreorder],[zprodgroup] FROM [PrimeLaundry].[dbo].[Vision_Inventory]";
        sqlCmd = new SqlCommand(commandString, sqlCon);
        sda = new SqlDataAdapter(sqlCmd);
        sda.Fill(SQLInventoryResultSet);                     // read the select statement results into the dataTable


        // cycle through DataTable
        foreach (DataRow row in SQLInventoryResultSet.Rows)
        {   // read a matching record from Foxpro
            string selectStatement = "select accountno from Inventry where accountno = '" + row["AccountNo"] + "'"; 
            string insertStatement = "INSERT INTO INVENTRY ([uniqueid],[ledgerno],[accountno],[sortcode],[title],[periods],[groupno],[taxcode],[taxcode2],[leadtime],[reorder],[binno],[alternate],(Clipped for readability)...,[zprodgroup],[zilegacy])"
                                        + " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,' ')";
            string updateStatement = "UPDATE INVENTRY SET sortcode = ?,title = ?,periods = ?,groupno = ?,taxcode = ?,taxcode2 = ?,leadtime = ?,reorder = ?,binno = ?,alternate = ?,remarks = ?,salesunit = ?,purchunit = ?(Clipped for readability)...maxreorder = ?,zprodgroup = ? where Accountno = '" + row["AccountNo"] + "'";
            OleDbCommand selectCommand = new OleDbCommand(selectStatement, oleDbConnection1);
            OleDbCommand insertCommand = new OleDbCommand(insertStatement, oleDbConnection1);
            OleDbCommand updateCommand = new OleDbCommand(updateStatement, oleDbConnection1);

            string selectQueryResult = (String)selectCommand.ExecuteScalar();
            if (string.IsNullOrEmpty(selectQueryResult))
            {
                insertCommand.Parameters.Add("uniqueid", OleDbType.VarChar).Value = row["uniqueid"];
                insertCommand.Parameters.Add("ledgerno", OleDbType.Numeric).Value = row["ledgerno"];
                insertCommand.Parameters.Add("accountno", OleDbType.VarChar).Value = row["accountno"];
                insertCommand.Parameters.Add("sortcode", OleDbType.VarChar).Value = row["sortcode"]; (Clipped for readability)...
                insertCommand.Parameters.Add("maxreorder", OleDbType.Numeric).Value =     row["maxreorder"];
                insertCommand.Parameters.Add("zprodgroup", OleDbType.VarChar).Value = row["zprodgroup"];

                int count = insertCommand.ExecuteNonQuery();
            }
            else
            {
                updateCommand.Parameters.Add("Sortcode", OleDbType.VarChar, 2).Value = row["sortcode"];
                updateCommand.Parameters.Add("title", OleDbType.VarChar).Value = row["title"];
                updateCommand.Parameters.Add("periods", OleDbType.Numeric).Value = row["periods"]; (Clipped for readability)...
                updateCommand.Parameters.Add("zprodgroup", OleDbType.VarChar).Value = row["zprodgroup"];

                int count = updateCommand.ExecuteNonQuery();
            }


        }
        oleDbConnection1.Close();
        sqlCon.Close();
    }
}

}

0
source share
2 answers

, , . , ONCE, reset VALUE ... , , . -, ONCE, , . / , ...

string ins = "insert into MyTable ( ColA, ColB, ColC, ..., ColZ ) values ( ?, ?, ?, ..., ? )"
string upd = "update MyTable set ColA = ?, ColB = ?, ColC = ?, ..., ColZ = ?  where pkColumn = ?"

OleDbCommand insCmd = new OleDbCommand(ins, oleDbConnection1);
OleDbCommand updCmd = new OleDbCommand(upd, oleDbConnection1);

, IDENTICAL , , WHERE LAST. , , SQL, ... , , ,

DataRow tmpRow = SQLCustomerResultSet.Rows[0];
prepParameters( insCmd, tmpRow, false );
prepParameters( updCmd, tmpRow, true );

private void prepParameters( OleDbCommand oCmd, DataRow oSampleRow, bool IsUpdate )
{
   oCmd.Parameters.Add("ColA", OleDbType.VarChar).Value = oSampleRow["ColA"];
   oCmd.Parameters.Add("ColB", OleDbType.Numeric).Value = oSampleRow["ColB"];
   oCmd.Parameters.Add("ColC", OleDbType.VarChar).Value = oSampleRow["ColC"];
   ...
   oCmd.Parameters.Add("ColZ", OleDbType.VarChar).Value = oSampleRow["ColZ"];

   if( IsUpdate )
      oCmd.Parameters.Add("PKCol", OleDbType.VarChar).Value = oSampleRow["PKCol"];
}

, , , , ..

private void AssignParameters( OleDbCommand oCmd, DataRow oSampleRow, bool IsUpdate )
{
   oCmd.Parameters[0].Value = oSampleRow["ColA"];
   oCmd.Parameters[1].Value = oSampleRow["ColB"];
   oCmd.Parameters[2].Value = oSampleRow["ColC"];
   ...
   oCmd.Parameters[n].Value = oSampleRow["ColZ"];

   if( IsUpdate )
      oCmd.Parameters[extra].Value = oSampleRow["PKColumn"];

}

- ...

foreach (DataRow row in SQLCustomerResultSet.Rows)
{  // read a matching record from Foxpro
   Console.WriteLine(row["AccountNo"]);

   // Just update the respective command parameter for the select...
   selectCommand.Parameters[0].Value = row["AccountNo"];

   // NOW, execute since we changed the parameter above before executing it.      
   String selectQueryResult = (String)selectCommand.ExecuteScalar();
   if (string.IsNullOrEmpty(selectQueryResult))
   {
      // with my simplified approach...
      AssignParameters( insCmd, row, false );
      // and now execute it...
      int count = insCmd.ExecuteNonQuery();
   }
   else
   {
      // with my simplified approach...
      AssignParameters( updCmd, row, true );
      // and now execute it...
      int count = updCmd.ExecuteNonQuery();
   }  // end of if (string.IsNullOrEmpty...
}  // end of foreach look
+2

, , , 2 VFP?

+1

More articles:


All Articles