Record Locking

Use transaction something like this.

objConn.BeginTrans
objCon.Execute("insert into your_table values (1)")
objConn.CommitTrans


hongjun

This is a tricky problem. It is almost impossible to guarantee no duplicates. The easiest way is to use a serial type field when adding new records. SQL server allots the number automatically. The problem is if you want to recuperate the alloted number immediately after the update. Another easy method (but again not foolproof) is to do a "SELECT MAX(idfield) FROM table" just before the insert. Add 1 to this value and do the INSERT. The safest way (in my opinion) is to use MTS shared property groups for this. Using these, with multi users accessing the same property value, the first to arrive takes the lead, the others are queued until the first releases the property, and so on and so forth, thus avoiding any DB concurrency locks on the table. This method, however, implies an ActiveX component. I don't know how complicated you want to go.

Hope this helps

Where do I fit that into my code. Posted below...

sql = "SELECT MAX(quote_reference) FROM quote"
   
   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open session("ses_connection")
   set cmd = Server.CreateObject("ADODB.Command")
   cmd.CommandText = sql
   set cmd.ActiveConnection = conn
   set rs = Server.CreateObject("ADODB.Recordset")
   set rs = cmd.Execute
   
   session("ses_quote_reference") = rs.Fields(0).Value
   
   If isnull(session("ses_quote_reference")) Then
    session("ses_quote_reference") = 1
   Else
    session("ses_quote_reference") = session("ses_quote_reference") + 1
   End If
   
   sql_insert = "SELECT * FROM quote WHERE 1=0"
   
   set conn_insert = Server.CreateObject("ADODB.Connection")
   conn_insert.Open session("ses_connection")
   set cmd_insert = Server.CreateObject("ADODB.Command")
   cmd_insert.CommandText = sql_insert
   set cmd_insert.ActiveConnection = conn_insert
   set rs_insert = Server.CreateObject("ADODB.Recordset")
   rs_insert.Open sql_insert, conn_insert, 2, 3
   
   rs_insert.AddNew    
   rs_insert.Fields("quote_reference").Value = session("ses_quote_reference")
   rs_insert.Fields("quote_type").Value = "Truck"
   rs_insert.Fields("status").Value = "Incomplete"
   rs_insert.Fields("date_added").Value = date()
   rs_insert.fields("broker").value = session("ses_username")
   rs_insert.update

<%
  sql = "SELECT MAX(quote_reference) FROM quote"
 
  set conn = Server.CreateObject("ADODB.Connection")
  conn.Open session("ses_connection")
  set cmd = Server.CreateObject("ADODB.Command")
  cmd.CommandText = sql
  set cmd.ActiveConnection = conn
  set rs = Server.CreateObject("ADODB.Recordset")
  set rs = cmd.Execute
 
  session("ses_quote_reference") = rs.Fields(0).Value
 
  If isnull(session("ses_quote_reference")) Then
   session("ses_quote_reference") = 1
  Else
   session("ses_quote_reference") = session("ses_quote_reference") + 1
  End If
 
  sql_insert = "SELECT * FROM quote WHERE 1=0"
 
  set conn_insert = Server.CreateObject("ADODB.Connection")
  conn_insert.Open session("ses_connection")
  set cmd_insert = Server.CreateObject("ADODB.Command")
  cmd_insert.CommandText = sql_insert
  set cmd_insert.ActiveConnection = conn_insert
  set rs_insert = Server.CreateObject("ADODB.Recordset")

  conn.begintrans

  rs_insert.Open sql_insert, conn_insert, 2, 3
 
  rs_insert.AddNew    
  rs_insert.Fields("quote_reference").Value = session("ses_quote_reference")
  rs_insert.Fields("quote_type").Value = "Truck"
  rs_insert.Fields("status").Value = "Incomplete"
  rs_insert.Fields("date_added").Value = date()
  rs_insert.fields("broker").value = session("ses_username")
  rs_insert.update

  conn.committrans
%>


hongjun

Hongjun,

I have added your code, (thanks for the help so far!)

However it runs your Sub OnTransactionAbort() everytime I try to add a record.

Here is the error!

Microsoft OLE DB Provider for SQL Server error '8004d013'

Cannot start more transactions on this session.

/croweonline_dev/home_quote.asp, line 174

Transaction Failed! Please contact system adminstrator.

wrap an

Application.Lock
 'your sensitive code
Application.Unlock

around the code in question, this will serialize access to to it, it is a heavy handed approach as it temporarily locks other users scripts. although it dont reallt matter if the script dont take long to execute

Had to play around a little bit. But got it to work.

Thanks very much.

Andy.. I am getting this error with a SP that I am currently calling on a project I am working on. What did you end up changing to fix the "Cannot start more transactions on this session." ?

This was in ASP Oversurge.

MakerP's code worked for me.

Application.Lock
 'your sensitive code
Application.Unlock