Question Excel Oledb Connection Error

[plahanov]

Dear Friends,
please help me to resolve my problem wit Oledb connection.

For my current Project I need to fetch the data from an excel file.
Each second the data in the excel file is changing... What I want to do is, copy it from the excel and Validate it in every second.

So I use an Oledb connection to get the data from Excel file...
Its working excellent for two hours... After 2 hours I am getting an error that

"the connection for viewing your linked microsoft excel worksheet was lost......"
...

I searched a lot to get a solution but was futile.
I was stuck with this error for 3 days.. No Idea what would be the error ..
Please help me....

Plahanov

 

[jmcilhinney]

Show us the code.

 

[plahanov]

hi jmcilhinney ,

Here is the excel connection code, I used threads to fetch the data from excel.

Function 1:
Sub thrdAccess()
Dim ivar As Integer

connExclReader = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"'C:\ODIN\DIET\Arbitrage.xls';Extended Properties=""Excel 8.0;HDR=YES;""")


If connExclReader.State = ConnectionState.Closed Then

connExclReader.Open()
End If
While tFlag

update_all_excel_data()

Thread.CurrentThread.Sleep(1000)
End While

End Sub
-----------------------------
SUB 2:

public sub update_all_excel_data()

Dim sqlInsFlag As Boolean = False
Dim myCommand1 As New System.Data.OleDb.OleDbCommand
Dim sql1 As String
Dim DReader As OleDbDataReader

If True Then


Try
myCommand1.Connection = connExclReader
sql1 = "select * from [Sheet1$] where cmp = 'cmp'"
myCommand1.CommandText = sql1
DReader = myCommand1.ExecuteReader()

While DReader.Read()
strProd = Trim(DReader(1))


// code for validating the data

End While

Catch ex As Exception
MsgBox(ex.ToString)

Finally
DReader.Close() // After exiting from the main error here the point will stops showing null error..
DReader = Nothing
myCommand1.Dispose()
myCommand1 = Nothing


End Try



End If
'---------------------------------------


end sub

Thanks,
Plahanov