I have an .CSV file which contain reviews and I am to programitically transfer the contents over into SQL Database from an FileUpload control. The problem I have is that my control are working but I dont see any data being populated in my database. Here
is my code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
I realize that having an connection is kinda pointless when I could just reading the contents from the FileUpload but if anyone has an idea of what I am doing wrong or could guide to a proper direction that would be greatly appreciated. Thanks for your time
Do you are executing this app in a server and the file is in the same server?
The control FileUpload has the path of the file, then before to use in your connectionstring do you should to do Save to a folder in this server and concat the path this file.
Occour any error in your app or simply doesn't work ?
Kindly mark this post as "Answer", if it helped you.
Yes, the application and the file are in the same server and yes I am also saving the file and concating my connectionstring path to folder I saved my file in. The thing is I dont get an error, my execption shows as ..."Data Inserted Sucessfully". However,
when I check my database, I dont see the tables populated with the data from the .CSV file so obliviously some part of the code is not working or not the suitible for this application.
Alright...after long 6 hours and with lots with coffee, I managed to get this to work...couple things first..
- I am importing a .CSV file, so your connection string is going to be different then most excel connection string
- Second, make sure you dispose your file cuz that gives you problems for file in use even though its closed, but it will crash if its open (I havent worked on that part of the code yet)
- Third, if your importing data to a database make sure your datatypes are right..for example, i had a lot of data coming and it was being traucated so i had to change my datatypes to proper format.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile) //Upload file here
{
if (fileExt == ".csv") //check to see if its a .csv file
{
FileUpload1.SaveAs("C:\\AudioPRFiles\\" + FileUpload1.FileName); //save file to the specified folder
OleDbConnection oconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AudioPRFiles\\; Extended Properties='text; HDR=Yes; FMT=Delimited'");
//string connection for .CSV OR Text file
try
{
OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + FileUpload1.FileName + "]", oconn);
//Select statement, if your using .CSV...put the name of the file NOT the excel tab
protected string valid(OleDbDataReader myreader, int stval) //this method checks for null values in the .CSV file, if there are null replace them with 0
{
object val = myreader[stval];
if (val != DBNull.Value)
{
I hope this helps anyone trying to do the same thing I was, if you guys see something that should be changed, I would love to hear your views. Also, much thanks to jypelton for recommending to remove the finally part, ..dunno why I didn't think of that
what does your catch exception say? if you have the finally part in your code..remove it for now to catch your exception. That should tell you why its not working. I had the same problem until i removed the finally part to see the exception.
brownsound90
Member
14 Points
8 Posts
Import Data from .CSV to SQL from FileUpload
May 02, 2012 03:08 PM|LINK
Hey,
I have an .CSV file which contain reviews and I am to programitically transfer the contents over into SQL Database from an FileUpload control. The problem I have is that my control are working but I dont see any data being populated in my database. Here is my code:
MarkUp
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<asp:Label ID="Label1" runat="server"></asp:Label>
Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
if (fileExt == ".csv")
{
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + FileUpload1 + "; Extended Properties=Excel 8.0");
try
{
OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [AudioPR$]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string Device = "";
string Source = "";
string Reviewer = "";
string Datetime = "";
string Links = "";
string Content = "";
string Subject = "";
while (odr.Read())
{
Device = valid(odr, 0);
Source = valid(odr, 1);
Reviewer = valid(odr, 2);
Datetime = valid(odr, 3);
Links = valid(odr, 4);
Content = valid(odr, 5);
Subject = valid(odr, 6);
InsertDataIntoSql(Device, Source, Reviewer, Datetime, Links, Content, Subject);
}
oconn.Close();
}
catch (Exception ee)
{
Label1.Text = ee.Message;
Label1.ForeColor = System.Drawing.Color.Red;
}
finally
{
Label1.Text = "Data Inserted Successfully";
Label1.ForeColor = System.Drawing.Color.Green;
}
}
else
{
Label1.Text = "Only .csv files allowed!";
}
}
else
{
Label1.Text = "You have not specified a file!";
}
}
protected string valid(OleDbDataReader myreader, int stval)
{
object val = myreader[stval];
if (val != DBNull.Value)
{
return val.ToString();
}
else
{
return Convert.ToString(0);
}
}
public void InsertDataIntoSql(string Device, string Source, string Reviewer, string Datetime, string Links, string Content, string Subject)
{
SqlConnection conn = new SqlConnection("Data Source=CI0000000879107\\BENSON;Initial Catalog=PRClips Mail;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO AudioPR(Device, Source, Reviewer, Datetime, Links, Content, Subject) VALUES(@Device, @Source, @Reviewer, @Datetime, @Links, @Content, @Subject)";
cmd.Parameters.Add("@Device", System.Data.SqlDbType.NVarChar).Value = Device;
cmd.Parameters.Add("@Source", System.Data.SqlDbType.NVarChar).Value = Source;
cmd.Parameters.Add("@Reviewer", System.Data.SqlDbType.NVarChar).Value = Reviewer;
cmd.Parameters.Add("@Datetime", System.Data.SqlDbType.Date).Value = Datetime;
cmd.Parameters.Add("@Links", System.Data.SqlDbType.NVarChar).Value = Links;
cmd.Parameters.Add("@Content", System.Data.SqlDbType.NVarChar).Value = Content;
cmd.Parameters.Add("@Subject", System.Data.SqlDbType.NVarChar).Value = Subject;
cmd.CommandType = System.Data.CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
I realize that having an connection is kinda pointless when I could just reading the contents from the FileUpload but if anyone has an idea of what I am doing wrong or could guide to a proper direction that would be greatly appreciated. Thanks for your time
pierrefrc
Participant
947 Points
201 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 02, 2012 04:25 PM|LINK
Hello
Do you are executing this app in a server and the file is in the same server?
The control FileUpload has the path of the file, then before to use in your connectionstring do you should to do Save to a folder in this server and concat the path this file.
Occour any error in your app or simply doesn't work ?
brownsound90
Member
14 Points
8 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 02, 2012 05:23 PM|LINK
Hey,
Yes, the application and the file are in the same server and yes I am also saving the file and concating my connectionstring path to folder I saved my file in. The thing is I dont get an error, my execption shows as ..."Data Inserted Sucessfully". However, when I check my database, I dont see the tables populated with the data from the .CSV file so obliviously some part of the code is not working or not the suitible for this application.
Thanks
pierrefrc
Participant
947 Points
201 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 02, 2012 06:18 PM|LINK
Hi
Do you can post a piece of csv?
In table of Sql Server not inserted nothing line or any is empty or null ?
jypelton
Member
153 Points
154 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 02, 2012 06:48 PM|LINK
Take the finally out of your try catch to make sure you see the error.
jthomas8946
Member
181 Points
240 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 03, 2012 12:15 AM|LINK
Brownsound90,
I am looking to complete the same task. Once you have this completed could you please post your corrected code for both pages for reference?
Thanks,
-Jeff
brownsound90
Member
14 Points
8 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 03, 2012 09:39 PM|LINK
Alright...after long 6 hours and with lots with coffee, I managed to get this to work...couple things first..
- I am importing a .CSV file, so your connection string is going to be different then most excel connection string
- Second, make sure you dispose your file cuz that gives you problems for file in use even though its closed, but it will crash if its open (I havent worked on that part of the code yet)
- Third, if your importing data to a database make sure your datatypes are right..for example, i had a lot of data coming and it was being traucated so i had to change my datatypes to proper format.
MarkUp:
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<asp:Label ID="Label1" runat="server"></asp:Label>
Not much done here, just basic controls
C# Code Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile) //Upload file here
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName); //Get extension
if (fileExt == ".csv") //check to see if its a .csv file
{
FileUpload1.SaveAs("C:\\AudioPRFiles\\" + FileUpload1.FileName); //save file to the specified folder
OleDbConnection oconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AudioPRFiles\\; Extended Properties='text; HDR=Yes; FMT=Delimited'"); //string connection for .CSV OR Text file
try
{
OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + FileUpload1.FileName + "]", oconn); //Select statement, if your using .CSV...put the name of the file NOT the excel tab
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string Device = "";
string Source = "";
string Reviewer = "";
string Datetime = "";
string Links = "";
string Content = "";
string Subject = "";
while(odr.Read())
{
Device = valid(odr, 0); //Call the valid method...see below
Source = valid(odr, 1);
Reviewer = valid(odr, 2);
Datetime = valid(odr, 3);
Links = valid(odr, 4);
Content = valid(odr, 5);
Subject = valid(odr, 6);
InsertDataIntoSql(Device, Source, Reviewer, Datetime, Links, Content, Subject); //Call the InsertDataIntoSql method...see below
FileUpload1.Dispose(); //Dispose the file
}
oconn.Close(); //Close connection
}
catch (Exception ee)
{
Label1.Text = ee.Message;
Label1.ForeColor = System.Drawing.Color.Red;
}
finally
{
Label1.Text = "Data Inserted Successfully";
Label1.ForeColor = System.Drawing.Color.Green;
}
}
else
{
Label1.Text = "Only .csv files allowed!";
}
}
else
{
Label1.Text = "You have not specified a file!";
}
}
protected string valid(OleDbDataReader myreader, int stval) //this method checks for null values in the .CSV file, if there are null replace them with 0
{
object val = myreader[stval];
if (val != DBNull.Value)
{
return val.ToString();
}
else
{
return Convert.ToString(0);
}
}
public void InsertDataIntoSql(string Device, string Source, string Reviewer, string Datetime, string Links, string Content, string Subject) //method to insert data into database
{
SqlConnection conn = new SqlConnection("Server=CI0000000879107\\BENSON; Database=PRClips Mail; Trusted_Connection=True"); //SQL connection
SqlCommand cmd = new SqlCommand(); //SQL command
cmd.Connection = conn;
cmd.CommandText = "USE [PRClips Mail] INSERT INTO AudioPR(Device, Source, Reviewer, Datetime, Links, Content, Subject) VALUES(@Device, @Source, @Reviewer, @Datetime, @Links, @Content, @Subject)";
cmd.Parameters.Add("@Device", System.Data.SqlDbType.Int).Value = Device;
cmd.Parameters.Add("@Source", System.Data.SqlDbType.NVarChar).Value = Source;
cmd.Parameters.Add("@Reviewer", System.Data.SqlDbType.NVarChar).Value = Reviewer;
cmd.Parameters.Add("@Datetime", System.Data.SqlDbType.Date).Value = Datetime;
cmd.Parameters.Add("@Links", System.Data.SqlDbType.NVarChar).Value = Links;
cmd.Parameters.Add("@Content", System.Data.SqlDbType.NVarChar).Value = Content;
cmd.Parameters.Add("@Subject", System.Data.SqlDbType.NVarChar).Value = Subject;
cmd.CommandType = System.Data.CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
I hope this helps anyone trying to do the same thing I was, if you guys see something that should be changed, I would love to hear your views. Also, much thanks to jypelton for recommending to remove the finally part, ..dunno why I didn't think of that
Good luck
jthomas8946
Member
181 Points
240 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 07, 2012 12:00 PM|LINK
Brownsound90,
I attempted your code and although it shows that the import was successful, it is not populating the database. Thoughts?
Thanks,
-Jeff
vikvish1
Member
748 Points
337 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 07, 2012 12:53 PM|LINK
hope this might help u...
http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx
with regards
vik
vikvish
Howtouseasp.net
Useofaspdotnet/
brownsound90
Member
14 Points
8 Posts
Re: Import Data from .CSV to SQL from FileUpload
May 07, 2012 01:43 PM|LINK
what does your catch exception say? if you have the finally part in your code..remove it for now to catch your exception. That should tell you why its not working. I had the same problem until i removed the finally part to see the exception.