Only 1 column is added to my MySQLDB from Excel FileRSS

• • Reply
  vom12

  None

  0 Points

  1 Post

  Only 1 column is added to my MySQLDB from Excel File

  Dec 05, 2015 11:52 AM|vom12|LINK

  I am trying to insert data from Excel file to Mysql DB. It works but the problem is, it insert just 1 column. I need to insert all the column from my Excel file

  Here is my Code

  protected void add_Click(object sender, EventArgs e)
  {
  
      if (importfile.HasFile)
      {
          string path = string.Concat((Server.MapPath("~/temp/" + importfile.FileName)));
          importfile.PostedFile.SaveAs(path);
          OleDbConnection oleConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;");
          OleDbCommand oleCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oleConn);
          OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCmd);
          DataSet ds = new DataSet();
          oleAdapter.Fill(ds);
          DataTable Dt = new DataTable();
          Dt = ds.Tables[0];
          string userId = (string)(Session["id"]);
          string groupName = groupname.Text.ToString();
          try {
              for (int i = 0; i < Dt.Rows.Count; i++)
              {
                  DataRow row = Dt.Rows[i];
                  int columnCount = Dt.Columns.Count;
                  string[] columns = new string[columnCount];
                  for (int j = 0; j < columnCount; j++)
                  {
                      columns[j] = row[j].ToString();
                  }
                  conn.Open();
                  string sql = "INSERT INTO contacts(user_id, idNum, contact_name, contact_number, group_name)";
                  sql += "VALUES('" + userId + "','" + columns[0] + "','" + columns[1] + "','" + columns[2] + "','" + groupName + "')";
                  MySqlCommand cmd = new MySqlCommand(sql, conn);
                  int x = cmd.ExecuteNonQuery();
                  if (x > 0)
                  {
                      string message = "Success!";
                      System.Text.StringBuilder sb = new System.Text.StringBuilder();
                      sb.Append("<script type = 'text/javascript'>");
                      sb.Append("window.onload = function(){");
                      sb.Append("alert('");
                      sb.Append(message);
                      sb.Append("')};");
                      sb.Append("</script>");
  
                      ClientScript.RegisterStartupScript(this.GetType(), "alert", sb.ToString());
                      Response.Redirect("viewgroups.aspx");
                      conn.Close();
                  }
              }
          }
          catch (Exception)
          {
                  string message = "Failed!";
                  System.Text.StringBuilder sb = new System.Text.StringBuilder();
                  sb.Append("<script type = 'text/javascript'>");
                  sb.Append("window.onload = function(){");
                  sb.Append("alert('");
                  sb.Append(message);
                  sb.Append("')};");
                  sb.Append("</script>");
  
                  ClientScript.RegisterStartupScript(this.GetType(), "alert", sb.ToString());
  
          }

  Having very hard time with this. Please help. Thanks

• • Reply
  Lannie

  Contributor

  3412 Points

  1329 Posts

  Re: Only 1 column is added to my MySQLDB from Excel File

  Dec 07, 2015 12:09 AM|Lannie|LINK

  It is better to use Oracle PARAMETERS and BIND VARIABLES for your VALUES in INSERT statement

  using System;
  using System.Xml.Linq;
  
    public static void updateUnitsActiveFlag(decimal decQuantity, string strDescription, DateTime dateDateClosed)
    {
  	// Insert Quantity into new row Units table
  	string OraConnStr = ConfigurationManager.ConnectionStrings["OraConnStr"].ConnectionString;
  	try
  	{
  	  var SQL = System.Xml.Linq.XElement.Parse("<SQL> INSERT INTO {YOURSCHEMANAME}.UNITS(UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) VALUES(UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)</SQL>");
  	  using (OracleConnection conn = new OracleConnection(OraConnStr))
  	  {
  		using (OracleCommand cmd = new OracleCommand(SQL.Value, conn))
  		{
  		  cmd.Parameters.Clear();
  		  cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input);
  		  cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input);
  		  cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input);
  		  conn.Open();
  		  cmd.ExecuteNonQuery();
  		}
  	  }
  	}
  	catch (Exception ex)
  	{
  	  AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb");
  	}
  }