ASP.NET - importing csv file and storing in to sql server problem occur

Asked By aman on 13-Nov-11 01:40 AM
hi all,

i am importing csv file data and trying to store data in to sql server database table but the following problem occurs

Invalid column name 'StudentName'.
Invalid column name 'RollNo'.
Invalid column name 'Course'.


below is my code snippet and my table structure.

        private void ReadData()
        {
            string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
            StreamReader sr = new StreamReader(Server.MapPath("")+filename);
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            SqlConnection con = new SqlConnection(str1);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            while (!sr.EndOfStream)
            {
                string s = sr.ReadLine();
                //SqlDataAdapter adap = new SqlDataAdapter("insert into Student values(" + s.Split(',')[0] + "," + s.Split(',')[1] + "," + s.Split(',')[2] + "",con);
                com.CommandText = "insert into Student values(" + s.Split(',')[0] + "," + s.Split(',')[1] + "," + s.Split(',')[2] + ")";
                com.ExecuteNonQuery();
            }
            sr.Close();
            com.Connection.Close();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            //importDataFromExcel();
            //ImportExcelData();
            if (FileUpload1.HasFile)
            {
                string type = FileUpload1.PostedFile.ContentType;
                string length = FileUpload1.PostedFile.ContentLength.ToString();
                string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string location = Server.MapPath("")+ filename;
                try
                {
                    FileUpload1.SaveAs(location);
                    Label1.Text = "<b> File Name: " + filename + "<br><b> Size:" + length + " kb<br>" + "Content Type: " + type + "<br><b>Uploaded Successfully";
                }
                catch (Exception ex)
                {
                    Label1.Text = "Error : " + ex.Message.ToString();
                }
            }
            else
            {
                Label1.Text = "Please Select a File.";
            }
            ReadData();
        }

below is how my table look like......

ID int
StudentName nvarchar(50)
RollNo int
Course nvarchar(50)

and below is the file from which i am trying to import.
Test.txt is the file name for example.
StudentName,RollNo,Course
'Feroz',09,'Science'
'Kalim',31,'Commerce'


Kirtan Patel replied to aman on 13-Nov-11 01:54 AM
Uploading
protected void UploadButton_Click(object sender, EventArgs e)
{
  if (FileUploader.HasFile)
  {
  FileUploader.SaveAs(Server.MapPath("confirm//") +
      FileUploader.FileName);
  Label1.Text = "File name: " +
      FileUploader.PostedFile.FileName + "<br>" +
      FileUploader.PostedFile.ContentLength + " kb<br>" +
      "Content type: " +
      FileUploader.PostedFile.ContentType + "<br><b>Uploaded Successfully";
  }
  else
  {
    Label1.Text = "You have not specified a file.";
  }
}
 
Importing
 
private void ReadRec()
{
  StreamReader Sr = new StreamReader(Server.MapPath(".") + "sample.txt");
  System.Text.StringBuilder sb = new System.Text.StringBuilder();
  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()
  string s;
  // do your connection stuff here:
  while(!Sr.EndOfStream)
  {
    s = Sr.ReadLine();
    cmd.CommandText =  "INSERT INTO MyTable Field1, Field2, Field3 VALUES(" +  s.Split(',')[1] + ", " + s.Split(',')[2] + ", " + s.Split(',')[3]   + ")";
    cmd.ExecuteNonQuery();
  }
  Sr.Close();
  cmd.Connection.Close();
}
 
Neha Garg replied to aman on 13-Nov-11 02:47 AM
Hello Aman,

Here is the Code to Import and store the csv file into Database:
Here is the Function to import the CSV Data:

public DataSet ConnectCSV (string filetable)
{
  DataSet ds = new DataSet ();
  try
   {     
     string strConnString=
       "Driver={Microsoft Text Driver (*.txt;*.csv)};
       Dbq="+txtCSVFolderPath.Text.Trim()+";
       Extensions=asc,csv,tab,txt;
       Persist Security Info=False";
 
     string sql_select;                 
     System.Data.Odbc.OdbcConnection conn;     
   
     //Create connection to CSV file
     conn = new System.Data.Odbc.OdbcConnection(
                  strConnString.Trim ());
   
     //Open the connection
     conn.Open ();
     //Fetch records from CSV
     sql_select="select * from ["+ filetable +"]";
         
    obj_oledb_da=new System.Data.Odbc.OdbcDataAdapter(
                        sql_select,conn);
 
     obj_oledb_da.Fill(ds,"Stocks");
         
    //Set the datagrid properties
        
     dGridCSVdata.DataSource=ds;
     dGridCSVdata.DataMember="Stocks";
     //Close Connection to CSV file
     conn.Close ();         
   }
   catch (Exception e)
   {
     MessageBox.Show (e.Message);
   }
   return ds;


Below the Code to Insert the CSV Data:

private void btnUpload_Click(object sender,
                System.EventArgs e)
{
 try
  {
  SqlConnection  con1=
     new SqlConnection(ReadConFile().Trim());
  SqlCommand cmd = new SqlCommand();
  SqlCommand cmd1 = new SqlCommand();
  DataSet da = new DataSet();
 
   da=this.ConnectCSV(strCSVFile);   
  cmd.Connection=con1;
  cmd.CommandType=CommandType.Text;
  cmd1.Connection=con1;
  cmd1.CommandType=CommandType.Text;
           
  con1.Open();
  for(int i=0;i<=da.Tables["Stocks"].Rows.Count-1;i++)
  {             
    for(int j=1;j<=da.Tables["Stocks"].Columns.Count-1;j++)
    {
    cmd.CommandText=
      "Insert  into Test(srno,
       "+da.Tables["Stocks"].Columns[0].ColumnName.Trim()+")
      values("+(i+1)+",
       '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(0)+"')";
 
    cmd1.CommandText=
      "Update Test set "
        +da.Tables["Stocks"].Columns[j].ColumnName.Trim()+"
        = '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j)+
      "' where srno ="+(i+1);               
    cmd.ExecuteNonQuery();
    cmd1.ExecuteNonQuery();               
    }
  }
  con1.Close();
  }
  catch(Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    btnUpload.Enabled=false;
  }
}

Jitendra Faye replied to aman on 13-Nov-11 11:57 PM
Here is working code-

protected

void Button1_Click(object sender, EventArgs e)

{

string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";

OleDbConnection con = new OleDbConnection(strProvier);

OleDbCommand cmd = new OleDbCommand();

int effectedRow = 0;

int lineCounter = 0;

con.Open();

if ((con.State.ToString() == "Open"))

{

StreamReader stReader = new StreamReader("Employees.csv");

string[] strRowData = null;

while (stReader.Peek() >= 0)

{

lineCounter = lineCounter + 1;

strRowData = stReader.ReadLine().Split(

",");

try

{

cmd.CommandText =

"INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" + strRowData(1) + "','" + strRowData(2) + "')";

cmd.Connection = con;

effectedRow = cmd.ExecuteNonQuery();

if ((effectedRow == -1))

{

// Messagebox.Show("Line: " + lineCounter + " Error");

}

else

{

// Messagebox.Show("Line: " + lineCounter + " Executed Successfully");

}

}

catch (OleDbException er)

{

Messagebox.Show(

"Line: " + lineCounter + " Error: " + er.Message);

}

}

stReader.Close();

con.Close();

}

else

{

Messagebox.Show(

"Not Connected To Database");

}

}



Try this and let me know.l