VBA Question

I'm trying to run this code to lookup email address from an LDAP server based on an employee id (gpid).  I'm getting the following error when I run this code: "There is already an open DataReader associated with this Command which must be closed first."  

It does progress when I add a second connection but then when it loops through a 3rd time it gives the same error.  Thanks for the help.
 Default.aspx.vb
InfoTeamAsked:
Who is Participating?
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
Have a look at the code below:
1. You need to use a second connection object for the update.
2. The use of the Using keyword ensure that the connection object will be closed and disposed of at the End Using statement and is good practice.
3. The use of a sqlDataReader is not appropriate for an update statement, it is usually used only for a fast forward read.
4. The use of the ExecuteNonQuery method of the command object is more appropriate for an update statement.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strSQL As String = "SELECT gpid FROM dbo.tblAssets"
        Dim email As String
        Dim updateSQL As String
        Using conn As New SqlConnection("Server=MY PC; Database=FixedAssets; Trusted_Connection=True;")
            conn.Open()
            Dim sqlCmd As New SqlCommand(strSQL, conn)
            Dim SqlReader As SqlDataReader = sqlCmd.ExecuteReader
            If SqlReader.HasRows Then
                While SqlReader.Read()
                    email = GetAllUsers(SqlReader.GetString(0).ToString())
                    updateSQL = "UPDATE tblAssets SET AssetOwner ='" & email & _
                    "' WHERE gpid ='" & SqlReader.GetString(0).ToString() & "'"
                    Using conn1 As New SqlConnection("Server=MY PC; Database=FixedAssets; Trusted_Connection=True;")
                        Dim sqlcmd1 As New SqlCommand(updateSQL, conn1)
                        sqlcmd1.ExecuteNonQuery()
                     End Using
                End While
            End If
            SqlReader.Close()
        End Using
        Response.Write("done")
    End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.