Change your code as follow and tell me if it works:
3 public static void DisplayDataTable(
4
5 DataTable myDataTable
6 )
7 {
8 SqlConnection mySqlConnection = new SqlConnection(connectionString);
9
10 DataSet myDataSet = new DataSet();
11 // formulate a SELECT statement to retrieve the
12 string selectString =
13 "SELECT ID, firstname, lastname " +
14 "FROM excelTest " ;
15 // use the Clear() method of the DataSet object
16 // to remove all the rows in the DataSet
17
18 // create a SqlCommand object to hold the SELECT statement
19 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
20
21 // set the CommandText property of the SqlCommand object to
22 // the SELECT string
23 mySqlCommand.CommandText = selectString;
24
25 // create a SqlDataAdapter object
26 SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
27
28 // set the SelectCommand property of the SqlAdapter object
29 // to the SqlCommand object
30 mySqlDataAdapter.SelectCommand = mySqlCommand;
31
32 myDataSet.Clear();
33
34 // use the Fill() method of the SqlDataAdapter object
35 // to synchronize any changes made to the database
36 // with the DataSet object
37 mySqlDataAdapter.Fill(myDataSet, "ExcelTable");
38
mySqlConnection.Open();
mysqlCommand = new SqlCommand();
mySqlCommand.CommandType = CommandType.StoredProcedure;
46 mySqlCommand.CommandText = "insExcelTest";
47 mySqlCommand.Connection = mySqlConnection;
48
49 mySqlCommand.Parameters.Add("@candidate_id", SqlDbType.Int, 4);
50 mySqlCommand.Parameters.Add("@firstname", SqlDbType.NVarChar, 50);
51 mySqlCommand.Parameters.Add("@lastname", SqlDbType.NVarChar, 50);
52
39
40 // display the columns for each row in the DataTable,
41 // using a DataRow object to access each row in the DataTable
42 foreach (DataRow myDataRow in myDataTable.Rows)
43 {
53
54 mySqlCommand.Parameters["@candidate_id"].Value = myDataRow["ID"];
55 mySqlCommand.Parameters["@firstname"].Value = myDataRow["firstname"];
56 mySqlCommand.Parameters["@lastname"].Value = myDataRow["lastname"];
57
58
59
60
61 mySqlCommand.ExecuteNonQuery();
62
63
64
65
66 }
mySqlConnection.Close();
|