andremara
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.HostDatabaseC onnectionS tring();
OleDbConnection connection = new OleDbConnection(sConnectio nString);
connection.Open();
OleDbCommand myCommand = new OleDbCommand(testSQL, connection);
object recno = myCommand.ExecuteScalar();
if (recno != null)
{
myCommand.CommandText = "select rlock('"+recno.ToString()+ "','loadou t') 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();
}
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.HostDatabaseC
OleDbConnection connection = new OleDbConnection(sConnectio
connection.Open();
OleDbCommand myCommand = new OleDbCommand(testSQL, connection);
object recno = myCommand.ExecuteScalar();
if (recno != null)
{
myCommand.CommandText = "select rlock('"+recno.ToString()+
lockedstatus = myCommand.ExecuteScalar();
myCommand.CommandText = "unlock in loadout";
//myCommand.CommandText = "Execscript('unlock in loadout')";
//lockedstatus = myCommand.ExecuteNonQuery(
lockedstatus = myCommand.ExecuteScalar();
}
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
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
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
ASKER
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.
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( );
*untested
myCommand.CommandText = "Execscript('if used('tablename')+chr(13)+
'select tablename'+chr(13)+chr(10)
'goto '"+recno.tostring()+"+chr(
'unlock'+chr(13)+chr(10)+
'endif')"
lockedstatus = myCommand.ExecuteNonQuery(
ASKER
Using your suggestion, I coded thus:
myCommand.CommandText = "Execscript('if used('loadout') select loadout goto "+recno.ToString()+"+chr(1 3)+chr(10) +'unlock endif')";
lockedstatus = myCommand.ExecuteNonQuery( );
I get "function name is missing )." as the error returned. What did I do wrong?
myCommand.CommandText = "Execscript('if used('loadout') select loadout goto "+recno.ToString()+"+chr(1
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
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(
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 ?
myCommand.CommandText = "Execscript([if used('tablename')]+chr(13)
[select tablename]+chr(13)+chr(10)
[goto "+recno.tostring()+"]+chr(
[unlock]+chr(13)+chr(10)+
[endif])";
chr(13)+chr(10) are dilimeters for foxpro syntax creates new line ...\n ?
ASKER
When I use
myCommand.CommandText = "Execscript([if used('loadout')]+chr(13)+c hr(10)+[se lect loadout]+chr(13)+chr(10)+[ goto "+recno.ToString()+"]+chr( 13)+chr(10 )+[unlock] +chr(13)+c hr(10)+[en dif])";
I get Variable is Not Found.
myCommand.CommandText = "Execscript([if used('loadout')]+chr(13)+c
I get Variable is Not Found.
ASKER
...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.
myCommand.CommandText = "Execscript([select loadout unlock])";
I get "Feature is Not Available".
Can anyone help? My customer is very unhappy right now.
cmd.CommandText="execscrip t([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 .....
Checked and That works
BTW , You need to have patience .....
ASKER
:) 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="exe cscript([i f 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 :)
myCommand.CommandText="exe
lockedstatus = myCommand.ExecuteNonQuery(
I get "Variable ' ' is not found"
Are you using ExecuteNonQuery, or how are you running it?
Thanks :)
ASKER
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
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....
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 ......
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 ......
ASKER
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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