Can anyone see what is wrong with this code?

[SonJ]

Hi there,

I am trying to create an audit history for a project that I am working on. Each time I try to create an updated instance of a superseded record I get the following error at the highlighted line.

Dim objCmdTemp As SqlCommand = New SqlCommand
                objCmdTemp.Connection = objConn

                Try
                    strSQL = "UPDATE tblProjects SET IsSuperseded=1, DateSuperseded=CONVERT(DateTime, '" & Now & "', 103) WHERE ProjectID=" & Request.QueryString("ProjectID")
                    objCmd.CommandText = strSQL
                    objCmd.ExecuteNonQuery()

                    strSQL = "INSERT INTO tblProjects " & strCols & " VALUES " & strVals
                    objCmd.CommandText = strSQL
                    objCmd.ExecuteNonQuery()

                    'get details of the identity of the last inserted record and pass back to the details page
                    strSQL = "SELECT Max(ProjectID) AS MaxPID FROM tblProjects"
                    objCmd.CommandText = strSQL
                    objDR = objCmd.ExecuteReader()
                    objDR.Read()

                    intMaxPID = objDR("MaxPID")

                    objDR.Close()

                    
                    ' we need to mark the existing studies as superseded and create new instances of these studies and point them to the new project id
                    strSQL = "SELECT * FROM tblStudies WHERE ProjectID=" & Request.QueryString("ProjectID") & " AND IsSuperseded=0 ORDER BY StudyID"
                    lblMessage.Text = (&quot;strSQL: &quot; & strSQL & &quot;<br><br>&quot;)

                    objCmd.CommandText = strSQL
                    objDR = objCmd.ExecuteReader()
                    objDR.Read()


                    While objDR.Read
                        lblMessage.Text += &quot;Studies found: &quot;
                        lblMessage.Text += (&quot;objDR(StudyID): &quot; & objDR(&quot;StudyID&quot;) & &quot;<br>&quot;)

                        strSQL = &quot;INSERT INTO tblStudies &quot;
                        strSQL += &quot; (fieldnames) &quot;
                        strSQL += &quot; VALUES &quot;
                        strSQL += &quot; (&quot; & objDR(&quot;fieldvalues&quot;) & &quot;)&quot; 
                        lblMessage.Text += &quot;strSQL : &quot; & strSQL & &quot;<BR><BR>&quot;
                        objCmdTemp.CommandText = strSQL
                        objCmdTemp.ExecuteNonQuery()

                        'update the old study id as superseded

                        strSQL = &quot;UPDATE tblStudies SET IsSuperseded=1, DateSuperseded=CONVERT(DateTime, '&quot; & Now & &quot;', 103)&quot;
                        lblMessage.Text += &quot;strSQL: &quot; & strSQL & &quot;<BR><BR>&quot;
                        objCmdTemp.CommandText = strSQL
                        objCmdTemp.ExecuteNonQuery()
                    End While

                    objDR.Close()

                Catch objExc
                    lblMessage.Text = &quot;Unable to edit project - please try again.&quot;
                    lblMessage.Text += &quot;<br>objExc source: &quot; & objExc.Source
                    lblMessage.Text += &quot;<br>objExc message: &quot; & objExc.Message
                    lblMessage.Text += &quot;<br>objExc stacktrace: &quot; & objExc.StackTrace

End Try

The error message returned is: "There is already an open DataReader associated with this Connection which must be closed first" at the first objCmdTemp.ExecuteNonQuery().

Does anyone know how I can fix this, or see what I am doing wrong?

Thanks in advance,
SonD

 

[jmeckley]

You may have an open datareader from another method or class that has not been closed. Try commenting out the first [tt]objCmdTemp.ExecuteNonQuery()[/tt] and see if it errors on the next one.

hth

Jason Meckley
Database Analyst
WITF

 

[SonJ]

Hi,

All SQLDataReaders are closed after I have finished using them. If I comment out the line you suggest then it falls over at the next objCmd.ExecuteNonQuery() with the same error.

Does anyone know if what I am trying to do is possible using what I have decided to use? Alternatively, if anyone has manage to execute an update statement while looping through a datareader, please could you tell me how you did it!

Thanks in advance.
SonD

 

[MetaFunk]

You need to use multiple datareaders.

Try Tutorial on:

http://www.dotnet-webhosting.com/ado-dotnet/multiple-datareaders.aspx

Regards
G,

 

[AtomicChip]

As MetaFunk (luvin' the name=D) stated, you need multiple datareaders to do this which, in turn, requires multiple connections. You cannot have multiple open DataReaders pulling from the same connection (I'm pretty sure anyways ).

-----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[jmeckley]

I have an app that uses multiple datareader within 1 connection. As long as the readers use the same connections it's fine. You just need to close each reader before opening the next.

example:
[tt]
private sub()
Dim Cnn as New SqlConnection ([connection string])
Dim Cmd1 as New SqlCommand()
Dim Cmd2 as New SqlCommand()
Dim Cmd3 as New SqlCommand()
Dim Rdr1, Rdr2, Rdr3 as SqlDataReader

'Set each command with each command using Cnn for their connection

Cnn.Open
Rdr1 = Cmd1.ExecuteDataReader()
While Rdr1.Read
'populate labels, text boxes, and variables
End While
Rdr1.Close()
Rdr2 = Cmd2.ExecuteDataReader()
'populate drop down list
With ComboBox
.DataSource = Rdr2
.FieldText = "Field 1 of Rdr1"
.FieldValue = "Field 1 of Rdr1"
.DataBind()
End With
Rdr2.Close()
Rdr3 = Cmd3.ExecuteNonQuery()
Cnn.Close
end sub
[/tt]

Jason Meckley
Database Analyst
WITF

 

[LOLLA]

Hi SonD

Im getting the same error which you got, I can't
execute an update statement while looping through a datareader, please could you tell me if you found a solution to this problem yet?

Thanks for help

 

[SonJ]

Hi Lolla,

Yes I have solved this. After much thinking, I decided that the easiest way would be for me to load the data that needs to be looped through into a dataset. Once I had done this, I looped through the dataset and ran the update query.

Example is below - you may need to dim / public some variables that are not in the snippet below:

strSQL = "SELECT * FROM TableName WHERE FieldName=" & Request.QueryString("FieldID") & " ORDER BY FIELDID"

                    objSA = New SqlDataAdapter(strSQL, objConn)
                    objSA.Fill(objDS, "DataSetTable")

                    For Each DataRow In objDS.Tables("DataSetTable").Rows

                        'lblMessage.Text += &quot;<BR><BR>records found: &quot; & DataRow(&quot;FieldID&quot;)

                        strSQL = &quot;INSERT INTO TableName &quot;
                        strSQL += &quot; (Cols) &quot;
                        strSQL += &quot; VALUES &quot;
                        strSQL += &quot; (DataRow(&quot;FieldName&quot;))&quot;

                        'lblMessage.Text += &quot;<BR>strSQL: &quot; & strSQL

                        objCmd.CommandText = strSQL
                        objCmd.ExecuteNonQuery()

                        strSQL = &quot;UPDATE tblTableName SET FieldName=FieldValue WHERE ID=&quot; & DataRow(&quot;ID&quot;)

                        'lblMessage.Text += &quot;<br>strSQL: &quot; & strSQL

                        objCmd.CommandText = strSQL
                        objCmd.ExecuteNonQuery()

                    Next

HTH,
SonD