By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,303 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Can MSSQL do what foxpro did in terms of record locking?

P: 1
Can MSSQL do what foxpro did in terms of record locking? if so? How?. Tnx
Mar 8 '13 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 10K+
P: 12,430
Since this is the Microsoft SQL Server forum, you can't expect us to know what foxpro did in terms of record locking. You will have to tell us what it did and we can tell you if there's similar functionality.

You should be aware though that foxpro is more like Microsoft Access, combining a database engine with a front end interface. Whereas Microsoft SQL Server is just the database engine.

Therefore, if what you want to accomplish is a front end issue, that is not something that Microsoft SQL Server can accomplish.
Mar 8 '13 #2

P: 3
Sir, I am just concert with the data i currently working with hundreds of table.
What I want to do is to avoid duplication and handles those error.
in foxpro simply,

Expand|Select|Wrap|Line Numbers
  1. FUNCTION get_referenceNo()
  2. DO WHILE .t.
  3.     nLAstno = 1
  4.     SELECT tbl_setup
  5.     SET ORDER TO tag intcode
  6.     IF SEEK('001')
  7.         DO WHILE .t.
  8.             IF RLOCK()
  9.                 nLastno = tbl_setup.lastno
  10.                 REPLACE tbl_setup.lastno WITH nLastno + 1
  11.                 UNLOCK 
  12.                 EXIT 
  13.             ENDIF 
  14.         ENDDO 
  15.     ENDIF 
  16.     SELECT tbl_itinerary
  17.     SET ORDER TO tag itineraryno
  18.     IF !SEEK(nLAstno)
  19.         EXIT 
  20.     ENDIF 
  21. ENDDO 
  22. RETURN nLAstno
  23. ENDFUNC 
  26. USing MSSQL I got this
  29. Dim rs As New ADODB.Recordset
  30. Dim cn As New ADODB.Connection
  32. Private Sub Form_Load()
  33. cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SubReceiving;Data Source=.\SQLEXPRESS"
  34. End Sub
  35. Function get_referenceNo()
  36. Do While True
  37.     Set rs = Nothing
  38.     rs.Open "select lastno from tbl_setup where intcode='001'", cn, adOpenStatic
  39.     nLastno = 1
  40.     If rs.RecordCount > 0 Then
  41.         nLastno = rs.Fields("lastno")
  42.         cn.Execute "update tbl_setup set lastno=" & nLastno + 1 & ""
  43.     End If
  44.     Set rs = Nothing
  45.     rs.Open "select * from TBL_ITINERARY where ITINERARYNO=" & nLastno & "", cn, adOpenStatic
  46.     If rs.RecordCount = 0 Then
  47.         Exit Do
  48.     End If
  49. Loop
  50. get_referenceNo = nLastno
  51. End Function
But it is not Invincible, maybe there is other solution to do it in SQl
if i were maybe lock those record, I will add another loop so other workstation will block untill the one who lock the record

I badly need suggestion..tnx
Mar 9 '13 #3

Expert Mod 10K+
P: 12,430
Again, this is the Microsoft SQL Server forum. I don't know FoxPro code so I can't tell you whether or not you can duplicate what you're doing in the code. You will need to describe what you want to do in words, not code.
Mar 10 '13 #4

P: 3
Ok Sir, I heard that MSSQL has record locking capabilies.
My questions are:
1. How to lock and unlock records?
2. How to determine which rows are locked?
Mar 12 '13 #5

Expert Mod 10K+
P: 12,430
Locking is mostly done automatically but you can use the HOLDLOCK and ROWLOCK hints to force and hold a lock until your transaction is committed.

The sys.dm_tran_locks procedure will tell you about currently active requests to the lock manager for a lock that has been granted or is waiting to be granted.
Mar 12 '13 #6

P: 3
One thing, how can We determine which rows are locked?
Mar 14 '13 #7

Expert Mod 10K+
P: 12,430
You can use the READPAST hint to return those unique ids that have no locks. The complement of those ids will be the locked records. You will need to use the NOLOCK hint to return those records.
Mar 14 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.