Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 7th, 2007, 02:25 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
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
  #2 (permalink)  
Old August 7th, 2007, 09:47 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: noida, UP, India.
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();
  #3 (permalink)  
Old August 7th, 2007, 10:05 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
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 :)

  #4 (permalink)  
Old August 7th, 2007, 11:56 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: noida, UP, India.
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

You are most welcome.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:11 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.