Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Record Locking

Posted on 2001-07-20
10
Medium Priority
?
408 Views
Last Modified: 2011-09-20
Please can someone explain the best way to stop duplicates in SQL Server 7.

I have a web page which via 2 recordsets finds the next id number + 1, then saves the record in the database through the second recordset doing rs.addnew.

I want to make sure there is no way even with two people pushing the buttons at the same time can create the same id.
0
Comment
Question by:andy_booth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 6301654
Use transaction something like this.

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


hongjun
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6301670
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
0
 
LVL 1

Author Comment

by:andy_booth
ID: 6301671
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Expert Comment

by:hongjun
ID: 6301678
<%
  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
0
 
LVL 33

Accepted Solution

by:
hongjun earned 200 total points
ID: 6301685
You might also need this

<%@Language=VBScript transaction=required%>

<%
Sub OnTransactionCommit()
' put anything you want here
End Sub

Sub OnTransactionAbort()
     Response.write ("<br><br><font color=red size=4>Transaction Failed! Please contact system adminstrator.</font>")
     Response.End
End Sub
%>


<%
 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

' other stuff here
%>

hongjun
0
 
LVL 1

Author Comment

by:andy_booth
ID: 6301759
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.
0
 
LVL 10

Expert Comment

by:makerp
ID: 6301819
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
0
 
LVL 1

Author Comment

by:andy_booth
ID: 6302329
Had to play around a little bit. But got it to work.

Thanks very much.
0
 
LVL 1

Expert Comment

by:OverSurge
ID: 10583263
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." ?
0
 
LVL 1

Author Comment

by:andy_booth
ID: 10596529
This was in ASP Oversurge.

MakerP's code worked for me.

Application.Lock
 'your sensitive code
Application.Unlock
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question