SQL Error--Datareader Already Open

Question

0.00/5 (No votes)

See more:

Hi, I don't know much about SQL and have been unable to see the problem in this code even after hours of searching. I would appreciate the help. The code is below.
Here's the specific error: "There is already an open DataReader associated with this Command which must be closed first."

I marked where the error is happening.

Here's part of the database class (DBUpdt):

VB

<pre>
Public Sub setField(ByVal strTable As String, ByVal strKeyName As String, ByVal strKeyContents As String, ByVal strFieldName As String, ByVal strFieldContents As String) Implements NWTTemplate.setField
        Dim strSQL As String

        strFieldContents = strFieldContents.Replace("'", "") ' Replaces "'" in FieldContents with a blank.
        ' Sets the SQL string based on the data passed.
        strSQL = "UPDATE " & strTable & " SET " & _
         strFieldName & "='" & strFieldContents & "' " & _
         "WHERE " & strKeyName & "='" & strKeyContents & "'"
        MessageBox.Show(strSQL)
        _dbCmd.CommandText = strSQL
        _dbCmd.ExecuteNonQuery()  ' GETTING ERROR HERE!
    End Sub

_dbCmd IS THE NAME OF OleDbCommand

Here is the code for the Main Form:

VB

 Private Sub btnUpdateDBNames_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdateDBNames.Click
        Dim dbRead As New DBUpdt
        
        dbRead.openConnection("weather.mdb")  ' Opens database connection (weather.mdb).
        dbRead.query("SELECT * FROM stations") ' Sets the connection to read ("stations" portion of weather.mdb).
        While dbRead.moreRecords
            getSurfaceWeather(dbRead.getField("stationID"))
        End While
        dbRead.close()
    End Sub

    Public Sub getSurfaceWeather(ByVal strID As String)
        Dim dbWrite As New DBUpdt
        dbWrite.openConnection("weather.mdb")  ' Opens database connection (weather.mdb).
        dbWrite.query("SELECT * FROM stations") ' Sets the connection to read ("stations" portion of weather.mdb).
        Dim latitude As String
        Dim strUrl As String = _
                  "http://www.weather.gov/xml/current_obs/K" & strID & ".xml"

        Dim doc = XDocument.Load(strUrl)  ' Creates an XML object and downloads the weather to it.
        With doc.Descendants
            latitude = .Elements("latitude").Value
        End With

       ' "SETFIELD" IS THE FUNCTION THAT CAUSES THE PROBLEM
        dbWrite.setField("stations", "StationID", strID, "latitude", latitude) 
        dbWrite.close()
    End Sub
End Class

Thanks in advance to anybody who can help me get this figured out.

Posted 13-May-12 15:22pm

thomas struss

Add a Solution

2 solutions

Solution 2

Thomas, what was your solution to this?

Posted 13-May-13 18:27pm

big_D

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-05-13T17:43:00

As the error says, somewhere before the code you posted you have executed a datareader using the same command and the datareader is still open.

If you place a breakpoint on the line

VB

strFieldContents = strFieldContents.Replace("'", "")

in setField you can check _dbCmd.CommandText using the debugger. This would show you what statement is executed previously so that you can find the correct place where the datareader isn't closed.

One thing is that you use the same command for different purposes and store the command most likely in the form's variables. I would suggest that you create a new command each time you need to execute a statement, because I don't think there's any benefit of storing the command especially if the commandtext doesn't stay the same between executions.