Problem when deleting records in a sql server tabl

Results 1 to 2 of 2

Thread: Problem when deleting records in a sql server tabl

  1. #1
    Join Date
    Dec 1969

    Default Problem when deleting records in a sql server tabl

    Hi, I&#039;ve got a problem when deleting certain records in a sql server table and what I am experiencing is beyond my knowledge. I do appreciate it if you could help me out. Thanks!!<BR><BR>I have a calculate button and when it gets clicked it first delete sth. from the sql server table.<BR>Below is the sql statement:<BR><BR>sql = ""<BR>sql = sql & " DELETE From mytable "<BR>sql = sql & " WHERE id = " & vId <BR>ObjCon.Execute sql<BR><BR><BR>Most of the time it works fine, yet when more than 3 people are <BR>clicking the calculate button at the same time, it turns out that the above delete statement sometimes only deletes part of the records, not all. Though the different users are deleting records from the same table, the records to be deleted are not the same, the id in the delete query is different.<BR><BR>Below is the code after I added sth. to test whether the delete statement deletes all the records and also did a loop to execute the delete again if not. I know it sounds silly, but I do get different results, like once it is the 4th time to delete all the records, once it is the 6th time.<BR><BR><BR>sql = ""<BR>sql = sql & " DELETE From mytable "<BR>sql = sql & " WHERE id = " & vId <BR>ObjCon.Execute sql<BR><BR>sql = "select count(*) as cnt from mytable where id = " & vId<BR>rs.Open sql, ObjCon, adOpenStatic, adLockReadOnly , adCmdText<BR>myCnt = rs ("cnt")<BR>rs.Close <BR><BR>if myCnt &#060;&#062; 0 then<BR> Response.Write vId & ", 1- " & myCnt & ", "<BR> for i=2 to 10<BR> <BR> sql = ""<BR> sql = sql & " DELETE mytable "<BR> sql = sql & " WHERE id = " & vId<BR> ObjCon.Execute sql <BR> <BR> sql = "select count(*) as cnt from mytable where id = " & vId<BR> rs.Open sql, ObjCon, adOpenStatic, adLockReadOnly , adCmdText<BR> myCnt = rs ("cnt")<BR> rs.Close <BR><BR> Response.Write i & "- " & myCnt & ", " <BR> <BR> if myCnt = 0 then<BR> exit for<BR> end if<BR><BR> i=i+1<BR> next<BR>end if<BR><BR>Where it fails to delete all the records required, <BR>there is no time out error appearing on the screen. <BR>My feeling is that the error occurs only when <BR>there are multiple users trying to delete different records <BR>from the same table. Yet I don&#039;t understand what exactly happened.<BR><BR>The database connection is a DSN-less one, there is an<BR>index on the id field in the table, the connection timeout and<BR>the command timeout is set to a huge number (1500 for connection time out and 12000 for command time out, honestly I don&#039;t know if this timeout setting helps at all) I tried to lock the table<BR>when deleting, yet I can&#039;t really do that since there are other insert statement executed on the same table which sometimes turns out to be timeout.<BR><BR>Thanks !!!

  2. #2
    Join Date
    Dec 1969

    Default RE: Problem when deleting records in a sql server

    1. turn your timeouts back down.<BR>2. make sure connections are openbed as late as possible and closed as early as possible. looping through a delete has the connection open for an AGE.<BR>3. look up SQL&#039;s IN statement<BR><BR><BR><BR>j

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts