0

I have this code to generate auto number by select max and I use lock table. My question is why unlock tables can't run after lock table ?

Sub locking()
        Dim nomortrans As Integer
        SQL = "lock tables tblpos_trans_nomor write, tblpos_trans_nomor as tn2 read"
        Proses.ExecuteNonQuery(SQL)
        SQL = "insert into tblpos_trans_nomor (s_nomor_trans) select MAX(s_nomor_trans)+1 from tblpos_trans_nomor as tn2"
        Proses.ExecuteNonQuery(SQL)
        TblPayment = Proses.ExecuteQuery("select MAX(s_nomor_trans) from tblpos_trans_nomor as tn2")
        nomortrans = TblPayment.Rows(0).Item(0)
        frm_pos.LblNomorTransaksi.Text = nomortrans
        frm_pos3.LblNomorTransaksi.Text = nomortrans
        SQL = "unlock tables"
        Proses.ExecuteNonQuery(SQL)
End Sub

If I edit like this when it is running no problem but I can't parse value select max,

Sub locking()
        Dim nomortrans As Integer
        SQL = "lock tables tblpos_trans_nomor write, tblpos_trans_nomor as tn2 read"
        Proses.ExecuteNonQuery(SQL)
        SQL = "insert into tblpos_trans_nomor (s_nomor_trans) select MAX(s_nomor_trans)+1 from tblpos_trans_nomor as tn2"
        Proses.ExecuteNonQuery(SQL)
        SQL = "unlock tables"
        Proses.ExecuteNonQuery(SQL)
End Sub
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Tommy Pramuja
  • 1
  • 1
  • Is this [tag:mysql]? If so, please add that tag to your question. If not, please add the *appropriate* tag for your database system – Damien_The_Unbeliever Nov 29 '16 at 09:33
  • Normally, you would **NOT** lock and unlock tables through separate invocations. Instead, you would call a stored procedure/function (depending on your DBMS) that locks, inserts/updates/deletes and unlocks (and this to warrant that everything takes place within the same session). – FDavidov Nov 29 '16 at 10:05
  • Damien : Iam using InnoDB – Tommy Pramuja Nov 29 '16 at 10:15
  • FDavid : can you give sample, i do not understand about Store Procedure, thanks David – Tommy Pramuja Nov 29 '16 at 10:19

0 Answers0