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();
                  }
Microsoft ApplicationsC#FoxPro

Avatar of undefined
Last Comment
suhashegde

8/22/2022 - Mon
CarlWarner

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
CarlWarner

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.
ASKER
andremara

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
andremara

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.
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();
ASKER
andremara

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
suhashegde

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
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 ?


ASKER
andremara

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
andremara

...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.
suhashegde

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 .....
ASKER
andremara

:)  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 :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
andremara

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
suhashegde

are you sure recno.ToString() returns a value ?
thats the only varialbe i can see....
suhashegde

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 ......
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
andremara

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
suhashegde

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question