looped sql statements with ASP.NET

Vedder · 25 Mar 2006 at 13:39

I have wrote a simple script were you click a button and the button calls a method which does a select statement and then in a loop inserts the results in a table ebfore closing the connection. This works using localhost but as soon as I upload it and try it there I get the following error:

Code:

There is already an open DataReader associated with this Connection which must be closed first

However with it being a compiler error I cannot see how it could work on my local machine rather than online. I also dont have any datareaders open. Here is the method (without try..catch etc):

Code:

Sub getEmail(ByVal s As Object, ByVal E As EventArgs)
       
        Dim queryString As String = "select statement"


        objCmd = New OleDbCommand
        objCmd.CommandText = queryString
        objCmd.Connection = objConn

        Dim dbParam_tutorialID As
 System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter

        dbParam_tutorialID.ParameterName = "@tutorialID"
        dbParam_tutorialID.Value = Session("sessionTutorialID")
        dbParam_tutorialID.DbType = System.Data.DbType.Int32
        objCmd.Parameters.Add(dbParam_tutorialID)
        objConn.Open()
        objRdr = objCmd.ExecuteReader()
            
        While objRdr.Read()
            objCmd = New OleDbCommand("INSERT INTO ABSENTEE(absenteeEmail) VALUES (@absenteeEmail)", objConn)
            objCmd.Parameters.Add("@studentEmail", objRdr.Item("studentEmail"))
           objCmd.ExecuteNonQuery()
        End While
        objRdr.Close()
        objConn.Close()
    End Sub

Any help appreciated, this is doing my head in and ive found nothing on the net etc..

oldbag · 25 Mar 2006 at 13:59

Isn't that while loop only going to run once? Unless you are using some kinda viewstate on the button where you can press it many times?

Vedder · 25 Mar 2006 at 14:07

I have tried and tested it locally and it works fine so I dont think its a problem with the loop

Gman · 25 Mar 2006 at 14:16

hmm you say that its ment to call a select statement when the button is clicked and then loop round and do the insert statement ?

But in the code you've given I can't see where the select statement is ?

Vedder · 25 Mar 2006 at 14:22

To make the code a bit simpler i did this

Code:

Dim queryString As String = "select statement"

Gman · 25 Mar 2006 at 14:34

Vedder said:
To make the code a bit simpler i did this

Code:

Dim queryString As String = "select statement"

yep sorry mate noticed that after I made the post

it looks ok from what I can see but its not how id usualy do it.
I would break the whole thing down so that, that particular function does one thing only i.e. retrieving the student names. You could then use the

Code:

While objRdr.Read()
            objCmd = New OleDbCommand("INSERT INTO ABSENTEE(absenteeEmail) VALUES (@absenteeEmail)", objConn)
            objCmd.Parameters.Add("@studentEmail", objRdr.Item("studentEmail"))
           objCmd.ExecuteNonQuery()
        End While

bit to store the names in an array, then call another function that accepts the array of names and inserts them into the database as required.

thats how i'd do it. I think for some reason it confusing .net that your keeping a datareader open and at the same time your trying to use the same connection object to insert data whiles still mainting the connection the reader. I supose you could create another connection object and use that for the insert part. But i'd personaly go the way of two seperate functions doing one thing and one thing well.

Vedder · 25 Mar 2006 at 18:43

Still working on it but thanks very much for your help guys. I was thinking of instead inserting the names into a database maybe make a spreadsheet or a text file or something. Thanks again for all your help