SqlDataReader Object Not Wanting To Close

Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. "

on the lne:

Line 95:                        DR_IndJobPostings = oComm_IndPostings.ExecuteReader();

I have closed the DR_IndJobPostings object after every use of it as see n on line number 175

 

----------- Code--------------1 /// <summary>
2 /// Will generate and email job alerts based on the frequency
3 /// </summary>
4 /// <param name="frequency">WEEKLY or MONTHLY</param>
5 /// <param name="oServerIN">Instance of Server from ASPX page</param>
6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN)
7 {
8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]);
9 oConn.Open();
10
11 SqlCommand oComm;
12
13 emailSystems oEmail = new emailSystems();
14 HttpServerUtility oServer = oServerIN;
15
16 bool validCall = false;
17 bool industryHasPostings = false;
18 string sEmail = "";
19 string sEmailTemplate = "";
20 string sVacListForEmail = "";
21
22 int IJPost_VacId = 0;
23 int IJPost_EmpId = 0;
24 string IJPost_Req = "";
25 string IJPost_KeyRes = "";
26 string IJPost_VacTitle = "";
27 string IJPost_VacJobTitle = "";
28 string IJPost_VacUrl = "";
29
30 int loopCounter1 = 0;
31
32 string CandEmailAddress = "";
33
34 oComm = new SqlCommand();
35 oComm.Connection = oConn;
36 oComm.CommandType = CommandType.Text;
37
38 SqlCommand oComm_IndPostings = new SqlCommand();
39 oComm_IndPostings.Connection = oConn;
40
41 SqlDataReader DR_Industries;
42 SqlDataReader DR_IndJobPostings;
43 SqlDataReader DR_AlertList;
44
45 if (frequency == "WEEKLY" || frequency == "MONTHLY")
46 {
47 validCall = true;
48 }
49
50 if (validCall)
51 {
52 if (frequency == "WEEKLY")
53 {
54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt");
55 }
56
57 if (frequency == "MONTHLY")
58 {
59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt");
60 }
61
62 sSql = "" +
63 "SELECT [id],[industry] FROM S_Utils_Industries " +
64 "WHERE [active] = 1";
65 oComm.CommandText = sSql;
66 DR_Industries = oComm.ExecuteReader();
67
68 if (DR_Industries.HasRows)
69 {
70 //
71 // Loop through each active industry
72 //
73 while (DR_Industries.Read())
74 {
75 industryHasPostings = false;
76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0);
77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString();
78
79 // Get all active vacancy postings for this
80 // industry
81 sSql = "SELECT [id]," +
82 "[emp_id], " +
83 "[vac_Requirements]," +
84 "[vac_KeyResp]," +
85 "[vac_VacTitle]," +
86 "[vac_VacJobTitle]," +
87 "FROM [S_Vacancies] " +
88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " +
89 "[status] = 1 AND " +
90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " +
91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'";
92
93 oComm_IndPostings.CommandText = sSql;
94
95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader();
96
97 //
98 // If there are job vacancy postings for the industries
99 //
100 if (DR_IndJobPostings.HasRows)
101 {
102 industryHasPostings = true;
103 sEmail = sEmailTemplate;
104
105 //
106 // Loop through the job postings for this industry
107 //
108 while (DR_IndJobPostings.Read())
109 {
110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0);
111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1);
112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString();
113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString();
114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString();
115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString();
116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs";
117
118 sVacListForEmail += IJPost_VacTitle + @"
119
120 Job title: " + IJPost_VacJobTitle + @"
121
122 Key Responsibilities
123 " + IJPost_KeyRes + @"
124
125 Requirements
126 " + IJPost_Req + @"
127
128 " + IJPost_VacUrl + @"
129
130 ============================================================
131
132 ";
133 }
134
135
136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail);
137
138 // If there are job postings for this industry
139 // get all the people who signed up for a job alert
140 if (industryHasPostings)
141 {
142 sSql = "SELECT [S_JobAlerts].[IndustryId]," +
143 "[S_JobAlerts].[candidateEmail] " +
144 "[S_Cv_Status].[Cv_Online], " +
145 "[S_Cv_Status].[usingShortResume], " +
146 "[S_Cv_Status].[iHasHadIntro] " +
147 "FROM [S_JobAlerts] " +
148 "INNER JOIN [S_Cv_Status] ON " +
149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " +
150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " +
151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + "";
152
153 oComm.CommandText = sSql;
154
155 DR_AlertList = oComm.ExecuteReader();
156
157 // If there are candidates who signed up
158 // for a job alert
159 if (DR_AlertList.HasRows)
160 {
161 //
162 // Loop through each job alert for this industry
163 //
164 while (DR_AlertList.Read())
165 {
166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString();
167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail);
168 }
169
170 }
171 DR_AlertList.Close();
172
173 }
174 }
175 DR_IndJobPostings.Close();
176
177 }
178
179 }
180 DR_Industries.Close();
181
182
183 oConn.Close();
184 } // END: if (validCall)
185
186 }