Upoload CSV file in GridView

Question

Hello, I am trying to upload .CSV file in gridview of asp.net i am successfully upload that file in gridview now i want to store that uploaded gridview data in sql server on button click.

Khargesh Rajput · Answer

write dt.Acceptchanges(); after adding row in dt

Vishnu Bhangale · Answer

Hello sir , i try it but in dt it shows only header row and data is come in to dt

Khargesh Rajput · Answer

debug your code and check whether dt has values or not

Vishnu Bhangale · Answer

Hello sir if i change the datatype of Mobile as varchar it run but in database table it shows no record means blanks record

Vishnu Bhangale · Answer

Hello sir, I already try this but give error: Input string was not in a correct format. My DB Table structure is like UserName varchar(50), Email varchar(50), Mobile int

Khargesh Rajput · Answer

foreach (GridViewRow row in gridview1.Rows) { string UserName = row.Cells[0].Text; string Email = row.Cells[1].Text; int Mobile = int.Parse(row.Cells[2].Text); dt.Rows.Add(UserName, Email, Mobile); }

Vishnu Bhangale · Answer

Hello sir, In my CSV file contain 3 column UserName,Email,Mobile protected void btn_Add_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn("UserName", typeof(string)), new DataColumn("Email", typeof(string)), new DataColumn("Mobile",typeof(int)) }); foreach (GridViewRow row in gridview1.Rows) { string UserName = row.Cells[1].Text; string Email = row.Cells[2].Text; int Mobile = int.Parse(row.Cells[3].Text); dt.Rows.Add(UserName, Email, Mobile); } if (dt.Rows.Count > 0) { string consString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = "dbo.CSV"; //[OPTIONAL]: Map the DataTable columns with that of the database table sqlBulkCopy.ColumnMappings.Add("UserName", "UserName"); sqlBulkCopy.ColumnMappings.Add("Email", "Email"); sqlBulkCopy.ColumnMappings.Add("Mobile", "Mobile"); con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } } }

Khargesh Rajput · Answer

plz share your code. you have to match the columns in csv and database

Vishnu Bhangale · Answer

Hello sir,I try it but i got error: Specified argument was out of the range of valid values. I try to solve it but not get any solution..

Khargesh Rajput · Answer

If you have inserted csv data to gridview then use bulk insert to sql or insert each row to sql db http://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-insert-bulk-data-from-GridView-to-database-in-ASPNet.aspx http://www.c-sharpcorner.com/UploadFile/0c1bb2/insert-bulk-records-into-database-using-Asp-Net-C-Sharp/ http://aspforums.net/Threads/155716/Bulk-Insert-Data-from-GridView-Rows-into-Database-in-ASPNet/