This is the excel file I am trying to import.I am getting a strange error on my C# asp.net web solution. When trying to import an excel file to the DB I am getting the error "Invalid column name 'DSI', but this is not a name of a column. 'DSI' is the text for the field not the column name.
My code:
protected void Button1_Click(object sender, EventArgs e)
{
try
{
conn.Open();
SqlCommand cmddel = new SqlCommand("DELETE FROM VW_EMP_FORN1", conn);
cmddel.ExecuteNonQuery();
conn.Close();
LblDel.Visible = true;
LblDel.Text = "Dados anteriores apagados!";
LblDel.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
LblDel.Text = ex.ToString();
LblDel.ForeColor = System.Drawing.Color.Red;
LblDel.Visible = true;
}
try
{
string CCU_Agregado;
string EMPRESA;
string ENTIDADE_NOME;
string ENTIDADE_CODIGO;
string CCU_Nome;
string CCU_Negocio_Refletido;
string PCO_DES_REFLETIDO;
string PCO_COD_REFLETIDO;
string path = Path.GetFileName(FPRL1.FileName);
path = path.Replace(" ","");
FPRL1.SaveAs(Server.MapPath("~/excelfolder/") + path);
string ExcelPath = Server.MapPath("~/excelfolder/") + path;
OleDbConnection mycon = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + ExcelPath + "; Extended Properties=Excel 8.0; Persist Security Info = False");
//OleDbConnection mycon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + ExcelPath + "; Extended Properties=Excel 8.0; Persist Security Info = False");
mycon.Open();
OleDbCommand cmd = new OleDbCommand("select * from [Hoja1$]", mycon);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CCU_Agregado = dr[0].ToString();
EMPRESA = dr[1].ToString();
ENTIDADE_NOME = dr[2].ToString();
ENTIDADE_CODIGO = dr[3].ToString();
CCU_Nome = dr[4].ToString();
CCU_Negocio_Refletido = dr[5].ToString();
PCO_DES_REFLETIDO = dr[6].ToString();
PCO_COD_REFLETIDO = dr[7].ToString();
savedata(CCU_Agregado, EMPRESA, ENTIDADE_NOME, ENTIDADE_CODIGO, CCU_Nome, CCU_Negocio_Refletido, PCO_DES_REFLETIDO, PCO_COD_REFLETIDO);
LblStatus.Visible = true;
LblStatus.Text = "Excel importado com sucesso para a tabela!";
LblStatus.ForeColor = System.Drawing.Color.Green;
try
{
SqlCommand CMDJ = new SqlCommand("RUN_SGP", conn);
CMDJ.CommandTimeout = 5000;
CMDJ.CommandType = CommandType.StoredProcedure;
conn.Close();
LblStatus.Visible = true;
LblStatus.Text = "Job final corrido com sucesso!";
LblStatus.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
LblJob.Text = ex.ToString();
LblJob.ForeColor = System.Drawing.Color.Red;
LblJob.Visible = true;
}
}
}
catch (Exception ex)
{
LblStatus.Text = ex.ToString();
LblStatus.ForeColor = System.Drawing.Color.Red;
LblStatus.Visible = true;
}
}
private string GetUserIP()
{
string ipList = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (!string.IsNullOrEmpty(ipList))
{
return ipList.Split(',')[0];
}
return Request.ServerVariables["REMOTE_ADDR"];
}
private void savedata(string CCU_Agregado1, string EMPRESA1, string ENTIDADE_NOME1, string ENTIDADE_CODIGO1, string CCU_Nome1, string CCU_Negocio_Refletido1, string PCO_DES_REFLETIDO1, string PCO_COD_REFLETIDO1)
{
String query = "insert into VW_EMP_FORN1(CCU_Agregado, EMPRESA, ENTIDADE_NOME, ENTIDADE_CODIGO, CCU_Nome, CCU_Negocio_Refletido, PCO_DES_REFLETIDO, PCO_COD_REFLETIDO) values(" + CCU_Agregado1 + ",'" + EMPRESA1 + "','" + ENTIDADE_NOME1 + "','" + ENTIDADE_CODIGO1 + "','" + CCU_Nome1 + "','" + CCU_Negocio_Refletido1 + "','" + PCO_DES_REFLETIDO1 + "','" + PCO_COD_REFLETIDO1 + "')";
SqlConnection con = new SqlConnection(mainconn);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = con;
cmd.ExecuteNonQuery();
conn.Close();
}
}
I get this error:
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'DSI'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)
1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Finance_Parfois.WPAGES.02INTERNAL.TESOURARIA._02TesSGPSaldosAberto.savedata(String CCU_Agregado1, String EMPRESA1, String ENTIDADE_NOME1, String ENTIDADE_CODIGO1, String CCU_Nome1, String CCU_Negocio_Refletido1, String PCO_DES_REFLETIDO1, String PCO_COD_REFLETIDO1) in C:\Users\Ricardo Loureiro\Desktop\Finance_Parfois_SLN_OLD\Finance_Parfois\WPAGES\02INTERNAL\TESOURARIA_\02TesSGPSaldosAberto.aspx.cs:line 142 at Finance_Parfois.WPAGES.02INTERNAL.TESOURARIA._02TesSGPSaldosAberto.Button1_Click(Object sender, EventArgs e) in C:\Users\Ricardo Loureiro\Desktop\Finance_Parfois_SLN_OLD\Finance_Parfois\WPAGES\02INTERNAL\TESOURARIA_\02TesSGPSaldosAberto.aspx.cs:line 94 ClientConnectionId:1f2f9f5a-6e60-4c5f-8800-1e11fc819a86 Error Number:207,State:1,Class:16
Can someone help me coping with this error?
Best regards,
RL
User contributions licensed under CC BY-SA 3.0