0
I have a dotnet application to import spreadsheet, but I don’t know why I’m not able to enter the information in the database. My code is below.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
protected void importar_Click(object sender, EventArgs e)
{
//Carregar e salvar o arquivo
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(selecionarArquivo.PostedFile.FileName);
string conString = string.Empty;
string extension = Path.GetExtension(selecionarArquivo.PostedFile.FileName);
//Validação
//Valida a extensão do arquivo:
if (Path.GetExtension(selecionarArquivo.FileName) != ".xlsx" && Path.GetExtension(selecionarArquivo.FileName) != ".xls")
{
}
else
{
ClientScript.RegisterStartupScript(typeof(string), "Erro", "<script>alert('Somente arquivos em excel')</script>");
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open(); //->>>> Aqui está dando um erro estranho a propriedade connectionstring não foi inicializada.
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[6]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: Recomenda-se, caso contrário, os dados serão considerados como String por padrão.
dtExcelData.Columns.AddRange(new DataColumn[] { new DataColumn("Id", typeof(int)), // [] passar o valor do campo da tabela do bd
// Aqui colocarei o campo da planilha de excel
new DataColumn("Data de Requisição do Pgto", typeof(DateTime)),
new DataColumn("NUMERO PROCESSO ANTERIOR",typeof(Int32)),
new DataColumn("NUMERO JUDICIAL", typeof(Int32)),
new DataColumn("COMARCA", typeof(string)),
new DataColumn("VARA", typeof(string)),
new DataColumn("NOME", typeof(string)),
new DataColumn("CPF", typeof(Int16)),
new DataColumn("DEENDERECO", typeof(string)),
new DataColumn("NUENDERECO", typeof(string)),
new DataColumn("DECOMPLEMENTO", typeof(string)),
new DataColumn("DEBAIRRO", typeof(string)),
new DataColumn("NUCEP", typeof(Int16)),
new DataColumn("NMMUNICIPIO", typeof(string)),
new DataColumn("BRUTO", typeof(decimal)),
new DataColumn("IRPF", typeof(decimal)),
new DataColumn("Nº SEP", typeof(Int16)),
new DataColumn("DATA SEP", typeof(DateTime)),
new DataColumn("DATA DE RECEBIMENTO NA ORIGEM", typeof(DateTime)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM sdpj_import_processo", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Define o nome da tabela
sqlBulkCopy.DestinationTableName = "@sdpj_import_processo";
//[OPTIONAL]: Mapeie as colunas do Excel com a da tabela do banco de dados
sqlBulkCopy.ColumnMappings.Add("Data de Requisição Pgto", "DAT_REQ_PGTO");
sqlBulkCopy.ColumnMappings.Add("NUMERO PROCESSO ANTERIOR", "NUM_PROC_ANTERIOR");
sqlBulkCopy.ColumnMappings.Add("NUMERO JUDICIAL", "NUM_PROC_JUDICIAL");
sqlBulkCopy.ColumnMappings.Add("COMARCA", "DSC_COMARCA");
sqlBulkCopy.ColumnMappings.Add("VARA", "NUM_VARA");
sqlBulkCopy.ColumnMappings.Add("VARANOME", "NME_VARA");
sqlBulkCopy.ColumnMappings.Add("NOME", "NME_INTERESSADO");
sqlBulkCopy.ColumnMappings.Add("CPF", "CPF_CNPJ_INTERESSADO");
sqlBulkCopy.ColumnMappings.Add("DEENDERECO", "DSC_ENDERECO");
sqlBulkCopy.ColumnMappings.Add("NUENDERECO", "NUM_ENDERECO");
sqlBulkCopy.ColumnMappings.Add("NMMUNICIPIO", "NME_MUNICIPIO");
sqlBulkCopy.ColumnMappings.Add("DECOMPLEMENTO", "DSC_DECOMPLEMENTO");
sqlBulkCopy.ColumnMappings.Add("DEBAIRRO", "NME_BAIRRO");
sqlBulkCopy.ColumnMappings.Add("NUCEP", "NUM_CEP");
sqlBulkCopy.ColumnMappings.Add("NMMUNICIPIO", "NME_MUNICIPIO");
sqlBulkCopy.ColumnMappings.Add("BRUTO", "VLR_BRUTO");
sqlBulkCopy.ColumnMappings.Add("IRPF", "VLR_IR");
sqlBulkCopy.ColumnMappings.Add("LIQUIDO", "VLR_LIQUIDO");
sqlBulkCopy.ColumnMappings.Add("Nº SEP", "COD_SEP");
sqlBulkCopy.ColumnMappings.Add("DATA SEP", "DAT_SEP");
sqlBulkCopy.ColumnMappings.Add("DATA DE RECEBIMENTO NA ORIGEM", "DAT_RECEB_ORIGEM");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
Is generating some exception ?
– JcSaint
Name the Message it gives is: Server Error in the/' Application. The Connectionstring property has not been initialized. Description: An untreated exception occurred during the execution of the current web request. Examine stack tracking for more information about the error and where it originated in the code. Exception Details: System.Invalidoperationexception: The Connectionstring property has not been initialized.
– Amaral
You started the variable
conString
empty, then tried to format it, and then tried to start aOleDbConnection
with it still empty, then the error seems clear to me. Assign a valid connection string to the variable.– Ricardo Pontual
Friend, could you give me an example, I didn’t understand.
– Amaral
@Amaral, your variable
constr
in the first part of reading excel you do not assign any value to it and then in Bulk copy, you have checked if it is actually declared in your settings file in the key"constr"
?– Leandro Angelo
Go to <a href="https://www.connectionstrings.com/excel/">connectionstrings.com</a> and take a valid connection string, change to your need and arrow in variable
consString
, so by example:conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath
– Ricardo Pontual