Cannot move datasorce file after processing oledb

G

Guest

I'm using oledb to read an excel data and create a formate file to send to
another app.

DataSource = DataDir & xlsFile
Dim xlPNSCon1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataSource & " ;" & _
"Extended Properties=""Excel 8.0; HDR=no;
MaxScanRows=1; IMEX=1"""

Dim conn1 As New OleDbConnection(xlPNSCon1)
Dim cmd1 As New OleDbCommand _
("Select * From [" & workSheet & "] where len(trim(F41)) >
0 and IsNumeric(F2) = True " & _
" and trim(F5) <> 'DEPT' order by F2,F4 ", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
rdr.close
File.Move(oldfile,newfile) <=== get ystem.IO.IOException: The process
cannot access the file ...... because it is being used by another process.

I assume it is in use by this process but is there a way to dallocate it so
it could be moved?
 
W

W.G. Ryan - MVP

Jar - the connection may still be open which may be the problem. on yoru
datareader, you can use the CloseConnection commandbehavior which will close
the connection automatically when you close the reader - but from the looks
of things I suspect the open connection may be your problem. If not, let me
know.

I suspect there's other code though so you may already be closing the
connection b/c you aren't iterating through the reader at any point - I'm
guessing you are and just didn't post it. So if the connection is already
closed, something else is the problem - let me know though and we'll take it
from there.
jar said:
I'm using oledb to read an excel data and create a formate file to send to
another app.

DataSource = DataDir & xlsFile
Dim xlPNSCon1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataSource & " ;" & _
"Extended Properties=""Excel 8.0;
HDR=no;
MaxScanRows=1; IMEX=1"""

Dim conn1 As New OleDbConnection(xlPNSCon1)
Dim cmd1 As New OleDbCommand _
("Select * From [" & workSheet & "] where len(trim(F41))0 and IsNumeric(F2) = True " & _
" and trim(F5) <> 'DEPT' order by F2,F4 ", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
rdr.close
File.Move(oldfile,newfile) <=== get ystem.IO.IOException: The process
cannot access the file ...... because it is being used by another process.

I assume it is in use by this process but is there a way to dallocate it
so
it could be moved?
Click to expand...
 
G

Guest

W.G. Ryan - MVP said:
Jar - the connection may still be open which may be the problem. on yoru
datareader, you can use the CloseConnection commandbehavior which will close
the connection automatically when you close the reader - but from the looks
of things I suspect the open connection may be your problem. If not, let me
know.

I suspect there's other code though so you may already be closing the
connection b/c you aren't iterating through the reader at any point - I'm
guessing you are and just didn't post it. So if the connection is already
closed, something else is the problem - let me know though and we'll take it
from there.
jar said:
I'm using oledb to read an excel data and create a formate file to send to
another app.

DataSource = DataDir & xlsFile
Dim xlPNSCon1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataSource & " ;" & _
"Extended Properties=""Excel 8.0;
HDR=no;
MaxScanRows=1; IMEX=1"""

Dim conn1 As New OleDbConnection(xlPNSCon1)
Dim cmd1 As New OleDbCommand _
("Select * From [" & workSheet & "] where len(trim(F41))0 and IsNumeric(F2) = True " & _
" and trim(F5) <> 'DEPT' order by F2,F4 ", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
rdr.close
File.Move(oldfile,newfile) <=== get ystem.IO.IOException: The process
cannot access the file ...... because it is being used by another process.

I assume it is in use by this process but is there a way to dallocate it
so
it could be moved?
Click to expand...
Click to expand...
 
G

Guest

I use the cmd0.Connection.Close() and step into the code and it executes it.
A watch on the rdr shows isclosed = true. Waht else could I try ?

W.G. Ryan - MVP said:
Jar - the connection may still be open which may be the problem. on yoru
datareader, you can use the CloseConnection commandbehavior which will close
the connection automatically when you close the reader - but from the looks
of things I suspect the open connection may be your problem. If not, let me
know.

I suspect there's other code though so you may already be closing the
connection b/c you aren't iterating through the reader at any point - I'm
guessing you are and just didn't post it. So if the connection is already
closed, something else is the problem - let me know though and we'll take it
from there.
jar said:
I'm using oledb to read an excel data and create a formate file to send to
another app.

DataSource = DataDir & xlsFile
Dim xlPNSCon1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataSource & " ;" & _
"Extended Properties=""Excel 8.0;
HDR=no;
MaxScanRows=1; IMEX=1"""

Dim conn1 As New OleDbConnection(xlPNSCon1)
Dim cmd1 As New OleDbCommand _
("Select * From [" & workSheet & "] where len(trim(F41))0 and IsNumeric(F2) = True " & _
" and trim(F5) <> 'DEPT' order by F2,F4 ", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
rdr.close
File.Move(oldfile,newfile) <=== get ystem.IO.IOException: The process
cannot access the file ...... because it is being used by another process.

I assume it is in use by this process but is there a way to dallocate it
so
it could be moved?
Click to expand...
Click to expand...
 
G

Guest

Thanks Cor,

But I am trying to move/rename/delete the file that is being used in the
connetion
after the processing is competed so it doesn't get peocesed again. It
appears the
dotnet progam is not releasing it.
 
C

Cor Ligthert [MVP]

Jar,

I did not know that the reader was opening a connection automatic as I now
see in your code.

Can you try yourself to open the connection before the reading and to close
the connection in that.

I have had that behaviour as well, however forgetten what was the solution
but I thought that was it.

Because in past I was a while for ever in this case using the dispose, can
it be that one as well instead of the close.

I do not say this is for sure the solution, however I think that you can try
that yourself instead of me.

Cor
 
W

W.G. Ryan - MVP

Jar - it sounds in most part like a connection pool is established but I
could have sworn that connection pooling isnt' on with excel. Iwrote a
quick sample but i can't seem to run it b/c of the installable ISAM error.
I'm trying to find the info I'm thinking of. Connection pooling leaves a
physical connection open and from the sounds of what is happening, that's
what is going on here. I could swear though that I read that pooling isn't
available with Excel but that sure sounds like what's happening if you're
positive the connection is closed. When I get to the office I'll try to
recreate it and see if I can find something useful out. Can't promise
anything but I'll do my best.

Bill
jar said:
I use the cmd0.Connection.Close() and step into the code and it executes
it.
A watch on the rdr shows isclosed = true. Waht else could I try ?

W.G. Ryan - MVP said:
Jar - the connection may still be open which may be the problem. on yoru
datareader, you can use the CloseConnection commandbehavior which will
close
the connection automatically when you close the reader - but from the
looks
of things I suspect the open connection may be your problem. If not, let
me
know.

I suspect there's other code though so you may already be closing the
connection b/c you aren't iterating through the reader at any point - I'm
guessing you are and just didn't post it. So if the connection is
already
closed, something else is the problem - let me know though and we'll take
it
from there.
jar said:
I'm using oledb to read an excel data and create a formate file to send
to
another app.

DataSource = DataDir & xlsFile
Dim xlPNSCon1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
_
"Data Source=" & DataSource & " ;" &
_
"Extended Properties=""Excel 8.0;
HDR=no;
MaxScanRows=1; IMEX=1"""

Dim conn1 As New OleDbConnection(xlPNSCon1)
Dim cmd1 As New OleDbCommand _
("Select * From [" & workSheet & "] where
len(trim(F41))

0 and IsNumeric(F2) = True " & _
" and trim(F5) <> 'DEPT' order by F2,F4 ", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
rdr.close
File.Move(oldfile,newfile) <=== get ystem.IO.IOException: The process
cannot access the file ...... because it is being used by another
process.

I assume it is in use by this process but is there a way to dallocate
it
so
it could be moved?
Click to expand...
Click to expand...
Click to expand...
 
Top