DataReader error-Operation is not valid due to the current state of th

[Programming2007]

Imports System.IO
Imports Oracle
Imports Oracle.DataAccess
Imports System.Threading
Imports Oracle.DataAccess.Client.OracleDataReader


Public Class LoadMissingData
Public myDS1 As New DataSet()

Public daOracle As New Oracle.DataAccess.Client.OracleDataAdapter()
Public daOracleSelect As New Oracle.DataAccess.Client.OracleDataAdapter()
Public OraDR2 As Oracle.DataAccess.Client.OracleDataReader
Public OraDR_cdr_text As Oracle.DataAccess.Client.OracleDataReader
Public cnOracle As New Oracle.DataAccess.Client.OracleConnection()

Public cmdOracle As New Oracle.DataAccess.Client.OracleCommand()
Public cmdOracleAddRecord As New Oracle.DataAccess.Client.OracleCommand()
Public cmdOracleInsert As New Oracle.DataAccess.Client.OracleCommand()
Public cmdOracleSelect As New Oracle.DataAccess.Client.OracleCommand()
Public cmdOracleChangeStatusAfterInsert As New Oracle.DataAccess.Client.OracleCommand()
Public cmdOracleChangeStatusAfterUpdate As New Oracle.DataAccess.Client.OracleCommand()

Public strSQL As String
Const strCNOracle As String = "the connection string"


Public ORIG_TIME As String
Dim ANI As String
Dim CALLED_NO As String
Dim ORIG_DT As String
Dim CDR_LINE_IMPORTED As String
Dim REC_TYpE_IMPORTED As String
Dim strLine As String = Nothing




Shared Sub Main()
Dim lmd As New LoadMissingData()

' lmd.readFiles()
lmd.readFilesTest()
End Sub



Public Sub checkRateSysTable(ByVal ORIG_TIME As String, ByVal ANI As String, ByVal CALLED_NO As String, ByVal ORIG_DT As String, ByVal cdrLine As String)


Dim myDS1 As New DataSet()
Dim myDS2 As New DataSet()
Dim tblCDR_text_data As New System.Data.DataTable()
Dim cdrINVBATCH_num As String
Dim sADDRECORD As String
Dim sInsertSQL As String
Dim boolInsert As String = False

strSQL = "select invoice_batch_number,line_number,call_date,call_time,terminate_number,originate_number from ratesys_gc_cdr_bad_TEST where status ='M' "


Try

cmdOracle = New Oracle.DataAccess.Client.OracleCommand(strSQL, cnOracle)

daOracle.SelectCommand = cmdOracle

' daOracle.Fill(myDS1, "ratesys_gc_cdr_bad")
'dgMissingFiles.Visible = True

'dgMissingFiles.DataSource = myDS1
'dgMissingFiles.DataMember = "ratesys_gc_cdr_ba
' daOracleSelect.SelectCommand = cmdOracleSelect


daOracle.Fill(myDS1, "ratesys_gc_cdr_bad_test")
Dim rowCounnt As Integer = myDS1.Tables("ratesys_gc_cdr_bad_test").Rows.Count
OraDR2 = cmdOracle.ExecuteReader

Dim hasRows As Boolean
Dim sChangeStatusAfterInsert As String
hasRows = OraDR2.HasRows()
Dim sSelectSql As String
sSelectSql = "select * from cdr_text_test"
cmdOracleSelect = New Oracle.DataAccess.Client.OracleCommand(sSelectSql, cnOracle)
OraDR_cdr_text = cmdOracleSelect.ExecuteReader


daOracle.Fill(myDS2, "cdr_text_test")
While OraDR2.Read


' If ORIG_DT = OraDR2("CALL_DATE") And ORIG_TIME = OraDR2("CALL_TIME") And CALLED_NO = OraDR2("Terminate_Number") And ANI = OraDR2("ORIGINATE_NUMBER") Then

Dim invBatchNum As String = OraDR2("INVOICE_BATCH_NUMBER")
CDR_LINE_IMPORTED = cdrLine
Try
REC_TYpE_IMPORTED = CDR_LINE_IMPORTED.Substring(0, 5)

Catch ex As Exception
REC_TYpE_IMPORTED = "RecType "

End Try

While Me.OraDR_cdr_text.Read
myDS1.Reset()
myDS2.Reset()
If OraDR_cdr_text.HasRows = True Then
If (OraDR_cdr_text("Invoice_Batch_Number") = OraDR2("INVOICE_BATCH_NUMBER") And OraDR_cdr_text("Line_NUMBER") = OraDR2("Line_number")) Then

cmdOracleInsert.CommandType = CommandType.StoredProcedure
cmdOracleInsert.CommandText = "insert_Qwest_Missing_Files"
cmdOracleInsert.ExecuteNonQuery()
cmdOracleChangeStatusAfterUpdate = New Oracle.DataAccess.Client.OracleCommand()
cmdOracleChangeStatusAfterUpdate.CommandType = CommandType.StoredProcedure
cmdOracleChangeStatusAfterUpdate.CommandText = "statusQwest"
cmdOracleChangeStatusAfterUpdate.ExecuteNonQuery()
cmdOracleInsert.ExecuteNonQuery()

cmdOracleChangeStatusAfterInsert = New Oracle.DataAccess.Client.OracleCommand(sChangeStatusAfterInsert, cnOracle)
cmdOracleChangeStatusAfterInsert.ExecuteNonQuery()
End If
End If
End While

cmdOracleAddRecord = New Oracle.DataAccess.Client.OracleCommand()
cmdOracleAddRecord.CommandType = CommandType.StoredProcedure
cmdOracleAddRecord.CommandText = "ADD_RECORD_Qwest_MISSING_FILES"
' cmdOracleAddRecord.ExecuteNonQuery()
Dim updatedNum = OraDR2.RecordsAffected
MessageBox.Show("numRowsUpated " & updatedNum)
cmdOracleChangeStatusAfterUpdate.ExecuteNonQuery()
' End If
End While
Dim numRowsAffected As Int16 = OraDR2.RecordsAffected
MessageBox.Show("Total rows affected" & numRowsAffected)


Catch ex As Exception

Dim strError As String = ex.Message
MessageBox.Show(strError)



End Try

'*STORED PROCEDURES'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'CREATE OR REPLACE PROCEDURE INSERT_QWEST_MISSING_FILES(INVBATCH IN VARCHAR ,CDR_LINE_IMPORTED IN VARCHAR, REC_TYPE_IMPORTED IN VARCHAR)
'AS BEGIN
'INSERT INTO CDR_TEXT_TEST (LINE_NUMBER,CDR_LINE,RECORD_TYPE,REPLACED)
'( SELECT LINE_NUMBER,CDR_LINE_IMPORTED,REC_TYPE_IMPORTED,SYSDATE
'FROM RATESYS_GC_CDR_BAD R
'WHERE R.INVOICE_BATCH_NUMBER =INVBATCH);
'END INSERT_QWEST_MISSING_FILES;
'/



' CREATE OR REPLACE procedure ADD_RECORD_Qwest_MISSING_FILES(cdr_line_imported in varchar, rec_type_imported in varchar)
'as begin
'insert into cdr_text_test (invoice_batch_number, line_number,cdr_line,record_type,replaced)
'(select r.invoice_batch_number, r.line_number,
' CDR_LINE_IMPORTED(, REC_TYpE_IMPORTED, SYSDATE)
' from ratesys_gc_cdr_bad_test r,dual);
'END ADD_RECORD_Qwest_MISSING_FILES;
'/

'CREATE OR REPLACE procedure statusQwest(invBatch in varchar,ORIG_DT in varchar, ORIG_TIME in varchar, CALLED_NO in varchar, ANI in varchar)
'as begin
' update(ratesys_gc_cdr_bad)
'set status = Concat('Replaced ', sysdate )
'where(invoice_batch_number = invBatch)
'and call_date = ORIG_DT
'and call_time = ORIG_TIME
'and terminate_number = CALLED_NO
'and originate_number = ANI;
'end statusQwest;
'/

'' *''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub


End Sub
Public Sub readFilesTest()
ORIG_TIME = "123"
ANI = "123"
CALLED_NO = "123"
ORIG_DT = "123"
strLine = "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
cnOracle = New Oracle.DataAccess.Client.OracleConnection(strCNOracle)
cnOracle.Open()
checkRateSysTable(ORIG_TIME, ANI, CALLED_NO, ORIG_DT, strLine)
End Sub
End Class

 

[Programming2007]

The error message is "Operation is not valid due to the current state of the object

 

[mansii]

It's not easy to see which line that produced the error.
Try changing

Dim strError As String = ex.Message

to

Dim strError As String = ex.ToString

It will show you the line number.

For now, I didn't see where you set the [blue]sChangeStatusAfterInsert[/blue] string value.

Regards.

 

[Programming2007]

Thanks, the exception says that the connection is closed. It is closed at execute nonquery but it is open before that. I don't know why it is suddenly closing.

 

[earthandfire]

I think I'm correct in saying that DataAdapter.Fill closes the connection when it has finished, therefore your connection would have been closed after the first fill.

Also, you appear to create the connection twice (once on your public declarations section and again in readfilestest).


Hope this helps.

 

[Programming2007]

Thank you for responding. The problem is not with the dataadpater closing it is with the stored procedures. The statusQwest stored procedure works but the other 2 do not. the "ADD_RECORD_Qwest_MISSING_FILES" stored procedure is what closes the connection at execute nonquery.

 

[mansii]

I see.
Seems that you forgot to set the connection to cmdOracleAddRecord. See the line:

cmdOracleAddRecord = New Oracle.DataAccess.Client.OracleCommand()

Regards.

 

[Programming2007]

Thank you.