Hi Friends,



can u pls help fo fix this problem, thanks for spending ur valuable 

time.  I am using three stored procedure to fetch information from 

database and two stored procedure is nested with the one.  When i run 

this code it throws error "INVALID ATTEMPT TO READ THE DATA WHEN READER 

IS CLOSED".  Acutally i found the problem becoz i am closing the 

connection as i need to open another connect at the end loop cannot be 

executed.  What is the solution for this can any1 help 







Sub Page_Load(Src as object, E as EventArgs) 



	Dim MyConnection As SQLConnection

       	Dim dbRead AS SqlDataReader

	Dim MyCommand AS sqlCommand

	Dim empid, fname, l_purpose, l_type, status, id, s_box AS String

	dim l_from, l_to, a_date, confirmdt as DateTime

	dim i, l_total as integer

        i = 1	



	MyConnection = New SqlConnection("Integrated Security=SSPI; 

Persist Security Info=False;Initial Catalog=test;Data Source=testserver")

	MyCommand = New SQLCommand("all_leave_total",MyConnection)

	MyCommand.CommandType = CommandType.StoredProcedure

	MyCommand.Connection.Open()

	

	Try 

           dbRead = MyCommand.ExecuteReader()

	   While dbRead.Read()

		empid = dbRead("emp_id").toString()

		fname = dbRead("emp_fname")

		l_from = dbRead("emp_leave_from")

		l_to = dbRead("emp_leave_to")

		a_date = dbRead("emp_appl_date")

		l_purpose = dbRead("emp_leave_purpose")

		l_type = dbRead("leave_type")

		l_total = dbRead("leavetotal")

	        status = dbRead("emp_status")

	        confirmdt = dbRead("emp_dt_confirm")	

	   	id = dbRead("l_id").toString()

	        s_box = "s" & i

		MyCommand.Connection.Close()

		

 	      if status = "confirmed" then

			

			dim emp_leave, x, y, bal_leave as integer

			Dim dbRead1 AS SQLDataReader

			Dim MyCommand1 AS sqlCommand

			MyCommand1 = New SQLCommand

("leave_total",MyConnection)

			MyCommand1.CommandType = 

CommandType.StoredProcedure

			MyCommand1.Parameters.Add(New SqlParameter

("@emp_id", SqlDbType.VarChar, 50))

			MyCommand1.Parameters("@emp_id").Value = empid

			MyCommand1.Parameters.Add(New SqlParameter

("@leave_code", SqlDbType.int, 4))

			MyCommand1.Parameters("@leave_code").Value = 1

			

			MyCommand1.Connection.Open()

			dbRead1 = MyCommand1.ExecuteReader()

			While dbRead1.Read()

			emp_leave = dbRead1("leavetotal")

			End While

			dbRead1.Close()

			MyCommand1.Connection.Close()		

			

			if datepart("yyyy", confirmdt) = datepart("yyyy", 

now()) then

			x = datepart("m",confirmdt)

			y = datepart("d",confirmdt)

			if y < 15 then

			x = 12-x

			x = (x/12)*14	

			else if y >= 15 then

			x = 12-(x+1)

			x = (x/12)*14	

			end if		

			

			else 		

			x = 14

			end if

	

			bal_leave = x - emp_leave			

		

			

			dim emp_leave1,bal_leave1 as integer

			Dim dbRead2 AS SQLDataReader

			Dim MyCommand2 AS sqlCommand

			MyCommand2 = New SQLCommand

("leave_total",MyConnection)

			MyCommand2.CommandType = 

CommandType.StoredProcedure

			MyCommand2.Parameters.Add(New SqlParameter

("@emp_id", SqlDbType.VarChar, 50))

			MyCommand2.Parameters("@emp_id").Value = empid

			MyCommand2.Parameters.Add(New SqlParameter

("@leave_code", SqlDbType.int, 4))

			MyCommand2.Parameters("@leave_code").Value = 4

			MyCommand2.Connection.Open()

			dbRead2 = MyCommand2.ExecuteReader()

			While dbRead2.Read()

			emp_leave1 = dbRead2("leavetotal")

			End While

			dbRead2.Close()

			MyCommand2.Connection.Close()

			bal_leave1 =  14 - emp_leave1

			

	else if status = "probohation" then

	response.write("probahation")

	else

	response.write("not eligible")

	end if	

	

	   MyCommand.Connection.Open()

	   

	   response.write("<form runat='server'>")

	   response.write("<table width='778' border='2' cellpadding='1' 

cellspacing='1' bordercolor='#FFFFFF' dwcopytype='CopyTableRow'>")

	   response.write("<tr bgcolor='#3366FF' align='center'><td>")

	   response.write("<font color='#FFFFFF'>Name</font></td>")

 	   response.write("<td><font color='#FFFFFF'>Status</font></td>")

	   response.write("<td><font color='#FFFFFF'>From</font></td>")

	   response.write("<td><font color='#FFFFFF'>To</font></td>")

	   response.write("<td><font color='#FFFFFF'>App Dt</font></td>")

	   response.write("<td><font color='#FFFFFF'>Purpose</font></td>")

	   response.write("<td><font color='#FFFFFF'>Type</font></td>")

	   response.write("<td><font color='#FFFFFF'>Days</font></td>")

	   response.write("<td><font color='#FFFFFF'>Balance</font></td>")

	   response.write("<td><font 

color='#FFFFFF'>Approval</font></td></tr>")

		

		       	

	    response.write("<tr bgcolor='#F2F2F2'><td>")	

	    response.write(fname)

	    response.write("</td><td>")	

	    response.write(status)

	    response.write("</td><td>")	

	    response.write((Format(l_from, "d MMM yy")))

	    response.write("</td><td>")	

    	    response.write((Format(l_to, "d MMM yy")))

	    response.write("</td><td>")	

    	    response.write((Format(a_date, "d MMM yy")))	

	    response.write("</td><td>")	

	    response.write(l_purpose)

	    response.write("</td><td>")	

	    response.write(l_type)	

    	    response.write("</td><td>")	

	    response.write(l_total)

	    response.write("</td><td>")	

	    response.write(".")

	    response.write("</td><td>")		

	    response.write("<select name="& s_box &" 

OnSelectedIndexChanged='claim_SelectedIndexChanged'><option 

selected>Select</option>")

            	    response.write("<option 

value='approve'>Approve</option><option 

value='reject'>Reject</option></select>")	

	    response.write("</td></tr>")

	    id = Nothing

	    i = i + 1	

	    End While

	    response.write("</table>")	

	    response.write("</form>")

            

        Catch Exp As SQLException

            	response.write(Exp.toString())

		Response.write("ERROR: Some Error Occured")

		response.end

        End Try



	dbRead.Close()

	MyCommand.Connection.Close()

	MyCommand = Nothing	

	

End Sub 



cheers



prasanna