Link to home
Start Free TrialLog in
Avatar of andremara
andremaraFlag for Afghanistan

asked on

unlock foxpro record using OLEDB

using OLEDB to connect to a VisualFoxpro driver.  I'd like to unlock a record. How to do this.
I've written a test script that locks, then unlocks a record.  Locking the record works, but unlocking doesn't:
object lockedstatus = null;
                  string testSQL = "select RECNO() from loadout where batch = '0000002'";
                  string sConnectionString = HostDatabase.HostDatabaseConnectionString();
                  OleDbConnection connection = new OleDbConnection(sConnectionString);
                  connection.Open();
                  OleDbCommand myCommand = new OleDbCommand(testSQL, connection);
                  object recno = myCommand.ExecuteScalar();
                  if (recno != null)
                  {
                        myCommand.CommandText = "select rlock('"+recno.ToString()+"','loadout') from loadout where recno() ="+recno.ToString();
                        lockedstatus = myCommand.ExecuteScalar();
                                myCommand.CommandText = "unlock in loadout";
                        //myCommand.CommandText = "Execscript('unlock in loadout')";
                        //lockedstatus = myCommand.ExecuteNonQuery();
                        lockedstatus = myCommand.ExecuteScalar();
                  }
Avatar of CarlWarner
CarlWarner
Flag of United States of America image

Your query to get the locked record reference still doesn't get it back to the locked record.

You could simply add the clause "ALL" after UNLOCK and not worry about exactly which one you locked:

Now Reads:
unlock in loadout

Try instead:
unlock all in loadout
Or make sure you have the reference pointing exactly on that locked record number referenced RECNO.  You only determined what the RECNO was with that SELECT command and left a cursor reference open rather than pointing back to that RECNO in the source table.
Avatar of andremara

ASKER

regarding your first idea, here are my results:
myCommand.CommandText = "Execscript('unlock all in loadout')";
                        lockedstatus = myCommand.ExecuteScalar();

returns: variable '' is not found



myCommand.CommandText = "Execscript('unlock all in loadout')";
                        lockedstatus = myCommand.ExecuteNonQuery();

returns: Variable ' ' is not found



myCommand.CommandText = "unlock all in loadout";
                        lockedstatus = myCommand.ExecuteScalar();

returns: One or more errors occurred during processing of command


myCommand.CommandText = "unlock all in loadout";
lockedstatus = myCommand.ExecuteNonQuery();

returns: One or more errors occurred during processing of command.


Regarding your second idea, please spell out your suggestion in code for the syntax-challenged.
thanks
One further comment is that rlock is a function in foxpro, while unlock is a command. mirroring the syntax for rlock when using unlock results in errors. Also, I started off trying to use the foxpro syntax unlock record 3 in loadout...WITHIN foxpro for test purposes, and it failed.  I then tried unlock in loadout, within foxpro, and it worked.  That's when I tried that line in .NET using OLEDB.
Avatar of suhashegde
suhashegde

the best is to disconnect and reconnect :) It will be automatically be unlocked

*untested

myCommand.CommandText = "Execscript('if used('tablename')+chr(13)+chr(10)+
                                                                 'select tablename'+chr(13)+chr(10)+
                                                                 'goto '"+recno.tostring()+"+chr(13)+chr(10)+
                                                                  'unlock'+chr(13)+chr(10)+
                                                                  'endif')"
                        lockedstatus = myCommand.ExecuteNonQuery();
Using your suggestion, I coded thus:
myCommand.CommandText = "Execscript('if used('loadout') select loadout goto "+recno.ToString()+"+chr(13)+chr(10)+'unlock endif')";
                        lockedstatus = myCommand.ExecuteNonQuery();


I get "function name is missing )." as the error returned.  What did I do wrong?
Where and how did you place a lock from .net ?
The above codes in the question dont place a lock.

To place a lock use something like
*untested (tried only once but requires more testing)
It was something like ... (will check out when i go home on my dual core. Computers here are very slow for .net)

Select * from tablename where recno() = 3 and rlock()
puts lock if not present .

Also to unlock
try

myCommand.CommandText = "Execscript([if used('loadout')] +chr(13)+chr(10)+
[select loadout]+chr(13)+chr(10)+
 [goto "+recno.ToString()+"]+chr(13)+chr(10)+[unlock endif])";

Might just work

I am still looking for otherways (more simpler) .....If time permits. (My primary profession is completely different from Computers/ programming)

suhashegde
more clearly

myCommand.CommandText = "Execscript([if used('tablename')]+chr(13)+chr(10)+
                                                                 [select tablename]+chr(13)+chr(10)+
                                                                 [goto "+recno.tostring()+"]+chr(13)+chr(10)+
                                                                  [unlock]+chr(13)+chr(10)+
                                                                  [endif])";

chr(13)+chr(10) are dilimeters for foxpro syntax creates new line ...\n ?


When I use

myCommand.CommandText = "Execscript([if used('loadout')]+chr(13)+chr(10)+[select loadout]+chr(13)+chr(10)+[goto "+recno.ToString()+"]+chr(13)+chr(10)+[unlock]+chr(13)+chr(10)+[endif])";

I get Variable is Not Found.
...and when I simplify and use

myCommand.CommandText = "Execscript([select loadout unlock])";

I get "Feature is Not Available".

Can anyone help?  My customer is very unhappy right now.
cmd.CommandText="execscript([if used('loadout')] +chr(13)+chr(10)+  [select loadout] +chr(13)+chr(10)+ [goto "+recno.ToString()+"]+ chr(13)+chr(10)+ [Unlock ] +chr(13)+chr(10)+ [Endif])";

Checked and That works

BTW , You need to have patience .....
:)  Suhasheqde, you are funny :)  Yes, I will try...and have patience. (I must admit to being worried they will drop me as a consultant.) When I run

myCommand.CommandText="execscript([if used('loadout')] +chr(13)+chr(10)+  [select loadout] +chr(13)+chr(10)+ [goto "+recno.ToString()+"]+ chr(13)+chr(10)+ [Unlock ] +chr(13)+chr(10)+ [Endif])";

lockedstatus = myCommand.ExecuteNonQuery();


I get "Variable ' ' is not found"

Are you using ExecuteNonQuery, or how are you running it?
Thanks :)
PS, thought you might be very interested in this article on .NET/VFP interop:

http://www.west-wind.com/presentations/VFPDOTNETiNTEROP/VFPDOTNETINTEROP.HTM#_Accessing_VFP_Data_with%20OleDb

I have been studying it in an attempt to find an alternative way to record unlock, and what I've found may make programming in .NET much easier by allowing me to leverage a LOT of foxpro capabilities and doing a lot of my coding in foxpro, which is easier for me, as I am used to coding in this language.
Andre
are you sure recno.ToString() returns a value ?
thats the only varialbe i can see....
BTW ,
 A quick fix would be disconnect the connection and reconnect. WOnt take much time to reconnect.

Also Locking and unlocking is kinda Outdated i feel. Take a look at transactions ......
Yes, recno returns a value.  This is taken directly from the debugger for myCommand.CommandText:
      myCommand.CommandText      "execscript([if used('loadout')] +chr(13)+chr(10)+  [select loadout] +chr(13)+chr(10)+ [goto 15]+ chr(13)+chr(10)+ [Unlock ] +chr(13)+chr(10)+ [Endif])"

I think rather than disconnecting and reconnecting, I will employ what little I just learned today about creating/calling COM functions to do the unlocking. Disconnecting/reconnecting doesn't feel like the right solution, and record locking/unlocking is as current as it ever was, as far as I know anyway, if not essential in multi-user database code development.  Thanks for your help :)
Your profile says you're a dentist - too cool that you do this AND that. All I do is this...and I still have troubles...well, programming was never my strong suite.  I'm more of the creative director who is currently working without help.
Andre
ASKER CERTIFIED SOLUTION
Avatar of suhashegde
suhashegde

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial