ridi786
asked on
DATAREADER Invalid attempt to Read when reader is closed.
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(calen dar1.Selec tedDate,0) ) = month(formatdatetime(syste m.DateTime .now,0)) or month(formatdatetime(calen dar1.selec tedDate,0) ) > month(formatdatetime(syste m.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.sele cteddate) ' 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.S electedDat e,2) & " to " & formatdatetime(system.Date time.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.OL EDB.4.0; Ole DB Services=-4; Data Source=DATASOURCE
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbCon nection(co nnectionSt ring)
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.OleDbCom mand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_datesel As System.Data.IDataParameter = New System.Data.OleDb.OleDbPar ameter
dbParam_datesel.ParameterN ame = "@datesel"
dbParam_datesel.Value = datesel
dbParam_datesel.DbType = System.Data.DbType.DateTim e
dbCommand.Parameters.Add(d bParam_dat esel)
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(Sy stem.Data. CommandBeh avior.Clos eConnectio n)
Return dataReader
End Function
Any help will be appreciated.
Thx
'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(calen
datagrid1.datasource = ""
datagrid1.databind()
Label1.text = "Please choose another date, at least a month back"
Else
datagrid1.datasource = Select_info(calendar1.sele
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.S
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.OL
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbCon
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.OleDbCom
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_datesel As System.Data.IDataParameter
dbParam_datesel.ParameterN
dbParam_datesel.Value = datesel
dbParam_datesel.DbType = System.Data.DbType.DateTim
dbCommand.Parameters.Add(d
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(Sy
Return dataReader
End Function
Any help will be appreciated.
Thx
ASKER
Hi still does not work.
Gives me the same error.
System.InvalidOperationExc eption: Invalid attempt to Read when reader is closed.
Source Error:
Line 21: Else
Line 22: datagrid1.datasource = Select_info(calendar1.sele cteddate)
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(Sy stem.Data. CommandBeh avior.Clos eConnectio n)
Return dataReader
Gives me the same error.
System.InvalidOperationExc
Source Error:
Line 21: Else
Line 22: datagrid1.datasource = Select_info(calendar1.sele
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(Sy
Return dataReader
Hi,
change this
datagrid1.datasource = Select_info(calendar1.sele cteddate) ' DATA READER USED HERE
datagrid1.databind()
to
Dim reader as DataReader = Select_info(calendar1.sele cteddate)
if reader.HasRows Then
datagrid1.datasource = reader
datagrid1.databind()
else
'show message
end if
Regards,
B..M
change this
datagrid1.datasource = Select_info(calendar1.sele
datagrid1.databind()
to
Dim reader as DataReader = Select_info(calendar1.sele
if reader.HasRows Then
datagrid1.datasource = reader
datagrid1.databind()
else
'show message
end if
Regards,
B..M
ASKER
Type 'DataReader' is not defined.
Error thats coming up.
Error thats coming up.
the datareader doesnt give an error when binded if it is empty
ASKER
Well it is giving me the error as below
System.InvalidOperationExc eption: Invalid attempt to Read when reader is closed.
Source Error:
Line 21: Else
Line 22: datagrid1.datasource = Select_info(calendar1.sele cteddate)
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.
System.InvalidOperationExc
Source Error:
Line 21: Else
Line 22: datagrid1.datasource = Select_info(calendar1.sele
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also you have to use
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader()
instead of
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(Sy stem.Data. CommandBeh avior.Clos eConnectio n)
because the behaviour that you have specified close the reader
Regards,
B..M
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader()
instead of
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(Sy
because the behaviour that you have specified close the reader
Regards,
B..M
ASKER
but then wont it still keep an open connection to the database until its closed?
ASKER
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.OleDbDat aReader, and that accepted and worked. Thanks alot for the help :)
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
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.OleDbCon nection(co nnectionSt ring)
globally i.e out of the select_info function. It should work
Regards,
Venki
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.OleDbCon
globally i.e out of the select_info function. It should work
Regards,
Venki
datagrid1.datasource = Select_info(calendar1.sele
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.S
End if