Cannot apply indexing with [] to an expression of type 'DataColumn' C#

Question

this is my first time creating a post on here and so i may be a little new to the conventions here.

Basically, in my project (which is a c# form), I am trying to convert an excel spreadsheet into an empty database that has already been created. Its giving me the error shown below:

Unable to cast object of type 'System.Data.DataColumn' to type 'System.Data.DataRow'.

I have no way of knowing how to fix it. If its a really obvious fix then im really sorry.

I'm a college student who has only about 1 year of experience in c# coding.

public class ExcelConversion
    {
        public string filepath { get; set; }
        public void FileImport()
        {
            FileInfo file = new FileInfo("top5_leagues_player.csv");
            string fileNameAccess = "FMSDatabase.mdb";
            string ConnectionStringAccess = string.Format("Data Source={0}; Provider=Microsoft.Jet.OLEDB.4.0; Persist security Info=false", fileNameAccess);
            using (OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties = 'text;HDR=Yes;FMT=Delimited(,)'; "))
            {
                using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [top5_leagues_player.csv]", cnn))
                {   
                    cnn.Open();
                    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        DataTable tbl = new DataTable("MyTable");
                        adp.Fill(tbl);
                        using (OleDbConnection dbcnn = new OleDbConnection(ConnectionStringAccess))
                        {
                            // Player Table
                            dbcnn.Open();
                            OleDbCommand cmdAccess = dbcnn.CreateCommand();
                            cmdAccess.CommandType = CommandType.Text;
                            cmdAccess.CommandText = "INSERT INTO Player ([PlayerId], [PlayerName], [Age], [Nationality], [Price]) VALUES(@id, @name, @age, @nationality, @price)";
                            OleDbParameter param1 = new OleDbParameter("@id", OleDbType.Integer);
                            cmdAccess.Parameters.Add(param1);
                            OleDbParameter param2 = new OleDbParameter("@name", OleDbType.VarChar);
                            cmdAccess.Parameters.Add(param2);
                            OleDbParameter param3 = new OleDbParameter("@age", OleDbType.Integer);
                            cmdAccess.Parameters.Add(param3);
                            OleDbParameter param4 = new OleDbParameter("nationality", OleDbType.VarChar);
                            cmdAccess.Parameters.Add(param4);
                            OleDbParameter param5 = new OleDbParameter("@price", OleDbType.Decimal);
                            cmdAccess.Parameters.Add(param5);


                            foreach (DataColumn row in tbl.Columns)
                            {
                                param1.Value = Convert.ToInt32(row[0]);
                                param2.Value = row[1].ToString();
                                param3.Value = Convert.ToInt32(row[3]);
                                param4.Value = row[5].ToString();
                                param5.Value = Convert.ToDecimal(row[4]); ;
                                cmdAccess.ExecuteNonQuery();
                            }

                            // Club Table
                            cmdAccess.CommandText = "INSERT INTO Club ([ClubName) VALUES(@club)";
                            OleDbParameter param8 = new OleDbParameter("@club", OleDbType.Integer);
                            cmdAccess.Parameters.Add(param8);
                            foreach (DataRow row in tbl.Rows)
                            {
                                param8.Value = row[12].ToString();
                                cmdAccess.ExecuteNonQuery();
                            }


                            cmdAccess.CommandText = "INSERT INTO League ([LeagueName) VALUES(@league)";
                            OleDbParameter param9 = new OleDbParameter("@league", OleDbType.VarChar);
                            cmdAccess.Parameters.Add(param9);
                            foreach (DataRow row in tbl.Rows)
                            {
                                param8.Value = row[17].ToString();
                                cmdAccess.ExecuteNonQuery();
                            }
                        }
  
                    }
                }
            }

        }
    }

Before, the foreach loop had a DataRow column but i changed it to a DataColumn object to try and fix a previous error that i had before but now im stuck with this.

Answer 1

I think you are iterating over the columns of the DataTable instead of rows in the below loop:

foreach (DataColumn row in tbl.Columns)
{
    param1.Value = Convert.ToInt32(row[0]);
    param2.Value = row[1].ToString();
    param3.Value = Convert.ToInt32(row[3]);
    param4.Value = row[5].ToString();
    param5.Value = Convert.ToDecimal(row[4]); ;
    cmdAccess.ExecuteNonQuery();
}

You should be iterating over the rows of the DataTable and extracting values from the columns.

foreach (DataRow row in tbl.Rows)
{
    param1.Value = Convert.ToInt32(row[0]);
    param2.Value = row[1].ToString();
    param3.Value = Convert.ToInt32(row[3]);
    param4.Value = row[5].ToString();
    param5.Value = Convert.ToDecimal(row[4]);
    cmdAccess.ExecuteNonQuery();
}

There is one more mistake in the below code blocks where you are inserting values into the League tables.

cmdAccess.CommandText = "INSERT INTO League ([LeagueName) VALUES(@league)";
OleDbParameter param9 = new OleDbParameter("@league", OleDbType.VarChar);
cmdAccess.Parameters.Add(param9);
foreach (DataRow row in tbl.Rows)
{
    param8.Value = row[17].ToString();  // This should be param9.Value = row[17].ToString();
    cmdAccess.ExecuteNonQuery();
}