No Fun... Import a .CSV file into Access 2007DB in C#/ASP.NET

Question

0.00/5 (No votes)

See more:

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...

C#

// connections string to DB
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...

C#

public int NonQueryDataIns(string pSQLString, int pcmdType)
{
    try
    {
        string insSQL = pSQLString;
        CreateConnection();
        OleDbCommand cmd = new OleDbCommand();
        OleDbTransaction trans;

        // set the properties of the cmd Object
        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;

        // add new parameter to command object & Execute it
        int rowsAffected = cmd.ExecuteNonQuery();
        cmd.Transaction.Commit();
        dbConn.Close();
        return rowsAffected;

    }
    catch (OleDbException oleEx)
    {

Thanks
Phill

Posted 1-Dec-10 9:25am

Squire Dude

Updated 1-Dec-10 13:25pm

Henry Minute

v3

Add a Solution

Comments

JOAT-MON 1-Dec-10 19:20pm

1. What do you mean by 'I am unable to get this to insert the new rows every time.'? Does that mean that it does do the insert some of the time?
2. Have you tried the selection query with out the insert portion to ensure that you are getting rows returned?
3. Have you stepped through the code to identify where it is problematic? (i.e. connection, retrieving data, inserting data, etc.)
4. Is it safe to assume that 'CreateConnection()' will instantiate 'dbconn' correctly?
5. Is it possible for DBTableName != "RAW_Transaction_Data"? if so, it looks like your code will still pass the incomplete query to be executed.

Thomas Krojer 2-Dec-10 3:45am

hmm ... give as an example of your data (i am interested in the contents of F5)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Squire Dude · Answer 1 · 2010-12-02T04:41:00

Solution 2

Hi,

to answer your questions in order...

1. It will insert the data about 30% of the time, it is irratick.
2. I have and I do get the rows back from the text file.
3. It would always bail at the "ExecuteNonQuery" line.
4. you are correct the code is below.
5. I have checked each time and the table name is being passed each time.

// Make the connection to the DB<br />
string a = varAppTitle + ".Properties.Settings." + varAppTitle + "ConnectionString";<br />
dbConn = new OleDbConnection(Settings.GetConnectionStringByName(a));<br />
dbConn.Open();<br />
<br />
// write to the Log File<br />
ExceptionLog("CreateConnection Success - " + a);<br />
return dbConn;<br />

What I have found and it is strange is that the cause of the DBConn being closed is either the BeginTransaction() or the next line trans; I have stubbed all the Transaction lines and it seems to work fine. From what I have read the Transaction lines are needed but I do not know why they are causing the errors.

Got any suggestions?

Thanks

Posted 2-Dec-10 4:41am

Squire Dude

Comments

JOAT-MON 2-Dec-10 14:02pm

Since it is intermittent, I would agree with Thomas Krojer (comment above) that it could be the format of the data being passed in. I think he is concerned about the format of the data being passed to the datetime field (F5), which makes sense. Can you check your data and see if there is a difference between the data rows that succeed and the ones that fail? Or post a small sample (rows that succeed and rows that do not)?

BTW - You should only post as an answer if it is an answer to the original question, use the Improve Question button or Add Comment to respond to comments. This way, people who may have a solution won't bypass your question thinking it had an answer.

Squire Dude 2-Dec-10 15:42pm

Sorry about my using the wrong area to respond.

Well I have 2 different files that use the same function to insert the data into the DB. I have checked and rewritten it more times than I care to think about. I am now back to the original error that the Connection is getting closed.

I have changed ALL the columns in both tables to type=Text in the DB so there should be no issues with "type".

This time I ran it and got {"Syntax error in INSERT INTO statement."}... I have changed nothing!

I check it immidialy before the Execute and it is open. NOw I ahve a different error... {"You must enter a value in the 'RAW_Transaction_Data.post_tran_cust_id' field."} I checked the file and there are now empty values for this (1st) column.

Squire Dude 2-Dec-10 16:01pm

Maybe this is shte issue.... When I check the Connection properties just before the Execute command it is not OPEN but CONNECTING???? Then the Execute FAILS. If I go back and step through the code slowly the Connection is then OPEN. Then I Execute and I get {"Syntax error in INSERT INTO statement."} hmmm....

JOAT-MON 2-Dec-10 17:15pm

Regarding: {"You must enter a value in the 'RAW_Transaction_Data.post_tran_cust_id' field."}: Is the 'RAW_Transaction_Data.post_tran_cust_id' field set to allow NULL value? Or, is it set as PrimaryKey? (both situations would require data to be present) This is likely to be related to the {"Syntax error in INSERT INTO statement."} error if the field requires a value and it receives a NULL.

Squire Dude 2-Dec-10 17:20pm

I had all but given up on this and was ready to tell the PM to get someone else to finish the project. Now it works... not sure exactly what I did or didn't do...

So I am unable to tell anyone else who has such a nighmare what to do with the code to make it work!!!.

Sorry about that people.

JOAT-MON 2-Dec-10 18:37pm

Glad it is working! :)

JOAT-MON · Answer 2 · 2010-12-02T11:29:00

Solution 3

Try using the .Commit() function in conjunction with the BeginTransaction(). Here is an example from MSDN[^]

Modify the execution area of your code to mimic this example. The biggest differences I see are the explicit .Open() of the connection, the use of the .Commit(), and the specification of the isolation level. Let me know if this makes a difference.

Posted 2-Dec-10 11:29am

JOAT-MON

Comments

JOAT-MON 2-Dec-10 17:53pm

Oops, sorry...not the use of .Commit() but where it calls it...don't know if this has an effect on how it behaves. The example calls the commit from outside of the command object.