File In Use

AlexBB

Trying to execute a delete command from .NET with OleDb driver I get an error: File in Use. It seems strange that a similar command appeared to have executed once before. That command is downstream of this one and that worked while the code around this one was not yet debugged and this ExecuteNonQuery was simply bypassed

cmdm.CommandText = "execscript('USE crossRefTable in 0 EXCLUSIVE ' + chr(13) + chr(10) + ' SELECT [crossRefTable]' + chr(13) + chr(10) + ' " +

comndText + "' + chr(13) + chr(10) + ' PACK' )";

Console.WriteLine ( cmdm.CommandText );

cmdm.ExecuteNonQuery ( );

The comndText string is built by a StringBuilder and the resulting overall CommandText (from a Console printout) looks like this:

execscript('USE crossRefTable in 0 EXCLUSIVE ' + chr(13) + chr(10) + ' SELECT [crossRefTable]' + chr(13) + chr(10) + ' DELETE FROM crossRefTable WHERE (pointer_one = [C000149] and pointer_two = [C004116]) ' + chr(13) + chr(10) + ' PACK' )

Any hope for any insight

Thanks.

AlexBB

I thought that perhaps by using USE command to close that Table and connecting to it again I would get rid of the problem. It does not seem to be the case:

execscript('USE crossRefTable in 0 EXCLUSIVE ' + chr(13) + chr(10) + ' SELECT [crossRefTable]' + chr(13) + chr(10) + ' DELETE FROM crossRefTable WHERE (pointer_one = [C000149] and pointer_two = [C004116]) ' + chr(13) + chr(10) + ' SELECT [crossRefTable]' + chr(13) + chr(10) + ' USE ' + chr(13) + chr(10) + 'USE crossRefTable in 0 EXCLUSIVE ' + chr(13) + chr(10) + ' SELECT [crossRefTable]' + chr(13) + chr(10) + 'PACK' )

Still "File In Use"

CetinBasoz

Code Block

string strScript = @"

if (USED('crossRefTable'))

USE IN 'crossRefTable'

ENDIF

use crossRefTable in 0 exclusive

SELECT 'crossRefTable'

pack";

/*

StringBuilder sb = new StringBuilder();

sb.AppendLine("if (USED('crossRefTable'))");

sb.AppendLine(" USE IN 'crossRefTable'");

sb.AppendLine("ENDIF");

sb.AppendLine("use crossRefTable in 0 exclusive");

sb.AppendLine("pack");

*/

OleDbConnection con = new OleDbConnection(strCon);

OleDbCommand cmd = con.CreateCommand();

con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "ExecScript";

cmd.Parameters.Add("myScript",OleDbType.Char).Value = strScript;

// cmd.Parameters.Add("myScript",OleDbType.Char).Value = sb.ToString();

cmd.ExecuteNonQuery();

con.Close();

The key point is that unless you close the connection, from one ExecScript to another table is open. Second call causes the error to occur. It is closed with if used() check.

PS: In your second SQL replace = operator with == (like C#'s ==). It tells regardless of ANSI setting make an exact comparison (trailing spaces are ignored).

Naomi Nosonovsky

Looks like you put your USE command in the wrong place, it should be the last command in your code, e.g.

use myTable in 0 exclusive

delete from myTable where myCondition

PACK in myTable

use in select('myTable')

The last command would close the table.

AlexBB

Naomi Nosonovsky wrote:

Looks like you put your USE command in the wrong place, it should be the last command in your code, e.g. use myTable in 0 exclusive delete from myTable where myCondition PACK in myTable use in select('myTable') The last command would close the table.

Thank you, Naomi and Cetin,

Too tired already tonight to read all this but I am sure tomorrow it will work with your help.

CetinBasoz

Yes unfortunately it would work. Is there a solid reason you're packing so often

AlexBB

CetinBasoz wrote:

Yes unfortunately it would work. Is there a solid reason you're packing so often

Well, I am dealing with a reference DB represented in my app as nodes of a treeView. I do not delete items frequently but when I do it is most probably one deletion per a run. Even if I did 15 deletions I would prefer to PACK every time because I may inadventedly click the Console and close the app this way and it is not a normal way to disconnect the DB. The records will dwell in there for no reason.

Why did you say: "UNFORTUNATELY "

CetinBasoz

Because pack should be used sparingly

If you know what you're doing then no problem.

AlexBB

This is a command I formed with StringBuilder according to the recommendations of one of the gurus.

It gave me an error at runtime:

It says: File in Use. The same error as before.

Need help.

execscript('USE [crossRefTable] in 0 EXCLUSIVE '
+ chr(13) + chr(10) +
'DELETE FROM [crossRefTable] WHERE (pointer_one = [C000149] and pointer_two = [C004116]) '
+ chr(13) + chr(10) +
'PACK IN [crossRefTable]'
+ chr(13) + chr(10) +
'USE IN (SELECT [crossRefTable])' )

Thanks.

AlexBB

I tried to remove the EXCLUSIVE clause same result. I removed the PACK clause -- same result.

What is going on Why can I INSERT but not DELETE

Just terrible. My whole work has ground to a halt.

CetinBasoz

Alex,

It has nothing to do with being exclusive or not. That error would pop up if the file is in use

Using stringbuilder is not an issue eoither (though you're not using it right - you may drop chr(13)+chr(10) using AppendLine). Did you check he script sample I provided before First close the file if it's in use.

If you don't need it exclusive you can as well do:

use 'crossRefTable' in 0 again alias 'myAlias'

....

use in 'myAlias'

For insert/delete/select/update SQL commands you don't need to 'use' or 'select' the table. VFP automatically opens it if not open already.

AlexBB

CetinBasoz wrote:

Alex, It has nothing to do with being exclusive or not. That error would pop up if the file is in use Using stringbuilder is not an issue eoither (though you're not using it right - you may drop chr(13)+chr(10) using AppendLine). Did you check he script sample I provided before First close the file if it's in use. If you don't need it exclusive you can as well do: use 'crossRefTable' in 0 again alias 'myAlias' .... use in 'myAlias' For insert/delete/select/update SQL commands you don't need to 'use' or 'select' the table. VFP automatically opens it if not open already.

Cetin, I mentioned before that I've had a few successful delete's and before I did it first time I got an error that the operation required EXCLUSIVE use of Table. I think it was pertinent to PACK, but I am not sure now.

I am the only user of this DB, there are no other applications competing for it and I can use EXCLUSIVE.

What's wrong with the code

I understand that removing exclusive was not a solution, etc. I need to have a direct answer to my question: how to make it work

Thanks.

AlexBB

CetinBasoz wrote:

Alex, It has nothing to do with being exclusive or not. That error would pop up if the file is in use Using stringbuilder is not an issue eoither (though you're not using it right - you may drop chr(13)+chr(10) using AppendLine). Did you check he script sample I provided before First close the file if it's in use. If you don't need it exclusive you can as well do: use 'crossRefTable' in 0 again alias 'myAlias' .... use in 'myAlias' For insert/delete/select/update SQL commands you don't need to 'use' or 'select' the table. VFP automatically opens it if not open already.

Cetin, I mentioned before that I've had a few successful delete's and before I did it first time I got an error that the operation required EXCLUSIVE use of Table. I think it was pertinent to PACK, but I am not sure now.

I am the only user of this DB, there are no other applications competing for it and I can use EXCLUSIVE.

What's wrong with the code

I understand that removing exclusive was not a solution, etc. I need to have a direct answer to my question: how to make it work

Thanks.

AlexBB

CetinBasoz wrote:

Alex, It has nothing to do with being exclusive or not. That error would pop up if the file is in use Using stringbuilder is not an issue eoither (though you're not using it right - you may drop chr(13)+chr(10) using AppendLine). Did you check he script sample I provided before First close the file if it's in use. If you don't need it exclusive you can as well do: use 'crossRefTable' in 0 again alias 'myAlias' .... use in 'myAlias' For insert/delete/select/update SQL commands you don't need to 'use' or 'select' the table. VFP automatically opens it if not open already.

Cetin, I mentioned before that I've had a few successful delete's and before I did it first time I got an error that the operation required EXCLUSIVE use of Table. I think it was pertinent to PACK, but I am not sure now.

I am the only user of this DB, there are no other applications competing for it and I can use EXCLUSIVE.

What's wrong with the code

I understand that removing exclusive was not a solution, etc. I need to have a direct answer to my question: how to make it work

Thanks.

P.S. I just enclosed the ExecuteNonQuery ( ) in try/catch brackets (again) and lo and behold, the record was marked for deletion but not packed. However, when I excluded PACK command from the StringBuilder it gave me the same error; file in use (runtime).

AlexBB

OK, execScript is NOT working. I get File In Use all the time. It is pointless.

I broke the operation down into two steps:

DELETE

ExecuteNonQuery

PACK

ExecuteNonQuery

The deletion proceeds flawlessly. The records are marked for deletion. However, the following PACK command gives me an error: "Variable <tableName> NOT found."

Isn't it something! Wonderful Fox is full of idiosyncrasies and surprises.

What shall I do if I cannot pack