0

I've been working on this function to take a user selected csv file and parse it into an access database. After much trial and error and reading here, the thing will compile and run without crashing but no values are being inserted into the table. This I where I am at currently.

private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
    productFileName = productOpenDialog.SafeFileName;
    productFilePath = Path.GetDirectoryName(productOpenDialog.FileName);
    string connectionString = String.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0}"+"\\VendorDB.accdb;Persist Security Info = False;", Environment.CurrentDirectory);

    string selectSQL = String.Format(@"SELECT * FROM [" + productFileName + "]"); 


    using (OleDbConnection connection = new OleDbConnection(String.Format(@"Provider = Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Text;", productFilePath)))
    {
        using (OleDbCommand command = new OleDbCommand(selectSQL, connection))
        {

            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);

                //create connection to Access DB
                OleDbConnection DBconn = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand();
                //set cmd settings
                cmd.Connection = DBconn;
                //cmd.CommandType = CommandType.Text;
                //open DB connection
                DBconn.Open();

                //read each row in the Datatable and insert that record into the DB
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    cmd.CommandText = "INSERT INTO Product_List (ProductID, ProductName, Description, InventoryItem, Price, CaseSize, SalespersonID)" +
                                     " VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +
                                         "','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +
                                         "','" + dataTable.Rows[i].ItemArray.GetValue(6) + "')";

                    cmd.ExecuteNonQuery();

                }
                //close DB.connection
                DBconn.Close();

            }
        }
    }
}

I am aware that datatypes in fields might be an issue, so I removed a DateTime and changed price to a short number, so all data is strings and numbers. Despite successfully loading the DataTable from the csv file, no values are inserted into access.

Mohamed Najiullah
  • 403
  • 5
  • 12
Norton Deckert
  • 21
  • 1
  • 1
    Are you sure that your table contains any rows? Use the debugger to follow your code and check the if the objects and the properties involved have the expected values. – Steve Nov 23 '17 at 23:17
  • 1
    personally you should not try to share the same connection across objects like that when you are doing Selects vs Inserts, Connection pooling in .net is not like what one would see or could utilize in Delphi.Net for example.. create a method inside your loop and create a new separate connection for the insert statement.. also can you let us know what errors you are getting if any..? – MethodMan Nov 23 '17 at 23:17
  • Look into [OleDbCommand.Prepare](https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.prepare(v=vs.110).aspx) if reusing the same OleDbCommand in a loop and use parameters - they will help you overcoming your other issues. – Filburt Nov 23 '17 at 23:19
  • also inside the loop I would expect to see an error on the second row when you hit this line, `DBconn.Open();` because the connection is already open, hence why I stated you should separate the Select functionality from the insert functionality.. – MethodMan Nov 23 '17 at 23:26
  • 1
    The OP is using 2 different connections, but the code could exit/close the first loop after the table is filled. Then rather than a loop on datarows, just use a new DataAdapter to update them all. Then of course it could be the whole Copy If Newer thing if the second DB is part of the project – Ňɏssa Pøngjǣrdenlarp Nov 23 '17 at 23:27
  • @Steve, the values in the DataTable are present and I can see them in the debugger. – Norton Deckert Nov 24 '17 at 00:22
  • @Filburt I looked into parameterizing, and that is something I will need to do later, but first I need to get any values at all into the DB. – Norton Deckert Nov 24 '17 at 00:22
  • @Plutonix how would I implement such a DataAdapter? – Norton Deckert Nov 24 '17 at 00:22
  • @MethodMan, I'm not getting any errors. It runs fine just doesn't write to the DB. – Norton Deckert Nov 24 '17 at 00:23
  • All the comments are related to a better way to code that, not the problem you asked. The real problem might be related to https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails – Ňɏssa Pøngjǣrdenlarp Nov 24 '17 at 00:27
  • 1
    Can someone please tell him something about SQL Parameters/ Injection!?! – Krish Nov 24 '17 at 09:01
  • use the debugger, change the code to use Parameters, and separate your code into smaller more manageable pieces. start there first – MethodMan Nov 24 '17 at 15:46

0 Answers0