OleDbCommand

[loftty]

Hi,

I have this code where I add 20 parameters to the OleDbCommand but for some reason I only get 1 record inserted, why is this?

OleDbCommand dbcommand = new OleDbCommand(sql2, conn2);
 
                            int Tablecount = 0;
                            foreach (DataSet ds in TempDataSet)
                            {
                                //foreach (string s in TempColumnName)
                                //{
                                string s = "@" + TempColumnName[Tablecount];
                                foreach (DataRow dr in ds.Tables[0].Rows)
                                {
                                    //dbcommand.Parameters.Add(s, ds.Tables[0].Rows[ds.Tables[0].Rows.IndexOf(dr)][0]);
                                    dbcommand.Parameters.AddWithValue(s, ds.Tables[0].Rows[ds.Tables[0].Rows.IndexOf(dr)][0]);
                                }
                                //}
                                Tablecount++;
                            }
 
                            dbcommand.ExecuteNonQuery();

Thanks Loftty,

[jmcilhinney]

Firstly, parameters correspond to columns, not rows, so the number of parameters has no connection whatsoever to the number of records. You have one parameter and save a million records and vice versa, or any combination in between.

Secondly, what is this supposed to achieve:

ds.Tables[0].Rows[ds.Tables[0].Rows.IndexOf(dr)][0]

that wouldn't be achieved by this:

dr[0]

Finally, you have misunderstood how to use parameters with a Datatable. You're only calling ExecuteNonQuery once so you're only going to affect one row. The idea is that you add parameters that know the column in your DataTable from which to draw the data. You then call the Update method of a DataAdapter and it handles getting the data from each individual row.