About Chris Expertise I can answer pretty much any question relating to VB.NET and its use in a Windows environment. I specialize in ASP.NET web development and MSSQL database access.
Experience I have over 5 years of industry experience using VB.NET and other .NET technologies for web and database development.
Education/Credentials I have some college education, but does it really matter in this field of work?
Expert: Chris Date: 9/20/2007 Subject: SqlDataReader Issue
Question Chris,
I am having an issue with some vb.net code that I hope that you can help. I need to find all dups in a recordset then take information from one of the dup records an place it into a the other record then delete the first record.
I do this by looping through a datareader to find the distinct dup records. I then loop through a second datareader and run an insert query and a delete query.
The problem is that I get the following error:
There is already an open DataReader associated with this Command which must be closed first.
But there is not a datareader associated with the command.
This is what I do:
I open the connection to the database:
Dim MSSQLConnect As New SqlConnection("Server=xxxx;Database=xxxx;Trusted_Connection=yes;")
MSSQLConnect.Open()
I then create the SQL for the main datareader and create the reader and start the loop:
Dim MainSQLcmd = New SqlCommand(SQLstr, MSSQLConnect)
Dim MainSQLdr As SqlDataReader = MainSQLcmd.ExecuteReader()
Do While MainSQLdr.Read()
Then I create the second SQL to get both records by passing in data from my main datareader. Then I create the second reader:
Dim Dupcmd = New SqlCommand(SQLstr, MSSQLConnect)
Dim Dupdr As SqlDataReader = Dupcmd.ExecuteReader()
after the above line I get the error: There is already an open DataReader associated with this Command which must be closed first.
Any assistance is greatly appreciated.
Bob
Entire code:
Sub RmvRecTwo()
Dim SQLstr As String = Nothing
Dim SELECTstr As String = Nothing
Dim FROMstr As String = Nothing
Dim WHEREstr As String = Nothing
Dim GROUPBYstr As String = Nothing
Dim UNIONstr As String = Nothing
Dim ORDERBYstr As String = Nothing
Dim MSSQLConnect As New SqlConnection("Server=UNCWDBVS1;Database=OIR;Trusted_Connection=yes;")
MSSQLConnect.Open()
SQLstr = Nothing
SELECTstr = "SELECT DISTINCT ID "
FROMstr = "FROM " + Environment.UserName + "ZGRAD" + frmGrad.txtTerm.Text + " "
WHEREstr = "WHERE ID IN(SELECT ID FROM " + Environment.UserName + "ZGRAD" + frmGrad.txtTerm.Text + " GROUP BY ID HAVING (Count(ID) > 1))"
UNIONstr = Nothing
ORDERBYstr = Nothing
Dim Dupcmd = New SqlCommand(SQLstr, MSSQLConnect)
*ERROR START AT NEXT LINE*
Dim Dupdr As SqlDataReader = Dupcmd.ExecuteReader()
Dim x = 0
Dim deg = Nothing
Dim maj = Nothing
Dim minr = Nothing
Dim conc = Nothing
Do While Dupdr.Read()
If x = 0 Then
deg = Trim(Dupdr.Item(0).ToString)
maj = Trim(Dupdr.Item(1).ToString)
minr = Trim(Dupdr.Item(2).ToString)
conc = Trim(Dupdr.Item(3).ToString)
Else
SQLstr = "UPDATE " + Environment.UserName + "ZGRAD" + frmGrad.txtTerm.Text + "SET DEGREE_2 = " + deg + ", MAJOR_2 = " + maj + ", MINOR_2 = " + minr + ", CONC_2 = " + conc + " Where ID = " + MainSQLdr.Item(0).ToString
Dim Updatecmd = New SqlCommand(SQLstr, MSSQLConnect)
Updatecmd.ExecuteNonQuery()
Updatecmd.Dispose()
Updatecmd = Nothing
SQLstr = "DELETE " + Environment.UserName + "ZGRAD" + frmGrad.txtTerm.Text + " Where ID = " + MainSQLdr.Item(0).ToString + " AND Degree_1 = Degree_2 AND Major_1 = Major_2 AND Minor_1 = Minor_2 AND Conc_1 = Conc_2"
Dim Deletecmd = New SqlCommand(SQLstr, MSSQLConnect)
Deletecmd.ExecuteNonQuery()
Deletecmd.Dispose()
Deletecmd = Nothing
End If
x = x + 1
Loop
Loop
MainSQLcmd.Dispose()
MainSQLdr.Dispose()
MainSQLcmd.close()
MainSQLdr.Close()
MainSQLcmd = Nothing
MainSQLdr = Nothing
End Sub
Answer The problem with your code is because of this:
Dim MainSQLcmd = New SqlCommand(SQLstr, MSSQLConnect)
Dim MainSQLdr As SqlDataReader = MainSQLcmd.ExecuteReader()
Do While MainSQLdr.Read()
...
Loop
You're creating an SqlDataReader, which uses an SqlCommand, which in turn uses an SqlConnection. This is done outside of the loop, and inside the loop you use the same SqlConnection with other SqlCommands, and other SqlDataReaders. Unfortunately, you need to consume that first SqlDataReader before you can open any other one using that same connection. You could create multiple connections, one for each SqlCommand/SqlDataReader you're going to work with at any given time, but this is not recommended. What you should do is create a small class to encapsulate the data you'll retrieve from each SqlDataReader, or fill a DataTable with the results, then dispose of the SqlDataReader before creating another to retrieve more results.
If you want a simple tool to automatically generate a class to access your SQL database tables, you can get this program I made called Table Assimilator from my website at http://www.beyourown.net/areas/Developers/TableAssimilator.aspx, but this will only be useful if you're retrieving all columns from a particular table, and not retrieving any fields used in your JOINs.
