Currently I'm making a program to import values from multiple dBase files (.dbf). I have already an List full of Strings that have the name of the dBase files. That list is named FicheirosParaAnalisar. The dBase files not always have the same lines but they have all the same columns full of values that I want from one specific column named as sigla_parametro. The dBase filse are in path9 My code by now is something like this:
private void button6_Click(object sender, EventArgs e)
{
///*
//Console.WriteLine(sigla_parametro);
try
{
parametro = comboBox12.Text; //Console.WriteLine(parametro);
for (int FA = 0; FA < FicheirosParaAnalisar.Count; FA++)
{
//Console.WriteLine(FicheirosParaAnalisar[FA]+".dbf");
if (importar == true)
{
//........................................................................
string connectionStringTotal = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path9 + ";Extended Properties=dBASE III;";
OleDbConnection connectionsTotal = new OleDbConnection(connectionStringTotal);
connectionsTotal.Open();
// Comando de aquisição dos dados da Base de Dados
string Comand_Strings_Total = "Select * From [" + FicheirosParaAnalisar[FA] + "]";
OleDbDataAdapter DataAdaptersTotal = new OleDbDataAdapter(Comand_Strings_Total, connectionsTotal);
DataSet datasetsTotal = new DataSet();
DataAdaptersTotal.Fill(datasetsTotal);
// Obter os valores dos parâmetros
int count = datasetsTotal.Tables[0].Rows.Count;
for (int i = 0; i < count; i++)
{
try
{
valores_parametro.Add(FicheirosParaAnalisar[FA] + "_" + datasetsTotal.Tables[0].Rows[i][sigla_parametro].ToString());
}
catch (System.Data.OleDb.OleDbException exe)
{
string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + exe.Message + "\n";
System.IO.File.AppendAllText(log, text);
}
catch (Exception lolex)
{
string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + lolex.Message + "\n";
System.IO.File.AppendAllText(log, text);
}
}
count = 0;
}
Console.WriteLine("Ficheiro nº " + FA + " Importado!");
}
}
catch (Exception ex)
{
string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + ex.Message + "\n";
System.IO.File.AppendAllText(log, text);
}
//*/
valores_parametro.ForEach(Console.WriteLine);
button1.Enabled = true;
}
This code above only compute if I found all the dBase files (if so the variable importar becomes true). In theory I'm doing everything right, because the files exists and I have assigned every variable correctly, but really I have a problem in this code, because when I'm reading all the dBase files some files can't be read and an exception appear as I can see in my LogFile.txt. The message that appears is:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Now I want to ask you experts from Stackoverflow in C#, what I'm doing wrong? How can I get all the data from the column sigla_parametro to the List fo strings valores_parametro? How can I resolve my exception?
Note: I've also tried to use this Connection String
string connectionStringTotal = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path9 + ";Extended Properties=dBASE IV;";
The string path9 is the path to the folder that contains all my dBase files.
string path9 = "C:\\ProgramData\\CK electronic\\MPA5\\Data";
The List of strings FicheirosParaAnalisar has this strings:
CL-1831-150615-1
CL-1831-150615-2
CL-1831-150615-3
CL-1831-150615-4
CL-1831-150615-5
CL-1832-150611-1
CL-1832-150611-2
CL-1832-150611-3
CL-1832-150611-4
CL-1832-150611-5
CL-1833-150611-1
CL-1833-150611-2
CL-1833-150611-3
CL-1833-150611-4
CL-1833-150611-5
CL-1834-150611-1
CL-1834-150611-2
CL-1834-150611-3
CL-1834-150611-4
CL-1834-150611-5
CL-1835-150611-1
CL-1835-150611-2
CL-1835-150611-3
CL-1835-150611-4
CL-1835-150611-5
CL-1836-150612-1
CL-1836-150612-2
CL-1836-150612-3
CL-1836-150612-4
CL-1836-150612-5
CL-1837-150618-1
CL-1837-150618-2
CL-1837-150618-3
CL-1837-150618-4
CL-1837-150618-5
CL-1838-150611-1
CL-1838-150611-2
CL-1838-150611-3
CL-1838-150611-4
CL-1838-150611-5
CL-1839-150617-1
CL-1839-150617-2
CL-1839-150617-3
CL-1839-150617-4
CL-1839-150617-5
CL-1840-150611-1
CL-1840-150611-2
CL-1840-150611-3
CL-1840-150611-4
CL-1840-150611-5
CL-1841-150611-1
CL-1841-150611-2
CL-1841-150611-3
CL-1841-150611-4
CL-1841-150611-5
CL-1842-150611-1
CL-1842-150611-2
CL-1842-150611-3
CL-1842-150611-4
CL-1842-150611-5
CL-1843-150612-1
CL-1843-150612-2
CL-1843-150612-3
CL-1843-150612-4
CL-1843-150612-5
CL-1844-150616-1
CL-1844-150616-2
CL-1844-150616-3
CL-1844-150616-4
CL-1844-150616-5
CL-1844-150616-6
CL-1845-150615-1
CL-1845-150615-2
CL-1845-150615-3
CL-1845-150615-4
CL-1845-150615-5
CL-1846-150616-1
CL-1846-150616-2
CL-1846-150616-3
CL-1846-150616-4
CL-1847-150612-1
CL-1847-150612-2
CL-1847-150612-3
CL-1847-150612-4
CL-1847-150612-5
CL-1848-150612-1
CL-1848-150612-2
CL-1848-150612-3
CL-1848-150612-4
CL-1848-150612-5
CL-1850-150617-1
CL-1850-150617-2
CL-1850-150617-3
CL-1850-150617-4
CL-1850-150617-5
CL-1851-150616-1
CL-1851-150616-2
CL-1851-150616-3
CL-1851-150616-4
CL-1851-150616-5
CL-1852-150612-1
CL-1852-150612-2
CL-1852-150612-3
CL-1852-150612-4
CL-1852-150612-5
CL-1853-150616-1
CL-1853-150616-2
CL-1853-150616-3
CL-1853-150616-4
CL-1853-150616-5
CL-1854-150615-1
CL-1854-150615-2
CL-1854-150615-3
CL-1854-150615-4
CL-1854-150615-5
CL-1855-150616-1
CL-1855-150616-2
CL-1855-150616-3
CL-1855-150616-4
CL-1855-150616-5
CL-1856-150615-1
CL-1856-150615-2
CL-1856-150615-3
CL-1856-150615-4
CL-1856-150615-5
CL-1857-150616-1
CL-1857-150616-2
CL-1857-150616-3
CL-1857-150616-4
CL-1857-150616-5
CL-1858-150618-1
CL-1858-150618-2
CL-1858-150618-3
CL-1858-150618-4
CL-1858-150618-5
CL-1860-150618-1
CL-1860-150618-2
CL-1860-150618-3
CL-1860-150618-4
CL-1860-150618-5
CL-1861-150618-1
CL-1861-150618-2
CL-1861-150618-3
CL-1861-150618-4
CL-1861-150618-5
CL-1862-150618-1
CL-1862-150618-2
CL-1862-150618-3
CL-1862-150618-4
CL-1862-150618-5
CL-1863-150618-1
CL-1863-150618-2
CL-1863-150618-3
CL-1863-150618-4
CL-1863-150618-5
It is crucial to tell you that I have administrator permition to read and write data in the folder!
Without having sample of your actual variables, I would pose this question and think it MIGHT be your answer.
Your OleDb connection string should point to the PATH ONLY where the tables are located and NOT the PATH + TABLE NAME. Once connected to a PATH, you can query ANY table within that path...
ex:
string connectionStringTotal = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePathToYourData;Extended Properties=dBASE III;";
OleDbConnection connectionsTotal = new OleDbConnection(connectionStringTotal);
connectionsTotal.Open();
Then, you can
string Comand_Strings_Total = "Select * From Table1";
string Comand_Strings_Total = "Select * From Table2";
string Comand_Strings_Total = "Select * From Table3";
(or built as you have it with your for loop)
Hence if you ever needed to JOIN tables, you could such as
select T1.*, T2.*
from Table1 T1
JOIN Table2 T2
on T1.Key = T2.MatchingKey
Both tables reside in same folder would be seen.
Ok, that your path variable IS just to the path... several times in the past, others have had the connection to INCLUDE the dbf by mistake.
As for your table names, all of them having the "-" in them is probably what is choking the OleDb provider. Yes, you have [square-brackets] around the table, but maybe that might not be good enough. You may need to change it to QUOTES, and possibly even add "alias" to your query... Maybe something like..
// This version uses double-quotes around the table name
string Comand_Strings_Total = string.Format( "Select * From \"{0}\" ", FicheirosParaAnalisar[FA] );
or this which adds an ALIAS "tmp" reference for the query.
string Comand_Strings_Total = string.Format( "Select tmp.* From \"{0}\" tmp", FicheirosParaAnalisar[FA] );
If STILL error, are the files specifically dBASE, or just .dbf. You MIGHT be able to get it working by downloading Microsoft's Visual Foxpro OleDb Data Provider. The rest of the OleDb context of connection, commands, data adapter are the same. It just might provide better connection options for you. Just need to change the provider connection string. to
@"Provider=VFPOLEDB.1;Data Source=c:\\YourDataPath\\SomeSubFolder;";