DataAdapter problem with MS-Access

[x102077@hotmail]

This is my second c# program and the database stuff is quite different than vb 6.0, I am
obviously missing a line or otherwise have malformed code -- Help!
Program is supposed to read in FCC Amateur Radio DB (flat file) and insert data into
an Access Database. DB has a defined primary key. Everything appears to work ok,
except when putting the dataset back in the DB file, I get a program exception!!!
I do have a catch block that does not help me much wrt to debug, anything I can add to make my debugging easier? The braces do not necessarily match up due to cut and paste. Program does build w/o errors and run ok, till line identifed below.
Thanks, Chuck

OleDbConnection conn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0;" +
                                   "User Id=;Password=;" + "Data Source=" + DestinationFile);

            conn.Open();

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter("Select * FROM Entity", conn);
            
            //Create a new dataset
            DataSet dsHams = new DataSet();

            dataAdapter.Fill(dsHams,"Entity");

            DataTable dtEntity = dsHams.Tables["Entity"];

            //Close connection as dataAdapter has copy of records in dsHams
            conn.Close();
                       
            {
                try
                {
                    // Create an instance of StreamReader to read from a file.
                    // The using statement also closes the StreamReader.
                    using (StreamReader sr = new StreamReader(textFlatFile.Text))
                    {
                        long nRecs = new long();
                        long rowPosition = new long();
                        nRecs = 0;
                        rowPosition = 0;
                        string line;

                        InitializeComponent();
                        txtStatusBar.Text = "Processing DB records...";
                         //  ***************************************************
                        //  Major Loop to read FCC DB and split fields for inclusion intoDB
                        //  ***************************************************
                        
                        while ((line = sr.ReadLine()) != null | bStopFlag == true)
                        {
                            ++nRecs;
                            if (Convert.ToInt64(nRecs) % 10000 == 0)
                               { Console.WriteLine(Convert.ToString(nRecs)); }

                            string[] sTokens = line.Split(new char[] { '|' });

                            DataRow rwNextHam = dtEntity.NewRow();

                            //field values...see FCC:  EN.DAT                          
                            rwNextHam["Record_Type"] = "EN";
                            rwNextHam["Unique_System_Identifier"] = sTokens[1];
                            rwNextHam["Call_Sign"] = sTokens[4];
                            
                            dtEntity.Rows.Add(rwNextHam);
                            
                            rowPosition = dtEntity.Rows.Count - 1;

                        }
			//fails at next line, "dtEntity" contains 976,500 records
			//which is what the table should approx. contain
                        
                        dataAdapter.Update(dsHams, "Entity"); [B]<---Fails here,exception[/B]
			.
			.
			.


               //separate catch block, anything I can add that will help me decipher problem
                catch (Exception )
                {
                    Console.WriteLine("Error: {0}");
                    //return;
                    goto nextRecord;
                }
            nextRecord: ;

[Ramy Mahrous]

Catch the exception using "OleDbException" not generic Exception and reply me with exception message, I could help you.

[x102077@hotmail]

Ramy, got the following error msg when I used the oleDBCxception catch:

Update requires a valid InsertCommand when pased a DataRow collection of new Rows.

catch(OleDbException ae)
        {
            for (int i = 0; i < ae.Errors.Count; i++)
            {
                Console.WriteLine(ae.Errors[i].Message + " - " + ae.Errors[i].SQLState);
            }
        }

The above code never did execute, but did get a better error description. At this point I am not what line of code I need to add. Thanks chuck

[x102077@hotmail]

After some browsing of a couple of VB.NET sites, i came across the a snippet of
code that gave me the solution. Although the count of the table was correct (965,000),
the data was evidently not being stored into the dataset correctly. Look
at the code segment below and you will see the corrected lines. The problem
was evidently syntax related; however, I see no problems with the original
syntax. See code below to follow the flow of connection to dataAdapter to DataSet to adding a new row to table in DataSet and sending the DataSet back to the original DB. the program executes in about 20 minutes in debug mode (more stuff going on than shown below) and runs in less than 10 minutes in non-debug mode. When i figure out release mode, I expect that to improve to 3-4 minutes.

The corrected lines are: DataRow rwNextHam = dsHams.Tables["Entity"].NewRow();
.
.
.
dsHams.Tables["Entity"].Rows.Add(rwNextHam);

The program works great now;however, I have to now learn threading
to get the DB conversion into its own thread which will allow
the main form to interact with the user. The code is so tight that
the Application.DoEvents commands essentially do nothing. This thread is now complete as far as I am concerned. Chuck

//******************************************************************
            //             Get connected to DB                                 *
            //******************************************************************

            OleDbConnection conn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0;" +
                                   "User Id=;Password=;" + "Data Source=" + DestinationFile);

            conn.Open();

            //OleDbDataAdapter DataAdapter = new OleDbDataAdapter(commandString,                  ConnectionString);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter("Select * FROM Entity", conn);
            //build oledb command builder
            OleDbCommandBuilder builder = new OleDbCommandBuilder(dataAdapter);

            //Create a new dataset
            DataSet dsHams = new DataSet();

           //fills DataSet with contents of Table "Entity" in original DB (MDB)
           dataAdapter.Fill(dsHams,"Entity");

            //Close connection as dataAdapter has copy of records in dsHams
            conn.Close();
                       
            {
                try
                {
                    // Create an instance of StreamReader to read from a file.
                    // The using statement also closes the StreamReader when EOF reached.
                    using (StreamReader sr = new StreamReader(textFlatFile.Text))
                    {
                        long nRecs = new long();
                        nRecs = 0;
                        string line;
                        txtStatusBar.Text = "Processing DB records...";
                       
                        //  ************************************************
                        //  Major Loop to read FCC DB and split fields for input into ACCESS
                        //  ************************************************
                        
                        while ((line = sr.ReadLine()) != null | bStopFlag == true)
                        {
                            ++nRecs;
                            //write out debug line every 1000 lines to keep me awake
                            if (Convert.ToInt64(nRecs) % 1000 == 0)
                               { Console.WriteLine(Convert.ToString(nRecs)); }

                            //Parse the CSV file from FCC using "split" function
                            //"split" will generate the correct dimension for array                            
                            string[] sTokens = line.Split(new char[] { '|' });
                            
                           [B]//this was one of the changed line, that make program work
                            //Define a new row to be added into DataSet("Entity"]
                            DataRow rwNextHam = dsHams.Tables["Entity"].NewRow(); [/B]

                            //fill field values...see FCC:  EN.DAT                          
                            rwNextHam["Record_Type"] = "EN";
                            rwNextHam["Unique_System_Identifier"] = sTokens[1];
                            rwNextHam["Call_Sign"] = sTokens[4];
                            rwNextHam["Entity_Type"] = sTokens[6];
                            rwNextHam["Licensee_ID"] = sTokens[0];
                            rwNextHam["First_Name"] = sTokens[8];
                            rwNextHam["MI"] = sTokens[9];
                            rwNextHam["Last_Name"] = sTokens[10];
                            rwNextHam["Suffix"] = sTokens[11];
                            rwNextHam["PO_Box"] = sTokens[14];
                            rwNextHam["Street_Address"] = sTokens[15];
                            rwNextHam["City"] = sTokens[16];
                            rwNextHam["State"] = sTokens[17];
                            rwNextHam["Zip_Code"] = sTokens[18];

                            
                             [B]//This line was changed to enable code to work
                             //Add new row of data to the DataSet
                             dsHams.Tables["Entity"].Rows.Add(rwNextHam);[/B]
                           
                            // optional output for debug purposes...
                            //user can stop program by setting bStopFlag = True
                            if (Convert.ToInt64(nRecs) % 10000 == 0)
                            {
                                //this.txtCurrentRecord.Text = Convert.ToString(nRecs);
                                //Console.WriteLine(Convert.ToString(nRecs) + " " + line);
                                //this.txtStatusBar.Text = Convert.ToString(nRecs) + " " + line;
                                
                                if (bStopFlag == true)
                                {
                                    MessageBox.Show("Conversion manually stopped");
                                    bStopFlag = false;
                                    Application.DoEvents();
                                    return;
                                }
                            }
                        }

                    }

                }
                //new enhanced catch block to get better information on faulty execution
                catch ( OleDbException myException )
                {
                    for ( int i=0; i < myException.Errors.Count; i++ )
                        {
                            Console.WriteLine( "Index #" + i + "<br>" +
                            "Message: " + myException.Errors [i].Message + "<br>" +
                            "Native: " + myException.Errors [i].NativeError.ToString ( ) + "<br>" +
                            "Source: " + myException.Errors [i].Source + "<br>" +
                            "SQL: " + myException.Errors [i].SQLState + "<br>" );
                        }
                }
           
            }
            [B]//this line now works after the change above
            //this line was moved here to optimize loop execution (better practice)
            dataAdapter.Update(dsHams, "Entity");[/B]

            //tell user that we are through
            MessageBox.Show("DATABASE EOF REACHED!");
            txtStatusBar.Text = "Finished processing DB records...";
        }
   

    }
        
}