nested loops and variable scope RRS feed

  • Question

  • 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

                    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>"); 









    Thursday, February 7, 2019 5:05 PM

All replies

  • Maybe you should reorganise the program:


    OleDbDataReader rd_1 = cmd_1.ExecuteReader();



       if ( rd_1["FileSize"].ToString() != "0")


          OleDbDataReader rd = cmd.ExecuteReader();



             . . .





    Using parameterised queries, WHERE with more conditions, and “linked tables” will be probably an improvement.

    Thursday, February 7, 2019 6:35 PM
  • Please post code using the code block button.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 7, 2019 8:57 PM
  • After the first time through the outer loop, rd will have read all the way to the end. So the next time, there will be nothing left for it to read and the inner loop will not execute at all.

    You need to get a new rd by calling ExecuteReader every time through the outer loop.

    Friday, February 8, 2019 1:01 AM