VB6 and SQL Server record lock/unlock

Question

Mac Mac Gregor 0 Newbie Poster

14 Years Ago

Pleeeeeaaaase help. If you have the answer please email me on
<<mail removed>>

Thanks

Hi

I have a VB6 system that interacts with a SQL Server database and GET's
records with the following code

If I_Syscont.State = adStateOpen Then
        I_Syscont.Close
    End If
    szSQL = "select * from syscont"
    szSQL = Trim(szSQL) & " where recno = 1"  'section = '" & DB_wssasfSection & "'"
    If DB_LockMode = 1 Then
        I_Syscont.Open szSQL, ContractDB, adOpenDynamic, adLockPessimistic
    Else
        I_Syscont.Open szSQL, ContractDB, adOpenDynamic, adLockReadOnly
    End If

    I_Syscont.MoveFirst
    etc]
If DB_Lockmode is = 1 it locks the record LockPessimistic.

This GET routine, after changes to certain fields, is followed by a PUT routine as
follow:

If DB_LockMode = 1 Then
    DB_LockMode = 0
  End If
  I_Syscont("RecNo") = sy.recno
  I_Syscont("CoName") = sy.coname
  I_Syscont("LogSeq") = sy.logseq
  I_Syscont("ErrSeq") = sy.errseq
  I_Syscont("ProcSeq") = sy.procseq
  I_Syscont("DocSeq") = sy.docseq
  I_Syscont("DDSeq") = sy.ddseq
  I_Syscont("InpTyp") = sy.InpTyp
  I_Syscont("OutTyp") = sy.outtyp
  I_Syscont("ElemFileNo") = sy.elemfileno
  I_Syscont("PrintDocNo") = sy.printdocno
  I_Syscont("TextHistNo") = sy.Texthistno
  I_Syscont("RulesNo") = sy.rulesno
  I_Syscont("StreamNo") = sy.streamno
  I_Syscont("AutoNo") = sy.autono
  I_Syscont("STime") = sy.STime
  I_Syscont("DocPath") = sy.docpath
  I_Syscont("VolPath") = sy.volpath
  I_Syscont("ElementFilePath") = sy.ElementFilePath
  I_Syscont("BracketDocPath") = sy.BracketDocPath
  I_Syscont("FinalDocPath") = sy.FinalDocPath
  I_Syscont("DBName") = sy.DBName
  I_Syscont("VolFile") = sy.VolFile
  I_Syscont("VolExt") = sy.VolExt
  I_Syscont("Options") = sy.options
  I_Syscont("WpMacro") = sy.WPMacro
  I_Syscont("WpMacroM") = sy.wppmacrom
  I_Syscont("WpMacroL") = sy.wppmacrol
  I_Syscont("MnDTC") = sy.mndtc
  I_Syscont("static11") = sy.static1(1)
  I_Syscont("static12") = sy.static1(2)
  I_Syscont("static13") = sy.static1(3)
  I_Syscont("static14") = sy.static1(4)
  I_Syscont("static15") = sy.static1(5)
  I_Syscont("static16") = sy.static1(6)
  I_Syscont("static17") = sy.static1(7)
  I_Syscont("static18") = sy.static1(8)
  I_Syscont("static19") = sy.static1(9)
  I_Syscont("static110") = sy.static1(10)
  I_Syscont("staticl11") = sy.staticl1(1)
  I_Syscont("staticl12") = sy.staticl1(2)
  I_Syscont("staticl13") = sy.staticl1(3)
  I_Syscont("staticl14") = sy.staticl1(4)
  I_Syscont("staticl15") = sy.staticl1(5)
  I_Syscont("staticl16") = sy.staticl1(6)
  I_Syscont("staticl17") = sy.staticl1(7)
  I_Syscont("staticl18") = sy.staticl1(8)
  I_Syscont("staticl19") = sy.staticl1(9)
  I_Syscont("staticl110") = sy.staticl1(10)
  I_Syscont("static21") = sy.static2(1)
  I_Syscont("static22") = sy.static2(2)
  I_Syscont("static23") = sy.static2(3)
  I_Syscont("static24") = sy.static2(4)
  I_Syscont("static25") = sy.static2(5)
  I_Syscont("static26") = sy.static2(6)
  I_Syscont("static27") = sy.static2(7)
  I_Syscont("static28") = sy.static2(8)
  I_Syscont("static29") = sy.static2(9)
  I_Syscont("static210") = sy.static2(10)
  I_Syscont("staticl21") = sy.staticl2(1)
  I_Syscont("staticl22") = sy.staticl2(2)
  I_Syscont("staticl23") = sy.staticl2(3)
  I_Syscont("staticl24") = sy.staticl2(4)
  I_Syscont("staticl25") = sy.staticl2(5)
  I_Syscont("staticl26") = sy.staticl2(6)
  I_Syscont("staticl27") = sy.staticl2(7)
  I_Syscont("staticl28") = sy.staticl2(8)
  I_Syscont("staticl29") = sy.staticl2(9)
  I_Syscont("staticl210") = sy.staticl2(10)
  I_Syscont("Notes") = sy.Notes
  I_Syscont("AutoNo1") = sy.autono1
  I_Syscont("AutoNo2") = sy.autono2
  I_Syscont("ImageDir") = sy.ImageDir
  I_Syscont("EMailDir") = sy.EMailDir
  I_Syscont("EMailType") = sy.EMailType
  I_Syscont("FileElems1") = sy.FileElems1
  I_Syscont("FileElems2") = sy.FileElems2
  I_Syscont("DefaultFontSize") = sy.DefaultFontSize
  I_Syscont("DefaultFont") = sy.DefaultFont

  I_Syscont("Options2") = sy.Options2
  I_Syscont("SysRef") = " "
  I_Syscont("SysSeq") = 0
  I_Syscont("SysDate") = Date 'Now
  I_Syscont("SysUser") = wsuserid

  I_Syscont("FixedWaterMark01") = sy.FixedWaterMark(1)
  I_Syscont("FixedWaterMark02") = sy.FixedWaterMark(2)
  I_Syscont("FixedWaterMark03") = sy.FixedWaterMark(3)
  I_Syscont("FixedWaterMark04") = sy.FixedWaterMark(4)
  I_Syscont("FixedWaterMark05") = sy.FixedWaterMark(5)
  I_Syscont("FixedWaterMark06") = sy.FixedWaterMark(6)
  I_Syscont("FixedWaterMark07") = sy.FixedWaterMark(7)
  I_Syscont("FixedWaterMark08") = sy.FixedWaterMark(8)
  I_Syscont("FixedWaterMark09") = sy.FixedWaterMark(9)
  I_Syscont("FixedWaterMark10") = sy.FixedWaterMark(10)


  I_Syscont.update][/CODE]

I assumed this would PUT the record back and then release the lock. However
the lock is not released and the system hangs for anyone else trying to log on.

Pleeeeeaaaase help. If you have the answer please email me on
<<mail removed>>

Thanks

Mac

visual-basic

Edited 11 Years Ago by Reverend Jim because: Fixed formatting

2 Contributors
2 Replies
287 Views
22 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by Mac Mac Gregor

vb5prgrmr 143 Posting Virtuoso

14 Years Ago

Close the rs and if you open the rs as read only your following code would generate an error.

PS. Next time you are not creating a snippet but asking a question...

Good Luck

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Mac Mac Gregor 0 Newbie Poster · Answer 1 · 2009-12-01T13:35:50+00:00

Thanks vb5prgrmr

I am sorry that I did it in the wrong order but I am new to Daniweb.

As for your response my problem is that I am changing the contect of the row so need the row locked. After doing the update I assumed that this would release the record as it does in MSAccess.

Regards

Mac