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
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
Having very hard time with this. Please help. Thanks
Contributor
3532 Points
1348 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