Multiple users writing to same database

[Roel018]

Hi All !!
Hope you can help me out...

I'm having this asp application where the visitor is able to send a message to an Access database.. This works great (with the code below).

However, when two or more users simultaneously send a message to the database, only ONE message get's saved..

Now, I know this has something to do with lock/unlocking the database.. But how do I implement this in my code ?? I have tried everything but it wont work.. I keep getting errors.

Here I provide my clean, working code (of course no simultaneously sending of messages possible yet):



============================================================
<%

Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Dim DBConn, strDB, strInsertSQL, chatname, messagetxt, messagetime, recordTotalFlash
strDB = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("data/messages.mdb")

chatname=Request("chatname")
messagetxt=Request("messagetxt")
messagetime=Request("messagetime")
recordTotalFlash=Request("recordTotalFlash")

strInsertSQL="Insert Into Messages (chatname, messagetxt, messagetime) Values ('" & chatname & "','" & messagetxt & "','" & messagetime & "')"


if chatname<>"" and messagetime<>"" then
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open strDB
DBConn.execute strInsertSQL
response.write "&messageStorageSuccess=1"



if recordTotalFlash>50 then
response.write "&SizeAbove50"
strDELETESQL = "DELETE * from Messages WHERE id < ((SELECT MAX(id) FROM Messages) - 36)"


Set DBConn = Server.CreateObject("ADODB.Connection")

DBConn.Open strDB
DBConn.execute strDELETESQL
DBConn.update
DBConn.close
Set DBConn=Nothing
end if


Else
response.write "&messageStorageSuccess=0"
End If


DBConn.close
Set DBConn=Nothing
%>

============================================================



Hope someone knows how I can make sure all messages arrive at the datasbase .... Isn't there a way to qeue the instructions ????

Kind regards,
Roel

 

[mwolf00]

Not sure if these will solve this one. There really is no reason that the simultaneous execution is failing form what I can see. The inserts should both work...

          Set DBConn = Server.CreateObject("ADODB.Connection")
          DBConn.Open strDB
          DBConn.execute strInsertSQL
          response.write "&messageStorageSuccess=1"
        
           if recordTotalFlash>50 then    
           response.write "&SizeAbove50"
           strDELETESQL = "DELETE * from Messages WHERE id < ((SELECT MAX(id) FROM Messages) - 36)"
        
           [s]Set DBConn = Server.CreateObject("ADODB.Connection")[/s]
           [s]DBConn.Open strDB[/s]  [red] 'Not necessary since you never closed it above[/red]
           DBConn.execute strDELETESQL
           [s]DBConn.update[/s]		[red]'not needed[/red]
           DBConn.close
           Set DBConn=Nothing
           end if

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tsunami Relief Inform​