Mình có đoạn mã này chạy thì không báo lổi nhưng ko hiểu sao không import được dữ liệu từ excel vào SQL
private void btnImportExcel_Click(object sender, EventArgs e)
{
objMain = (frmMain)this.MdiParent;
System.Data.OleDb.OleDbConnection oleCnn = new System.Data.OleDb.OleDbConnection();
if (txtFilePath.Text.Trim() == "")
{
MessageBox.Show(this, "The file path is not empty");
txtFilePath.Focus();
return;
}
else
{
try
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + txtFilePath.Text.Trim() + ";" + "Extended Properties=Excel 8.0;";
oleCnn.ConnectionString = sConnectionString;
System.Data.OleDb.OleDbDataAdapter oleDa = new System.Data.OleDb.OleDbDataAdapter();
oleDa.SelectCommand = new System.Data.OleDb.OleDbCommand("Select * from [Sheet$]", oleCnn);
DataTable dtExcel = new DataTable();
oleDa.Fill(dtExcel);
// strSql = "Select *from tb_soluongnhap";
//commd = new SqlCommand(strSql, objMain.conn);
System.Data.SqlClient.SqlDataAdapter sqlDa = new System.Data.SqlClient.SqlDataAdapter();
sqlDa.SelectCommand = new System.Data.SqlClient.SqlCommand("Select * from tb_xuat", objMain.conn);
DataTable dtSql = new DataTable();
sqlDa.Fill(dtSql);
dtSql.PrimaryKey = new DataColumn[] { dtSql.Columns["soxe"] }; //To find an ID exists?
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("Insert into tb_xuat values(@makho, @soxe, @makhach, @khachhang, @zipcode, @diadiem, @ngaydi)", objMain.conn);
cmd.Parameters.Add("@makho", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@soxe", SqlDbType.Int, 10);
cmd.Parameters.Add("@makhach", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@khachhang", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@zipcode", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@diadiem", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@ngaydi", SqlDbType.DateTime);
objMain.conn.Open();
cmd.Prepare();
int nRowAddedCount = 0;
foreach (DataRow row in dtExcel.Rows)
{
if (dtSql.Rows.Find(row["soxe"]) != null)
{
MessageBox.Show("Row with soxe is " + row["soxe"] + " already exists.");
}
else
{
cmd.Parameters["@makho"].Value = row["makho"].ToString();
cmd.Parameters["@soxe"].Value = row["soxe"].ToString() ;
cmd.Parameters["@makhach"].Value = row["makhach"].ToString();
cmd.Parameters["@khachang"].Value = row["khachhang"].ToString();
cmd.Parameters["@zipcode"].Value = row["zipcode"].ToString();
cmd.Parameters["@ngaydi"].Value = row["ngaydi"].ToString();
cmd.ExecuteNonQuery();
nRowAddedCount++;
}
}
MessageBox.Show("There are " + nRowAddedCount.ToString() + " added row(s).");
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(objMain .xNew, "Import successfully", "Successfull", MessageBoxButtons.OK, MessageBoxIcon.Information); ;
}
finally
{
if (objMain.conn.State == ConnectionState.Open)
objMain.conn.Close();
if (oleCnn.State == ConnectionState.Open)
oleCnn.Close();
}
fillExcel();
}
}
Có cái thẻ code thì bỏ nó vào. Nhìn rối mắt quá bạn.
Chỗ này dùng để ghi chữ ký