Inserting values into the database brings wrong values

Question

I have copied some code from the internet and modified it I kind of understand what it's doing but I can't get it to insert properly the records into the database. I can't seem to get anything other than literally what I type in the values fields if I input "The name of a variable" then that's what I get out the other end. Once I get this to work it's finished.

The error I am getting is that the database is open/or /that there is a problem with the variables on the insert execute command line number.

private void button9_Click(object sender, EventArgs e)
{
    try
    {
        string ConnString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\names.accdb;Persist Security Info=False");
        using (OleDbConnection Conn = new OleDbConnection(ConnString))
        {
            Conn.Close();//severl times connection has been open
            Conn.Open();
            DataSet ds = new DataSet();
            ds.ReadXml(@"c:\\temp\\my123.xml");
            OleDbCommand cmd = new OleDbCommand();
            OleDbCommand cmd1 = new OleDbCommand();
            DataTable dtCSV = new DataTable();
            dtCSV = ds.Tables[0];              
            cmd.Connection = Conn;
            cmd.CommandType = CommandType.Text;
            cmd1.Connection = Conn;
            cmd1.CommandType = CommandType.Text;

            Conn.Open();

            for (int row = 0; row <= dtCSV.Rows.Count - 1; row++)
            {
                for (int col = 1; col <= dtCSV.Columns.Count - 1; col++)
                {
                    cmd.CommandText= ("INSERT INTO  tab1 ( field1, field2) VALUES (dtCSV.Rows ,dtCSV.Columns)");
                    cmd.ExecuteNonQuery();
                }
            }
            Conn.Close();
        }
    }
    catch (Exception ex)
    {
        richTextBox1.Text = richTextBox1.Text + "\n Error " + ex + "\n"; ;
    }
}

Error System.InvalidOperationException: The connection was not closed. The connection's current state is open. at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at WindowsFormsApp5.Form1.button9_Click(Object sender, EventArgs e)

Answer 1

private void button9_Click(object sender, EventArgs e)
{
    try
    {
        string ConnString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\names.accdb;Persist Security Info=False");
        using (OleDbConnection Conn = new OleDbConnection(ConnString))
        {
            //Conn.Close();//severl times connection has been open
            Conn.Open();
            DataSet ds = new DataSet();
            ds.ReadXml(@"c:\\temp\\my123.xml");
            OleDbCommand cmd = new OleDbCommand();
            //OleDbCommand cmd1 = new OleDbCommand();
            DataTable dtCSV = new DataTable();
            dtCSV = ds.Tables[0];
            cmd.Connection = Conn;
            cmd.CommandType = CommandType.Text;
            //cmd1.Connection = Conn;
            //cmd1.CommandType = CommandType.Text;

            //Conn.Open();

            for (int row = 0; row <= dtCSV.Rows.Count - 1; row++)
            {
                //for (int col = 0; col < dtCSV.Columns.Count - 1; col++)
                //{
                //    //cmd.CommandText = ("INSERT INTO  tab1 ( field1, field2) VALUES (dtCSV.Rows ,dtCSV.Columns)");
                //}
                cmd.Parameters.Clear();
                if (dtCSV.Columns.Count > 1)
                {
                    cmd.Parameters.Add(dtCSV.Rows[row][0].ToString());
                    cmd.Parameters.Add(dtCSV.Rows[row][1].ToString());

                    cmd.CommandText = ("INSERT INTO  tab1 ( field1, field2) VALUES (? , ?)");
                    cmd.ExecuteNonQuery();
                }

            }

            //Conn.Close();
        }
    }
    catch (Exception ex)
    {
        richTextBox1.Text = richTextBox1.Text + "\n Error " + ex + "\n"; ;
    }
}

You can use this. I hope it help you.

Answer 2

Well you are calling Open() function on your connection object twice as seen in below code block. So essentially, you are trying to open the connection while the connection state is still Open and that's what the error saying exactly

        Conn.Open();      <--- Here
        DataSet ds = new DataSet();
        ds.ReadXml(@"c:\\temp\\my123.xml");
        OleDbCommand cmd = new OleDbCommand();
        OleDbCommand cmd1 = new OleDbCommand();
        DataTable dtCSV = new DataTable();
        dtCSV = ds.Tables[0];              
        cmd.Connection = Conn;
        cmd.CommandType = CommandType.Text;
        cmd1.Connection = Conn;
        cmd1.CommandType = CommandType.Text;

        Conn.Open();     <--- Here

As @Steve said cmd1 is never used and looks like attached to same command text.

Moreover, your INSERT query never going to work the desired way cause you aren't actually substituting the values

"INSERT INTO  tab1 ( field1, field2) VALUES (dtCSV.Rows ,dtCSV.Columns)"

You should meant it to be

string.Format("INSERT INTO  tab1 ( field1, field2) VALUES ({0} ,{1})", dtCSV.Rows[row] ,dtCSV.Columns[col])

(Or) if you are using C#6 then you can just say

$"INSERT INTO  tab1 ( field1, field2) VALUES ({dtCSV.Rows[row]} ,{dtCSV.Columns[col]})"