AboutSyed Rizwan Muhammad Rizvi Expertise I can answers questions regarding web based and desktop based programming in VB.Net. Which can include SOAP, XML, Custom Controls, COM Interoperability etc.
Experience Have been working in this specific area for last 2 years previously I was a VB 6 Developer with experties in other languages as well. Total 10 years of programming experience.
Question Sir,
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))"
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 Solution 1: You can always have more than one connection objects open so with each connection you can have an open datareader.
Solution 2: Rather than using Datareader you can also use Datasets, the Command object has an executexml... method which can be used to create a dataset and utilise then you won't have datareader issues at all.
