[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Question

Posted on 2011-04-26
1
Medium Priority
?
296 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:InfoTeam
1 Comment
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 2000 total points
ID: 35472438
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question