nested loops and variable scope

[CGS_GU]

I used the following code behind to read data and display from two access files, "employee" and "records" using two nested loops. (Both data tables has a column "EPF") first to read the employee number from "employee" , then corresponding records from "records" file. when I run the program it gives only the records relevant to the first employee (i.e records relevant to first EPF number.) please help me to solve the problem.


using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Web.SessionState;
using System.Text;
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace viewevent
{
/// <summary>
/// Summary description for ViewImage.
/// </summary>
public class ViewImage : System.Web.UI.Page
{


private void Page_Load(object sender, System.EventArgs e)
{

//build our query statement for both databases

string ConnectionString_1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + MapPath("employee.mdb") ;
OleDbConnection connection_1 = new OleDbConnection(ConnectionString_1);

string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + MapPath("records.mdb") ;
OleDbConnection connection = new OleDbConnection(ConnectionString);

//open the database and get a datareader for both databases
connection_1.Open();
connection.Open();

string SQL_1;
string SQL;

SQL_1 = "SELECT * from file where Category='ph' order by EPF asc";
SQL = "SELECT * from file where Category='ph' order by EPF asc";

OleDbCommand cmd_1 = new OleDbCommand(SQL_1,connection_1);
OleDbDataReader rd_1 = cmd_1.ExecuteReader();

OleDbCommand cmd = new OleDbCommand(SQL,connection);
OleDbDataReader rd = cmd.ExecuteReader();

string EPF,epf;
int k=0; // record counter


while(rd_1.Read()) // read employee
{
if ( rd_1["FileSize"].ToString() != "0") {

EPF = (rd_1["EPF"].ToString());
int g=1;

Response.Write ("Employee :"+ rd_1["EPF"] +" ");

epf = EPF;

while(rd.Read()) // read records
{

if (rd["EPF"].ToString()==epf) {
Response.Write ("(" + g + ") " + rd["Record"] + "<br>");

}

g++;
}



}
k++;

}

connection.Close();
connection_1.Close();


}



}

}

Continue reading...