locked
Only 1 column is added to my MySQLDB from Excel File RRS feed

  • Question

  • User-1956115249 posted

    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

    Saturday, December 5, 2015 11:52 AM

All replies

  • User269602965 posted

    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");
    	}
    } 

     

    Monday, December 7, 2015 12:09 AM