I use some methods to import data from FoxPro tables and write them to SQL tables. This is just a tiny piece of an enterprise-class application, and frankly, most of it is above my head. The method I'm having problems with is getting two FoxPro tables and putting them into a dataset. The first table does not arise, but the second remains empty. Here is my code (more details after the code):
public DataSet GetFoxProDelinquencyData() { DataSet bothDlqTables = new DataSet(); DataSet DlqData; string selectQuery = "SELECT * FROM MasterDlqTable"; try { DlqData = _foxProServiceAccess.ExecuteFoxProDataSet(selectQuery, 1); } catch (Exception exceptionToHandle) { throw ExceptionHelper.HandleFoxProException(exceptionToHandle, "Failed to get data from FoxPro"); } DlqData.Tables[0].TableName = "Master"; bothDlqTables.Tables.Add(DlqData.Tables[0].Copy()); string delqnum = Convert.ToString(bothDlqTables.Tables[0].Rows[0]["delqnum"]); selectQuery = String.Format("SELECT * FROM rvdlqhst_txps WHERE delqnum = '{0}'", delqnum); try { DlqData = _foxProServiceAccess.ExecuteFoxProDataSet(selectQuery, 1); } catch (Exception exceptionToHandle) { throw ExceptionHelper.HandleFoxProException(exceptionToHandle, "Failed to get data from FoxPro"); } DlqData.Tables[0].TableName = "History"; bothDlqTables.Tables.Add(DlqData.Tables[0].Copy()); return bothDlqTables; }
So, when I debug, and I look at the contents of both DlqTables just before returning at the end, the master table is perfect, and the History table has the correct columns from its FoxPro equivalent, but it has no data, Same with the DlqData dataset. The delqnum line contains 000210, and our db guy confirms that the FoxPro table definitely contains records with this delay number. When he starts
SELECT * FROM rvdlqhst_txps WHERE delqnum = '000210'
from inside the database viewer it gets the results. I tried hard-coded the SELECT statement as a string instead of the string.format command, and I still get the same empty table. Any suggestions?
UPDATE: I also get an intermittent error in the first query:
Failed to connect to net.tcp: //rdsfoxproqa.alatax.com/FoxProService_Dist. The connection attempt continued for a period of 00: 00: 01.0625340. Error code 10061 TCP: The connection could not be completed because the target computer actively refused it. 172.19.0.105:808.
This happens from time to time, and often I can wait 30 minutes or so, and then try again and complete the request. I do not think it is connected, but I also do not know what causes it and will love it.