Using Sqldatareader inside another sqldatareader generating error

Question

I am using a sqldatareader inside another sqldatareader declaring MultipleActiveResultSets=True. But it is giving an exception "invalid attempt to call read when reader is closed"

    con.ConnectionString = "Data Source=HELLO-PC; Initial Catalog=hrmdb; Integrated Security=True; MultipleActiveResultSets=True"
    con.Open()
    Dim cmd2 As New SqlCommand("select count(*) from empsal", con)
    Dim dr2 As SqlDataReader = cmd2.ExecuteReader()
    While dr2.Read()
        Dim count As Integer = dr2(0).ToString
    End While
    dr2.Close()
    Dim cmd3 As New SqlCommand("select emplycode from empsal", con)
    Dim dr3 As SqlDataReader = cmd3.ExecuteReader()
    While dr3.Read()
        code1 = dr3(i2).ToString
        Dim cmd1 As New SqlCommand("select total from empsal where emplycode='" + code1 + "'", con)
        Dim dr10 As SqlDataReader = cmd1.ExecuteReader()
        While dr10.Read()
            tot = dr10(0).ToString
        End While
        dr10.Close()
        pTax = professionalTax(tot)
        ESI(tot)
        PF(code1, tot)
        adDebits = addDebits()
        eDebits = extraDebits(code1)
        iTax = IncomeTax(tot)
        txfrm = taxForm(code1, tot)
        abf = absentFine(code1, tot)
        totsal1 = tot - (Convert.ToDouble(pTax.ToString) + Convert.ToDouble(adDebits.ToString) + Convert.ToDouble(eDebits.ToString) + Convert.ToDouble(iTax.ToString) + Convert.ToDouble(txfrm.ToString) + Convert.ToDouble(abf.ToString) + Convert.ToDouble(esi1.ToString) + Convert.ToDouble(esi2.ToString) + Convert.ToDouble(pf1.ToString) + Convert.ToDouble(pf2.ToString))
        com = New SqlCommand("INSERT INTO fnlreport (enumbr,pcd,pnm,dcd,dnm,totsal,pt,it,pf,cpf,esi,cesi,debits,edebits,absent,txfrm,tot,mnth1,year1) VALUES ('" + code1 + "','" + TextBox7.Text + "','" + ComboBox7.Text + "','" + TextBox8.Text + "','" + ComboBox8.Text + "','" + tot.ToString + "','" + pTax.ToString + "','" + iTax.ToString + "','" + pf1.ToString + "','" + pf2.ToString + "','" + esi1.ToString + "','" + esi2.ToString + "','" + adDebits.ToString + "','" + eDebits.ToString + "','" + abf.ToString + "','" + txfrm.ToString + "','" + totsal1.ToString + "','" + TextBox9.Text + "','" + TextBox10.Text + "')", con)
        com.ExecuteNonQuery()
        i2 = i2 + 1
    End While
    dr3.Close()
    con.Close()

First row in the table gets inserted and exception occurs at "while dr3.Read()"

A messy code messed up? I'm not surprised. Update your code, but this time use the Using keyword on all the IDisposable objects. — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Jul 10, 2014 at 18:10
stackoverflow.com/questions/2057534/using-multiple-data-readers — John Bustos
– John Bustos, Commented Jul 10, 2014 at 18:52

alybaba726 · Accepted Answer · 2014-07-10 18:59:57Z

1

I have no idea what you are trying to do in the bottom half of your code, none of your variables are declared and there are no comments. However, for the first half:

The first SQL statement is just to get the number of rows in your table, correct? Try:

Dim cmd as new SQLCommand("Select count(*) from empsal", con)
Dim count as integer = cmd.ExecuteScalar()

This will return the row count as an integer, no need to read through a one line return.

Secondly, you need the total sales for each employee code, correct? Try:

Dim cmd2 as new SQLCommand("Select emplycode, sum(*) from empsal group by emplycode", con)
Dim rdr as new SQLDataReader = cmd2.ExecuteReader()

After that, I have no clue...we need more information. Your code is very sloppy and most of it is unnecessary!

answered Jul 10, 2014 at 18:59

alybaba726

4004 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3774245 Over a year ago

I am not finding the total sales for each employee. total is the field name in the table empsal, which i am retrieving. For each employee's total I want to calculate professionaltax, debits, incometax etc which are functions. Then the values are inserted into fnlreport table. But it is working for only 1 employee, then the exception occurs

user3774245 Over a year ago

Thank You so much I got a hint from your first suggestion and now my code is working.

alybaba726 Over a year ago

Glad it helped!! If you can test your SQL statements in your server environment first, it always helps a lot. Helps you determine if there is a syntax error and helps you visualize what the output should look like!

Collectives™ on Stack Overflow

Using Sqldatareader inside another sqldatareader generating error

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related