Re: How to lock a record in a table using a query, VB6 and Oracle as a database

Charles Hooper wrote:
> Gaz wrote:
> > Hi,
> >
> > Spent the last week searching for this solution here is my problem....
> >
> > I basically want to to be able to search for a record and then view it
> > but if i view it i want to lock it so no one else can edit it. This is
> > becuase i have got a browse and edit mode in my software. The problem
> > is it doesnt want to lock and i can update the record from the
> > database.
> >
> > Im using VB6 and adodb as a connection string ive tried opening the
> > record for update but cant seem to get it too work and its doing my
> > head in.
> >
> > All i want is to be able to parse a SQL string through and then lock
> > the tables/records it relates to is this possible??
> >
> > Here is my code.....
> >
> > set rst = new adodb.recordset
> > rst.Source = "Select t1.col2, t2.col1 " & _
> > "From tableone t1,tabletwo t2 " & _
> > "Where (t1.id=t2.id) and (t1.id=" & strID
> > & ")"
> > .Open , gcnnLive, adOpenKeyset, adLockOptimistic
> >
> > the query itself works and it populates my txtboxes but no data is
> > bound, ive tried using the ado control for VB6 and that shows the data
> > but doesnt lock it either im desperate can anyone help me or offer
> > advise??
> >
> > Cheers
> >
> > gaz
>
> Experiment with this code to see what it does:
> Dim strSQL As String
> Dim snpData As New ADODB.Recordset
>
> gcnnLive.BeginTrans
>
> strSQL = "SELECT" & vbCrLf
> strSQL = strSQL & " T1.COL2," & vbCrLf
> strSQL = strSQL & " T2.COL1" & vbCrLf
> strSQL = strSQL & "FROM" & vbCrLf
> strSQL = strSQL & " TABLEONE T1," & vbCrLf
> strSQL = strSQL & " TABLETWO T2" & vbCrLf
> strSQL = strSQL & "WHERE" & vbCrLf
> strSQL = strSQL & " T1.ID=T2.ID" & vbCrLf
> strSQL = strSQL & " AND T1.ID" = strID & vbCrLf
> strSQL = strSQL & "FOR UPDATE"
>
> snpData.Open strSQL, gcnnLive, adOpenKeyset, adLockOptimistic
> Stop
> snpData.Close
> gcnnLive.CommitTrans
>
> Note the Stop statement in the above. When the program reaches that
> point, execute the following using SQLPlus:
> SELECT
> *
> FROM
> V$LOCKED_OBJECT;
>
> You should see two entries in this view that were created by the SELECT
> statement in your VB program. Using SQLPlus, try to view the record
> that was selected in the VB program - you should have no problem
> viewing the record. Now, try to update the record - SQLPlus should
> hang until the CommitTrans is processed in the VB program.
>
> Keep track of the number of BeginTrans and corresponding
> CommitTrans/RollbackTrans that the program encounters - every
> BeginTrans must eventually have either a CommitTrans or RollbackTrans
> or unexpected things will happen in the program. Also, your program
> should specify CommandTimeout on the database connection (gcnnLive) so
> that locking errors can be returned by the database to your program in
> a timely manner, if another session already locked the rows in the
> table.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Mate i LOVE U!!!!

It works it locks the record, all i have to do now is figure out how to leave it locked til i finished with it i suppose its ok to commit the recordset once i close the form im assuming ? Received on Mon Oct 02 2006 - 06:50:18 CDT