Locking / unlocking my database....

[Roel018]

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

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 with this locking/unlocking procedure....
It should be easy.. I mean, isn't there a way to qeue the instructions ????

Kind regards

 

[fhlee]

Hi,
Your routine looks ok. What do u mean by 'only ONE message gets saved.'? Is it possible that you have the [messageTime] field as a primary key? So, when you have 2 posts with the same time (simultenously), it generates an error? Or if you have one of the index set to index only UNIQUE records.

If you have just the [ID] field (auto Increment) as primary field, the INSERT statement should execute correctly.

regards,
- Joseph

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[Roel018]

Hi Fhlee, you're right, it works ok now. I had [ID] field (auto Increment) as primary field already, but that wasn't the problem. The problem was in the chat app itself which sent this instruction to this asp page with the code above :S ... Not so clever

Thnx anywayz for the info !

regards,
Roel