I have a stubborn bit of code I am unable to get to work properly.
I have a CSV (comma delimited) text file of data I need to Append into an existing Access DB (Currently 2007 but could be 2010) table.
I am unable to get this to insert the new rows every time.
What am I missing???
I created a couple of bits of code...
string query = "INSERT INTO ";
if (DBTableName == "RAW_Transaction_Data")
{
query = query + DBTableName + " ( post_tran_cust_id, message_type, tran_type, extended_tran_type, datetime_tran_gmt, recon_business_date, source_node_name, settle_amount_rsp, to_account_id, pan, agent_id, sub_agent_id, terminal_id, card_program, merchant_type ) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15 FROM [Text;HDR=NO;DATABASE=" + HttpContext.Current.Request.MapPath("~") + "FTP_DL\\" + ";].[" + FileName + "]";
}
int rowsAffected = myDC.NonQueryDataIns(query, 2);
return rowsAffected;
Here is the Execute code segment...
public int NonQueryDataIns(string pSQLString, int pcmdType)
{
try
{
string insSQL = pSQLString;
CreateConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbTransaction trans;
if (pcmdType == 1)
cmd.CommandType = CommandType.StoredProcedure;
else if (pcmdType == 2)
cmd.CommandType = CommandType.Text;
else if (pcmdType == 3)
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = insSQL;
cmd.Connection = dbConn;
trans = dbConn.BeginTransaction();
cmd.Transaction = trans;
int rowsAffected = cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
dbConn.Close();
return rowsAffected;
}
catch (OleDbException oleEx)
{
Thanks
Phill