Insert a Data Table to Access Database using c#

Question

I have tried few codes from my end to insert a DataTable inside a Access db. Below is the code:

public void WriteToAccess(DataTable dt)
        {
            string strDSN = "DSN=MYDSN";
            string cmdText="Insert into AccessTable (ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF,ColumnG,ColumnH) Values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)";
            using (OdbcConnection cn = new OdbcConnection(strDSN))
            {
                using (OdbcCommand cmd = new OdbcCommand(cmdText, cn))
                {
                    cn.Open();
                    foreach (DataRow r in dt.Rows)
                    {
                        cmd.Parameters.AddWithValue("@p1", r["ColumnA"].ToString());
                        cmd.Parameters.AddWithValue("@p2", r["ColumnB"].ToString());
                        cmd.Parameters.AddWithValue("@p3", r["ColumnC"].ToString());
                        cmd.Parameters.AddWithValue("@p4", r["ColumnD"].ToString());
                        cmd.Parameters.AddWithValue("@p5", r["ColumnE"].ToString());
                        cmd.Parameters.AddWithValue("@p6", r["ColumnF"].ToString());
                        cmd.Parameters.AddWithValue("@p7", r["ColumnG"].ToString());
                        cmd.Parameters.AddWithValue("@p8", r["ColumnH"].ToString());
                        cmd.ExecuteNonQuery();//Exception at this line
                    }

                }
            }

The DataTable to be inserted has 8 columns, the table in Access which i have created also has 8 columns. When I execute the above piece of code, I encounter an exception. It says that:

ERROR [07002ױ] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.

I'm providing 8 params, yet it throws the error.

Can anybody let me know what I am doing wrong?

Try searching on StackOverflow or Google. I did and found a whole bunch of answers that go over this exact issue. Try using those answers to get something going, then ask a question when you have a specific problem. — KevenDenen
– KevenDenen, Commented Jan 12, 2015 at 18:04
@KevenDenen: I'm trying to write some code. Will edit the question soon. — Anurag
– Anurag, Commented Jan 12, 2015 at 18:14

Gord Thompson · Accepted Answer · 2015-01-13 15:40:37Z

There are at least two issues with your current code:

[1] Parameterized queries using System.Data.Odbc with the Access ODBC driver must use the question mark (?) for all parameter placeholders. It is not recognizing @p1 @p2, ... as parameters in the CommandText so you are getting the "Too few parameters" error. You need to use

string cmdText="Insert into AccessTable (ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF,ColumnG,ColumnH)"
        + " Values (?,?,?,?,?,?,?,?)";

[2] If you are going to use Parameters.AddWithValue() inside the loop you need to do a Parameters.Clear() before adding the parameter values. (Without it, you will keep adding new parameter values - 8 at a time - instead of replacing the existing ones.)

foreach (DataRow r in dt.Rows)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("?", r["ColumnA"].ToString());
    cmd.Parameters.AddWithValue("?", r["ColumnB"].ToString());
    // and so on

Collectives™ on Stack Overflow

Insert a Data Table to Access Database using c#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related