Solved

DATAREADER Invalid attempt to Read when reader is closed.

Posted on 2004-10-06
12
11,033 Views
Last Modified: 2011-10-03
Basically im having the error because the datareader is not finding any data to meet the query criteria. So it closes but returns nothing, how do i give back a message saying that there is no data? Code is Below


'This checks the date selected on the calendar and runs a query based on the selection
Sub Button1_Click(sender As Object, e As EventArgs)
    If month(formatdatetime(calendar1.SelectedDate,0)) = month(formatdatetime(system.DateTime.now,0)) or month(formatdatetime(calendar1.selectedDate,0)) > month(formatdatetime(system.DateTime.now,0)) then
    datagrid1.datasource = ""
    datagrid1.databind()
        Label1.text = "Please choose another date, at least a month back"
    Else
    datagrid1.datasource = Select_info(calendar1.selecteddate) ' DATA READER USED HERE
    datagrid1.databind()
        label1.text = "Select the campaign using the select button next to it to view detailed campaign information. You have selected campaign information from " & formatdatetime(Calendar1.SelectedDate,2) & " to " & formatdatetime(system.Datetime.now,2) & "."
    End if
End Sub


THIS IS THE FUNCTION used ABOVE :

    Function Select_info(ByVal datesel As Date) As System.Data.IDataReader
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=DATASOURCE
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "SQL QUERY where [Start_Dte] >= @datesel) order by start_dte desc"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_datesel As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_datesel.ParameterName = "@datesel"
        dbParam_datesel.Value = datesel
        dbParam_datesel.DbType = System.Data.DbType.DateTime
        dbCommand.Parameters.Add(dbParam_datesel)

        dbConnection.Open
        Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
       
        Return dataReader
    End Function

Any help will be appreciated.

Thx
0
Comment
Question by:ridi786
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 8

Expert Comment

by:razo
ID: 12246020
Else
    datagrid1.datasource = Select_info(calendar1.selecteddate) ' DATA READER USED HERE
    datagrid1.databind()
if datagrid1.items.count<1 then
' no data found
else
        label1.text = "Select the campaign using the select button next to it to view detailed campaign information. You have selected campaign information from " & formatdatetime(Calendar1.SelectedDate,2) & " to " & formatdatetime(system.Datetime.now,2) & "."
    End if
0
 
LVL 1

Author Comment

by:ridi786
ID: 12246128
Hi still does not work.

Gives me the same error.

System.InvalidOperationException: Invalid attempt to Read when reader is closed.
Source Error:


Line 21:         Else
Line 22:         datagrid1.datasource = Select_info(calendar1.selecteddate)
Line 23:         datagrid1.databind() 'HIGHLIGHTS HERE
Line 24:         if datagrid1.items.count<1 then
Line 25:     ' no data found
 

I stand to be corrected but I think there needs to be some sort of statement in the function here

Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
       
        Return dataReader


0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12246134
Hi,

change this
 datagrid1.datasource = Select_info(calendar1.selecteddate) ' DATA READER USED HERE
    datagrid1.databind()

to

 Dim reader as DataReader = Select_info(calendar1.selecteddate)
 if reader.HasRows Then
    datagrid1.datasource = reader
    datagrid1.databind()
 else
 'show message
 end if


Regards,
B..M
0
 
LVL 1

Author Comment

by:ridi786
ID: 12246164
Type 'DataReader' is not defined.

Error thats coming up.
0
 
LVL 8

Expert Comment

by:razo
ID: 12246166
the datareader doesnt give an error when binded if it is empty
0
 
LVL 1

Author Comment

by:ridi786
ID: 12246181
Well it is giving me the error as below

System.InvalidOperationException: Invalid attempt to Read when reader is closed.
Source Error:


Line 21:         Else
Line 22:         datagrid1.datasource = Select_info(calendar1.selecteddate)
Line 23:         datagrid1.databind() 'HIGHLIGHTS HERE
Line 24:         if datagrid1.items.count<1 then
Line 25:     ' no data found

I think its not picking up data and closing, so its not returning anything, and thats creating the error.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Accepted Solution

by:
mmarinov earned 500 total points
ID: 12246199
use this

 Dim reader as System.Data.OleDb.OleDbDataReader = Select_info(calendar1.selecteddate)
 if reader.HasRows Then
    datagrid1.datasource = reader
    datagrid1.databind()
 else
 'show message
 end if


Regards,
B..M
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12246209
also you have to use

Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader()
instead of
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

because the behaviour that you have specified close the reader


Regards,
B..M
0
 
LVL 1

Author Comment

by:ridi786
ID: 12246227
but then wont it still keep an open connection to the database until its closed?
0
 
LVL 1

Author Comment

by:ridi786
ID: 12246254
Ok its sorted, i didnt have to leave the reader open, as you said. What i did was just defined it with System.Data.OleDb.OleDbDataReader, and that accepted and worked. Thanks alot for the help :)
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12246259

if you want to close the connection, then use a dataset object
datareader object read a one-record at a time not all records
so if you close the connection, the reader will not be able to read from database
the dataset object get all data in memory and doesn't care if there is open connection
it dispose the object from the memory when you set it to null and the garbage collector goes through it
Regards,
B..M
0
 
LVL 21

Expert Comment

by:tovvenki
ID: 12246317
Hi,
I think the problem is that the connection is getting closed when the function Select_info ends sotry by placing the line
 Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
globally i.e out of the select_info function. It should work

Regards,
Venki
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now