data reader problem

[janilane]

Hi,

Please help, I'm getting error such as "There is already an open datareader associated with this command which must be closed first..." Below is my code and I am trying to loop through the datareader to match some field, how can I fix this please?
Thank you.

For Each strGetActualFiles As String In strActualFiles
                Dim strGetActFilesNoPath As New FileInfo(strGetActualFiles)
                Dim strTmpGetActFile As String = strGetActFilesNoPath.Name

                Try
                    My.Computer.FileSystem.CopyFile(strGetActualFiles, filePath & strTmpGetActFile)
                Catch cf As Exception
                    MessageBox.Show(cf.Message & vbCr & errMessage, "Copy File Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End
                End Try
            


                Try
                    dReaderUpdate = cmdReadRecordset.ExecuteReader()
                Catch ed As Exception
                    MessageBox.Show(ed.Message & vbCr & errMessage, "Stored Procedure Call Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    con.Close()
                    End
                End Try

                Do While dReaderUpdate.Read
                    strTmpStudID = dReaderUpdate.GetString(0)
                    MessageBox.Show(strTmpStudID)
                    cmdRecordUpdate.CommandText = strUpdateEditedFlag & strTmpStudID
                    Try
                        cmdRecordUpdate.ExecuteNonQuery()
                        MessageBox.Show(strTmpStudID)
                        dReaderUpdate.Close()
                    Catch errupd As Exception
                        MessageBox.Show(errupd.Message & vbCr & errMessage, "Record Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        con.Close()
                        End
                    End Try
                Loop


            Next

***this is the value of my command text:
Dim strUpdateEditedFlag As String = "UPDATE TEMPDB..SLetterMailMergeVersion " & _
"SET EditedFlag='Y' WHERE StudentID = " & strTmpStudID

 

[jmcilhinney]

You can only retrieve one result set at a time on a single connection. You can't call ExecuteNonQuery on one Command when you've already called ExecuteReader on another unless you Close the DataReader first. If you really need to execute another SQL statement while the DataReader is still open then you'll have to use another Connection.

 

[janilane]

Thanks for the response.
Do you mean create a new instance of connection like:
dim newCon as NEW sqlConnection ?

This is in a slightly different form but why is this working without the error, and this is using only one connection and 2 datareaders... ???

Dim strField1 As String
Dim strField2 As String
Dim filePath As String = "C:\Basura\TEMP\"
Dim files() As String = Directory.GetFiles(filePath)
Dim tempFileName As String

Dim procName As String = "TestProc"

'trans = myConnection.BeginTransaction
'cmd.Transaction = trans
cmd.CommandText = procName
cmd.CommandType = CommandType.StoredProcedure

Dim cmdNew As Odbc.OdbcCommand = myConnection.CreateCommand()

Dim dr As Odbc.OdbcDataReader = cmd.ExecuteReader()
While dr.Read()
strField1 = dr.GetString(0)
strField2 = dr.GetString(1)
MessageBox.Show(strField1 & "====" & strField2)

For Each filename As String In files
Dim strSelect As String = "Update employees set lastname='German Shepherd' " & _
"where employeeid = 1"
cmdNew.CommandText = strSelect
cmdNew.CommandType = CommandType.Text

Dim file As New FileInfo(filename)
tempFileName = file.Name.Remove(file.Name.Length - file.Extension.Length, file.Extension.Length)
If tempFileName = strField1 Then
MessageBox.Show("Renaming file " & tempFileName & " to " & strField2)
My.Computer.FileSystem.RenameFile(filePath & tempFileName & ".doc", strField2 & ".doc")
End If
' Test if there ia an error...

cmdNew.ExecuteNonQuery()




Next
End While

myConnection.Close()

 

[cjard]

Please use code boxes, and google for error messages before posting them THese error messages really are quite self explanatory: you cant run one database command on a connection that is already open and returning you the results from a previous database command