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

[wizard11]

Can MSSQL do what foxpro did in terms of record locking? if so? How?. Tnx

[Rabbit]

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.

[wizard12]

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,

FUNCTION get_referenceNo()
DO WHILE .t.
    nLAstno = 1
    SELECT tbl_setup
    SET ORDER TO tag intcode
    IF SEEK('001')
        DO WHILE .t.
            IF RLOCK()
                nLastno = tbl_setup.lastno
                REPLACE tbl_setup.lastno WITH nLastno + 1
                UNLOCK 
                EXIT 
            ENDIF 
        ENDDO 
    ENDIF 
    SELECT tbl_itinerary
    SET ORDER TO tag itineraryno
    IF !SEEK(nLAstno)
        EXIT 
    ENDIF 
ENDDO 
RETURN nLAstno
ENDFUNC 
 
 
USing MSSQL I got this
 
 
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
 
Private Sub Form_Load()
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SubReceiving;Data Source=.\SQLEXPRESS"
End Sub
Function get_referenceNo()
Do While True
    Set rs = Nothing
    rs.Open "select lastno from tbl_setup where intcode='001'", cn, adOpenStatic
    nLastno = 1
    If rs.RecordCount > 0 Then
        nLastno = rs.Fields("lastno")
        cn.Execute "update tbl_setup set lastno=" & nLastno + 1 & ""
    End If
    Set rs = Nothing
    rs.Open "select * from TBL_ITINERARY where ITINERARYNO=" & nLastno & "", cn, adOpenStatic
    If rs.RecordCount = 0 Then
        Exit Do
    End If
Loop
get_referenceNo = nLastno
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
releases.

I badly need suggestion..tnx

[Rabbit]

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.

[wizard12]

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?

[Rabbit]

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.

[wizard12]

One thing, how can We determine which rows are locked?

[Rabbit]

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.