"Gaz" <gonkowonko_at_gmail.com> wrote in message
news:1159789818.303152.61070_at_e3g2000cwe.googlegroups.com...
>
> 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 ?
>
It will stay locked until rollback or commit. Also make sure you use bind
variables in your code. Otherwise you will create a very unscalable
solution.
Jim
Received on Mon Oct 02 2006 - 07:44:51 CDT
