already an open datareader?

[dualshock03]

how to fix this code??

Im always getting an error: " there's already an open datareader associated with this connection which must be close first!"

Private Sub btn_VCR_generate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_VCR_generate.Click
        con1.Open()
        Try

            comDate.CommandText = " SELECT * from tblpersonaldata where dateofbirth > '" & dfromvalue.Value & "' AND dateofbirth < '" & dtovalue.Value & "'"
            comDate.Connection = con1
            SearchAdap.SelectCommand = comDate
            readate = comDate.ExecuteReader
            While readate.Read
                txtsusId.Text = (readate.GetValue(readate.GetOrdinal("accussed_No")))
                txtfulname.Text = (readate.GetValue(readate.GetOrdinal("accusedname")))

            End While

            dtaSet.Clear()
            dgrid_view.AutoGenerateColumns = False
            dteAdap = New MySql.Data.MySqlClient.MySqlDataAdapter("select * from tblpersonaldata", con1)
            dteAdap.Fill(dtaSet, "tblpersonaldata")

            DataRecord.Table = dtaSet.Tables("tblpersonaldata")
            totalcnt = DataRecord.Count

            dgrid_view.DataSource = dtaSet.Tables("tblpersonaldata")
            lbltotal.Text = totalcnt

        Catch SQL_err As MySql.Data.MySqlClient.MySqlException
            MsgBox("Error searching ha database" & SQL_err.Message)
        End Try
        con1.Close()
    End Sub

 

[jmcilhinney]

Did you actually read the error message? The error message is telling you that you need to close the DataReader. I'm guessing that the way to fix that is to close the DataReader.

 

[black zero]

Try this,

Put these lines before 'try'
con1.close()
con1.open()

That will close the connection present, and open it again. This will dispose all resources using that connection, and open it to be used for your VCR Click event.

 

[jmcilhinney]

Try this,

Put these lines before 'try'
con1.close()
con1.open()

That will close the connection present, and open it again. This will dispose all resources using that connection, and open it to be used for your VCR Click event.

There's no need to close the connection, nor should you as it takes time that is simply wasted. Just do as the error message says and close the DataReader.

 

[cjard]

how to fix this code??

Read the DW2 link in my signature, section on Creating a Simple Data App. It will start you off in the right direction of doing your data access properly.

Do not write SQLs in button click event handlers; it's just not good practice

 

[black zero]

I had tried that once on my app... It didn't work. Or was it my ignorance?

Well... I managed to fix it by closing down the connection and reconnect.

 

[cjard]

I had tried that once on my app... It didn't work. Or was it my ignorance?

I've successfully followed Microsoft's tutorials before; what part did you struggle with?

 

[black zero]

Same as TC.

The only thing that was available to solve that is by closing down the connection and reopen it. Sure I had tried to close the datareader only, but it didn't work and returned the same error message.

 

[jmcilhinney]

Same as TC.

The only thing that was available to solve that is by closing down the connection and reopen it. Sure I had tried to close the datareader only, but it didn't work and returned the same error message.

If it didn't work then you did it wrong. There's absolutely no reason that an open DataReader should be reported if you've closed the only DataReader that was open. Of course, we've never seen any of the code that is purported to have attempted to close the DataReader so we can't comment on what actual issue was.

 

[black zero]

Dim dr, drT, drS As SqlDataReader

These are the only data readers on my form. When that error code appeared, I use .close event to those three and yet the problem still persisted.

 

[jmcilhinney]

This is a fine example of why you should use Using blocks. If you create a DataReader with a Using block then you're guaranteed that it will be closed at the end of the block, e.g.

Using reader As SqlDataReader = command.ExecuteReader()
    'The reader is open here.
End Using
'The reader is closed here.

As for post #10, I stand by what I said earlier. If you close a DataReader then it can't be open, so you wouldn't get that error message. Without seeing the code I can't tell you what you did wrong but I can tell you that you definitely did something wrong. The only other explanation is that your .NET Framework installation is corrupt because there's no way a closed DataReader can be open.

 

[black zero]

Using block? That's a new thing for me. I'll dig it now, thank you for telling me.

(psst, I would open new thread if I find any difficulty learning that 'Using' block. lol...)

 

[cjard]

I've never seen this error, or had this problem; using data adapters that the dataset designer creates for you solves the problem because they write well structured code that does not suffer from forgetfulness-of-the-developer..

 

[dualshock03]

Ei Pro's how about using two SQL queries..?? how to do it properly?

Example:

Private Sub btn_search_s_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_search_s.Click

       
        IF_empty()
        Try
            con1.Open()
            comSearch.CommandText = "  SELECT * from tblpersonaldata left join criminal_records ON tblpersonaldata.accussed_No = criminal_records.accussed_No WHERE tblpersonaldata.accussed_No = '" & TextBox2.Text & "' = criminal_records.accussed_No AND  tblpersonaldata.accusedname = '" & TextBox1.Text & "'"
            comSearch.Connection = con1
            SearchAdap.SelectCommand = comSearch

            readsearch = comSearch.ExecuteReader

            While readsearch.Read
                txtsusId.Text = (readsearch.GetValue(readsearch.GetOrdinal("accussed_No")))
                txtfulname.Text = (readsearch.GetValue(readsearch.GetOrdinal("accusedname")))
                txtage.Text = (readsearch.GetValue(readsearch.GetOrdinal("agespan")))
                txtlocation.Text = (readsearch.GetValue(readsearch.GetOrdinal("addressstatus")))
                txtdatecase.Text = (readsearch.GetValue(readsearch.GetOrdinal("Date_issued")))
                LIST_ORDER.Text = (readsearch.GetValue(readsearch.GetOrdinal("ORD")))
                retrieve_Image()

            End While
        Catch SQL_err As MySql.Data.MySqlClient.MySqlException
            MsgBox("Error searching ha database" & SQL_err.Message)
        End Try

        con1.Close()

    End Sub



Function retrieve_Image() As Object
        Try

            BLOBcom = New MySql.Data.MySqlClient.MySqlCommand("SELECT accussed_No, criminal_image FROM tblpersonaldata where accussed_No = '" & txtsusId.Text & "'", con1)
            readBLOB = BLOBcom.ExecuteReader(CommandBehavior.CloseConnection)
            While readBLOB.Read : Dim byteDataBLOB(readBLOB.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
                readBLOB.GetBytes(1, 0, byteDataBLOB, 0, byteDataBLOB.Length)
                Dim streamBLOB As New MemoryStream(byteDataBLOB)
                crimPhoto.Image = Image.FromStream(streamBLOB)

            End While

        Catch SQL_err As MySql.Data.MySqlClient.MySqlException
            MsgBox("Error Loading to Database" & SQL_err.Message)
        End Try

        Return True

    End Function

if i put readsearch.close in FUNCTION retrieve_Image the program tells me that "Invalid attempt to read while the reader is close" waaahh... but thats the way should it be in order for me to access the FUNCTION retrieve_Image properly.. having two datareaders keeps me bouncing my head... sorry for being noob...

 

[jmcilhinney]

So now we see what the ACTUAL problem is. You are opening a DataReader and then, for each record, trying to use the same connection to execute another query. As should be obvious now, you can NOT use the same connection to read multiple result sets simultaneously. Let's examine that statement carefully:

you can NOT use the same connection to read multiple result sets simultaneously

The answer should be fairly obvious: either don't use the same connection or don't read multiple result sets simultaneously. You need to either use a second connection to retrieve your images or else you need to wait until you've read all the other data to retrieve the images. A simple option for the second condition is to simply Load your DataReader into a DataTable first, close it and then loop through the rows of the DataTable. That will free up the connection for you to get the images.