tawfeeq jamal

tawfeeq jamal

  • NA
  • 14
  • 873

extracting data from xpdf form and posting to access

Jul 16 2014 8:57 AM
Hi, any help would really be appreciated. i am busy with a project. from a pdf document when a user clicks the submit button it comes to my mail box in .xfdf format, i need to extract all the data entered and post it into ms access database. the code below is what i have but does not seem to work
 
 
public partial class readXML : System.Web.UI.Page
{
OleDbDataAdapter DAX = null;
DataTable DTX = null; //Datatable for SQL server table
DataTable DT = null; //for catching XML data
DataSet DTS = null;//Dataset for SQL database tables
DataSet DS = null;// dataset for XML files
DataRow DR = null;
string Natemis;
bool EmisFlag = false;
string mypath = "C:/SRTest/XML/";
string[] filepaths = null;

protected void Page_Load(object sender, EventArgs e)
{

readXMLFiles();
}

//protected void btnETLXML_Click(object sender, EventArgs e)
//{

// readmyxml();

//}
private void readXMLFiles()
{
//read each XML files in folder
//and pass on to integrator
string myfilter = "*.xfdf";
ListBox1.Items.Clear();
filepaths = Directory.GetFiles(mypath, myfilter);
foreach (string str in filepaths)
{
ListBox1.Items.Add(str);
//Console.WriteLine(str + "\n");
}
lblFileCount.Text = "Number of Files: "+ListBox1.Items.Count.ToString ();
}


private void readmyxml()
{
try
{
if (ListBox1.Items.Count != 0)
{
//Initialise connection to SQL Server database
DTX = new DataTable();
DTS = new DataSet();
DAX = new OleDbDataAdapter();
DAX = myDataAdapter();//connect to database and return DataTable
DAX.Fill(DTX);
//Initialise connection to XML files
Int64 j = 0;
//StreamWriter EMISNO_File = new StreamWriter("E:/SRTest/Database/EmisNumbers0.txt");

//read all xml files----------------

for (int ix = 0; ix < ListBox1.Items.Count; ix++)
{
string xmlfile = ListBox1.Items[ix].Text;
try
{

DS = new DataSet();
DS.ReadXml(xmlfile, XmlReadMode.InferSchema);
DT = new DataTable();
DT = DS.Tables[0];//only one table in set
Natemis = null;
Natemis = findEmisNo(DT, out EmisFlag);
{
j++;
//Read each column in datatable and extract its value
for (int i = 0; i < DT.Columns.Count; i++)
{

string xx = DT.Columns[i].ToString();
String yy = DT.Rows[0][xx].ToString();
populate_SQLDatatable(xx, yy); //populate datatable linked to database

}


}
}
catch (Exception exp2)
{
j++;


Debug.Print("Error in " + xmlfile + "\n\n" + exp2.Message);
}
}//For
lblMessage.Text = "Done!";
}//if list items not zero
else
{
lblMessage.Text = "List is empty!";
}
}
catch (Exception e)
{
//catch error and display
lblMessage.Text += "Exception: " + e.ToString();
}



}
private string findEmisNo(DataTable DTEmis,out bool EmisFlag)
{
EmisFlag = false;
//read the emis number from each XML file
String myEmis = null;
for (int i = 0; i < DTEmis.Columns.Count; i++)
{
string xx = DTEmis.Columns[i].ToString();
String yy = DTEmis.Rows[0][xx].ToString();
if (xx == "TextField1")
{ myEmis = yy; }

}
if (myEmis.Length == 0)
{ EmisFlag = false;
return null;
}
else
{
EmisFlag = true;
return myEmis;

}
}//return emis number

private void populate_SQLDatatable(string columnName,string fieldValue)
{
// DR = new DataRow();
try
{
DR = DTX.NewRow();

DR["answerFk"] = columnName;
DR["natemis"] = Natemis;
DR["Answer"] = fieldValue.ToString ();
DTX.Rows.Add(DR);
DAX.Update(DTX);
}
catch (Exception exc)
{
//catch any error - parrticularly if duplicate file exist
lblMessage.Text = exc.Message; }
}//populate SQL database

private OleDbDataAdapter myDataAdapter()
{

try
{
//Provider=SQLOLEDB;Data Source=(local); Initial Catalog=SchoolReadinessPDF; User ID=ISDWEB; Password=Nikita#01
//Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SchoolReadinessPDF;Data Source=HO-PERTUNIAM9TH\MSSQLSERVERNEW
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/TawfeeqJ/Documents/Database1.accdb;Persist Security Info=False";
string sqlstring = "select answerIDX,answerFk,natemis,Answer from tbl_SchoolReadiness";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbDataAdapter DA = new OleDbDataAdapter(sqlstring, conn);
OleDbCommandBuilder CRUD = new OleDbCommandBuilder(DA);
conn.Close();
return DA;
}
catch (Exception exc)
{ //catch and display any connection issues
lblMessage.Text = exc.Message;
return null;
}

}//Create Adapter