Wrox Programmer Forums
|
ASP.NET 1.x and 2.0 Application Design Application design with ASP.NET 1.0, 1.1, and 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.x and 2.0 Application Design section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 7th, 2007, 02:25 AM
Authorized User
 
Join Date: Oct 2003
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default DataTable and Database

Hi,

Code:
Hi

Hi,

 

I have a winform application that gets data from an excel file and places it in a datatable. I then need to insert those values into my database by calling a stored procedure.

 I have the following code but it gives me an error:
Procedure or function insExcelTest has too many arguments specified.

The first set of records from the excel file is entered into the database. This error occurs the second time it enters the loop. When i run SQL Profiler I see that it tries to insert the 1st record again and therefore there are more arguments passed then in the sp. 
I am not sure how to modify my code to insert into the database as it should.

Any help will be greatly appreciated. 
Thanks
 

myDataTable contains the information from the excel file.

 
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   
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               {
44   
45                   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   
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                   mySqlConnection.Open();
61                    mySqlCommand.ExecuteNonQuery();
62   
63                   mySqlConnection.Close();
64                   
65                  
66           }
67   
68   
69   
70
 
Old August 7th, 2007, 09:47 AM
Friend of Wrox
 
Join Date: Feb 2006
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

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();
 
Old August 7th, 2007, 10:05 AM
Authorized User
 
Join Date: Oct 2003
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default

wow i don't know how to thank you enough. i have been on this for about 2 days. your solution worked.
thanks sooooo much :)

 
Old August 7th, 2007, 11:56 AM
Friend of Wrox
 
Join Date: Feb 2006
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

You are most welcome.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Datatable Limno .NET Framework 1.x 0 May 28th, 2008 02:37 PM
Datatable access w.rosellini Visual C++ 2005 0 August 23rd, 2007 10:06 AM
Datagrid + datatable edukulla C# 2005 2 September 11th, 2006 04:53 PM
how to use DataTable.Select Salte C# 2 December 22nd, 2004 04:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.