Solved

problem to access database

Posted on 2002-05-22
42
336 Views
Last Modified: 2008-03-10
I use IIS4, ASP to access database.
If I query page sequencially, it's OK.
If I make 10 queries at the same time,
only one is OK and another 9 are fail.
After that fail, I must wait a period time.
Otherwise, it will fail.
But even I wait, only the first query is OK.
And then must wait another period time.
What's the problem?
Thanks

My ASP file like this:
<%
sn = request("sn")
pr = request("pr")

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB.recordset")
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3

if RS.EOF then
  Response.Write("###Account not find!!")
  rs.Close
  Conn.Close
  set RS = Nothing
  set Conn = Nothing
else
  amt = pr + RS("amount")
  Response.Write("***" & amt)

  rs("amount") = amt
  rs.update
  rs.Close
  Conn.Close
  set RS = Nothing
  set Conn = Nothing

end if
%>
0
Comment
Question by:ginaa
  • 13
  • 10
  • 9
  • +5
42 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 7026607
try
rs.open SQL, Conn, 2
0
 

Author Comment

by:ginaa
ID: 7026651
After try, the result has NO different.
It seems causes by openning same mdb file at the same time.
Should I make some setting in IIS?
Or I need to take care file share violation in ASP?
Thanks.
0
 
LVL 19

Expert Comment

by:webwoman
ID: 7026874
Make sure the database isn't set for exclusive use. If it is, it will only allow ONE connection, the others will fail until that one is completed.
0
 
LVL 19

Expert Comment

by:webwoman
ID: 7026876
Forgot this...
Make sure it's not set to lock the ENTIRE database when you access it. You should set it to lock only the record, not the table.
0
 
LVL 1

Accepted Solution

by:
pjbaratelli earned 150 total points
ID: 7027057
First off I would use the direct connection you are using.  Try creating a ODBC data source on the server and use that as your connection.  Then use this code.

<%
  sn = request("sn")
  pr = request("pr")

  set conn = Server.CreateObject("ADODB.Connection")
  conn.open [ODBC Connection Name],[userName],[password]

  sql = "Select * from user where serialno = '" & sn & "'"
  set rs = conn.Execute(sql)

  rc = rs.recordcount ' Gets the amount of records returned.

  if rc > 0 then
    rs.movefirst
    amt = rs.fields("amount")
  else
    response.write("Account not found on system.")
  end if
 
  rs.close
  set rs = nothing

  newAmount = pr + amt

  response.write "New Amount: " & newAmount

  sql = "UPDATE user SET amount=& newAmount & " " & _
        "WHERE serialno='" & sn & "'"
  set rs = conn.Execute(sql)
  set rs = nothing
  conn.close
  set conn = nothing
%>

  If you don't need to see the amount updated and you are sure that there is an account for the serial number passed try this.

<%
  sn = request("sn")
  pr = request("pr")

  set conn = Server.CreateObject("ADODB.Connection")
  conn.open [ODBC Connection Name],[userName],[password]

  sql = "UPDATE user SET amount=amount+" & pr & " " & _
        "WHERE serialno='" & sn & "'"
  set rs = conn.Execute(sql)
  set rs = nothing
  conn.close
  set conn = nothing
%>

Also, for IIS4 when opening a recordset use..

 rs.open sql,conn,3,3

hope this helps
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7027058
oops...
I meant 'I would not use the connection you are using...'

0
 
LVL 22

Expert Comment

by:CJ_S
ID: 7027063
pjbaratelli ,
it is ALWAYS recommended to NOT use ODBC but a DSN-less connection. That way you save the roundtrip to the registry which is rather slow.

CJ
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7027159
This was what I would would do regardless of the slowness, which in my experience has not ever added up to a second, because I found it more stable and reliable on my system when connecting to an access database.  And if I were dealing with that many records that it did (as in millions or even 100,000+) I would use a different database platform.

When attaching to other database servers that are more robust, I use DSN-less connections.

All the tools provided to connect to a database can be used and are a matter of preference. And as you mentioned, it is recommended and not required.  So don't get your panies in an uproar.
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7027189
ginaa,
 if you do not want to use an odbc connection, it's your choice.  It is not required for the code to work.  Only that there is a connection to the database.
0
 
LVL 19

Expert Comment

by:webwoman
ID: 7027202
The other consideration is whether you can create a DSN or not. Not everybody can, and not for every database you might use.

Like everything else to do with computers, it depends. ;-)
0
 
LVL 4

Expert Comment

by:Zvika
ID: 7027306
As far as I know Access allows up to 10 connections at once, so less then 10 should not make any problems but try changing :
----------------
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB.recordset")
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, Conn, 2, 3
----------------
to:
----------------
ConnStr = "DBQ=" & Server.MapPath("db1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
set rs = Server.CreateObject("ADODB.recordset")
SQL = "Select * from user where serialno = '" & sn & "'"
rs.open SQL, ConnStr, 2, 3
----------------
so instead of openeing a connection, then opening a recordset with the already opened connection object, you open the connection only as needed for the recordset.
this way you will not need the connection object at all.
You will also should not call rs.close, setting rs = Nothing will close the connection as well.
I think it's a better style of coding and opening recorsets, and I beleive it may solve your problem.
and another thing, why don't you use:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_filePath;Mode=ReadWrite"
as your connection string?
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7027360
If you are using access 97 it's up to 10 connections.  Access 2000 allows more, though I am not sure of the number..

On my NT server when I open a recordset I use
  rs.open sql,conn,3,3

On 2000 server :

  rs.open sql,conn,3,1
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7027432
If you are using access 97 it's up to 10 connections.  Access 2000 allows more, though I am not sure of the number..

On my NT server when I open a recordset I use
  rs.open sql,conn,3,3

On 2000 server :

  rs.open sql,conn,3,1
0
 
LVL 15

Expert Comment

by:robbert
ID: 7027620
A specific IIS configuration would cause exactly this behaviour:

In Internet Services Manager, right-click your website (resp. "Default Website"), Properties, tab Home Directory, Application Settings, Configuration,

make sure "Cache ISAPI applications" is checked
0
 

Author Comment

by:ginaa
ID: 7028825
Thanks every,
I try all cases according your recommands. It still has problem.
Right now I use Microsoft JET OLEDB engine.
When concurrent open a mdb file, only one connection is OK. It's reasonable. And I need NOT wait a period time for next connection.
So now, the question is how to set lock to record, not the table. In the connection string? What's the format?
Now I use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb".
Because if add "Mode=ReadWrite" it say can NOT lock file.
0
 
LVL 1

Assisted Solution

by:lalithaw
lalithaw earned 150 total points
ID: 7028874
Hi Ginna.,

When u r doing data handling always reduce the number of times u creating the connection to db.If U creating connection object for each recordset then u wasting resources.Always use one connection to create recordsets.

When U create the recordset use adOpenkeyset and lock type as adLockOptimistic.This lock type lock the db when only u execute the update command.

And have u heard about connection pooling.Here u use ur idle connections without creating new connections.If u have enabled connection pooling system first check if idle connection there if so check connection properties(uid,pwd).If those matches it will assign that connection for u.To be in connection pooling u should use  define connectionstring in a variable.
U can selectively set the CPTimeout property to enable connection pooling for specific ODBC database driver by creating a registry key with the following settings.

\HKEY_LoCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\driver-name\CPTimeout=timeout(REG_SZ)
CPTimeout property units are in seconds.

Ex.

\HKEY_LoCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Sql Server\CPTimeout=180


0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7029573
ODBC has the ability to provide for multiple concurrent connections..
0
 

Author Comment

by:ginaa
ID: 7030874
Or my testing method are wrong??
I write an unix client program. That program make socket connection to server port 80.
In that program, I send a string "GET /db3.asp?sn=argv[3]&amt=argv[4] HTTP/1.0\n\n", and wait response, then close and exit.
In normal case,  it's OK.
Then I write a batch, call the client 10 times for different serialno and run in backgroud.
batch file:
   cli serv_ip 80 0000001 100 &
   cli serv_ip 80 0000002 100 &
     ............
   cli serv_ip 80 0000010 100 &
Only one, at most two can success. Why?????
Thanks
0
 

Author Comment

by:ginaa
ID: 7038109
So the key point is how to lock record(recordset?) instead of full database file?
Thanks
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7038314
Hi ginaa

Yes dont lock the whole database.Lock the recordset which u r going to access.And lock it as late as possible and release it as soon as possible.And use adLockOptimistic method.Then it will lock the recordset when u execute the update method.This will help u to improve ur program performance.

Lalitha.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:ginaa
ID: 7038328
Thanks,
In my ASP code :
    .....
    set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "DBQ=" & Server.MapPath("db1.mdb") & ";Driver=(Microsoft Access Driver (*.mdb)};FIL=MS Access;"
    set rs = Server.CreateObject("ADODB.recordset")
    SQL = "Select * from user where serialno = '" & sn & "'"
    rs.open SQL, Conn, 2, 3
    .....

The param 4 of rs.open, value = 3 means adLockOptimistic.
But it doesn't work.
So I need someone tell me what's wrong in my code?
0
 
LVL 19

Expert Comment

by:webwoman
ID: 7038353
What errors do you get?
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7038357
Ginna.,

try this code .this one is working .I got it from one of my asp page that I developed.Change the provider parameter to suit to ur access driver.This is for access 97.If u r using access 2000 then it would be "Microsoft.Jet.OLEDB.4".

set conn = Server.CreateObject("ADODB.connection")
         
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb"
conn.Open
     
set rs = Server.CreateObject("ADODB.Recordset")
         
rs.Open "Select * from user where serialno = '" & sn & "'"
   ,conn,adOpenDynamic,adLockOptimistic

If u want full source code tell me I will send u.

Lalitha.
0
 

Author Comment

by:ginaa
ID: 7038827
I try yuor comment, and one is ok.
my system in Chinese.
It response said "file in use"
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7038858
ginaa.,

May be u have opened that perticular database in MSAccess editor.Just check whether u have opened it in Access.

Lalitha.
0
 

Author Comment

by:ginaa
ID: 7040680
I have tried several days.
I am sure I don't open it in Access editor at time.
Could it be problem in attribute of mdb file?
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7040861
Ginaa.,

Sometimes this happen because the application which access the database is not propely closed in previous times.So database may be locked.What u have to do is check for the databse folder if it has .ldb file then it is confirmed that databse was locked.(if ur database is db1.mdb then ldb file is db1.ldb).Solution is remove the .ldb file reboot the machine and run ur application again.If any problem write me again.

Lalitha.
0
 

Author Comment

by:ginaa
ID: 7040901
Thanks,
When I open mdb file with access editor, there is a .ldb file in folder. After exit editor, it disapper.
When I want to test, I check there is no .ldb file exist.
In my batch file, if I call client application 10 times in background, it will fail.
But if I call it sequencially in batch file, it will be OK for all 10 calls. So I think, the ASP really unlocked the mdb file after finish.

Ginaa
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7041019
ginna..,

Now I undestood the problem.To solve this u have to do this.

This happen because u have written code to open the recordset as when as u open the asp page.So when u open the asp page simultaniosly 10 times ,u open the recordset 10 times.This problem occurs u going to open the recordset that already opened in another browser.What u have to do is create the connection in asp normal way and put the recordset open code in command button.Then it will not encounter the problem when u open the asp page.U can write the code to create the connection in global.asa if u using visual interdev.so then it will be very efficient because then u donot need to create the connection each and everytime.
And check the lock type u have put.it should be adLockOptimistic.And one another important thing if u are not going to update the recordset then use the readonly recorset.if so u wont connect the database the all the time that open the recordset.It will retrieve the recordset and close the connection to database.

If u r not going to recordset then use this code.,

set conn = Server.CreateObject("ADODB.connection")
         
conn.Provider = "Microsoft.Jet.OLEDB.3.51"
conn.ConnectionString = "Data source=c:\database\db1.mdb"
conn.Open
   
set rs = Server.CreateObject("ADODB.Recordset")
         
rs.Open "Select * from user where serialno = '" & sn & "'"
  ,conn,adOpenForwardOnly,adLockReadOnly

rs.close

DONT forget to close the recordset after u retrieve the recordset.From that u will unlock the database.

If u using the recordset for updation then write the code for connection in normal way and write the code to open the recordset and update in commandbutton and close it after u did the operation.And use the adOpenKeyset and adLockOptimistic.

Tell me if u have any problem.

Lalitha.




0
 

Author Comment

by:ginaa
ID: 7041214
Oh! God.
Finally, I use PostgreSQL on linux as database server and PostgreSQL ODBC driver for WinNT ASP to query.
Then everything is OK.
So, is the MS access ODBC driver version too old?
I use WinNT 4.0 server, sp6, IIS 4.0, MS Access driver 4.00.3711.08.
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7041463
Ginaa.,

Thats depend on ur MS Access version.If u r using Access 97
then driver 3.51.if u r using MSAcess 2000 then it will be 4.00.3711.08.

Lalitha.
0
 

Author Comment

by:ginaa
ID: 7043307
Thanks.
I use MS Access2000, so the version is OK.
Then this problem still has no proper solution.
No one ever do testing like me?

Ginaa
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7043576
ginaa.,

Just try what I have told u in 05/28/2002 10:55PM comment.I hope u will get good solution for that.

Lalitha.
0
 

Author Comment

by:ginaa
ID: 7044103
Lalitha,
I have try your code and it's great.
But I do the testing is trying to simulate multi-user UPDATE their records at the same time. Each one update his individual record, but all in same database table.
In my case, I think, system should manage concurrent access to database for me if I do the right setting.
I have try two drivers, one for SQL server, the other is MSAccess, and SQL server is OK and MSAccess is not.
Essentially, I think the ASP code is OK except parameter setting. So my question is what is right setting or correctly function call.
Thanks
Ginaa
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7044161
Ginaa.,

Yes u r right.Normaly when u do the transaction with MS Access then system is lock whole the table .But thats depend on ur locking mechanism.If u put adLockPessimistic then it will lock whole table when u create the recordset.If u put adLockOptimistic then it will lock whole table when only u call the update method.So thats why I told u to create the recordset when as only necessary and close the recordset as soon as possible.So better create the recordset inside the command button code and close it inside same code.In MSSQL 7.0 u have page lock.But it doesnot lock whole table.If u using MSSQL 2000 no problem it will lock only the record.So thats why u didnt encounter that problem with MSSQL.
But U have another solution thats if u r using MTS(Microsoft transaction server).U can create MTS component(DCOM) and let it to handle the transaction.Just write the code inside the component to create the recordset and update.Dont forget to pass the connection parameter to component.So it will handle all concorrent transaction and u dont need to worry about that.

lalitha.
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7044504
I know I sound like a broken record, but I maintain a Access Database with 2000 users and over 30,000 active records with an average of 2.4 concurrent connections using an ODBC connection(100,000+ auditing records).  Some of the web pages make enormous calls to the database return over a 1000 records on a single page(this takes about a second at the most).  I have never had any issues with speed or connection problems using an ODBC connection to an access database.  The ODBC connection will handle your issue with multiple connections to the database.

Another thing to consider is if you look at alot of the MS Office Applications, you will see they use ODBC to make a majority of their connections.
0
 

Author Comment

by:ginaa
ID: 7045717
pjbaratelli,
Thanks.
Is it possible your concurrent access are all opened in read only?
Ginaa
0
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7046736
User can insert,update,delete,and read any data they wish at any time they wish.
0
 

Author Comment

by:ginaa
ID: 7050257
MSAccess is so popular product, so I don't think the problem is caused by it. It should be me.
But I really don't understand what mistake I take?

Ginaa
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7050312
ginna.,

MSAcess is popular and good product thats right.And u can add,delete,update and can do any operation in data manipulations.But thats depend on ur code how u write the code.As I told u in last my comments u should organize ur code properly.U cant write evry operations in every places.It has some suitable places to write the code.Read my previous comments u will get good picture.

Lalitha.
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8836654
No comment has been added lately and it seems that this question have been abandoned. So it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question or invite a Moderator to close this question if there's no reply from you after seven days.

In the absence of responses, I will recommend the following:

Split  points  to lalithaw pjbaratelli

** PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER **


Just trying to help for the cleanup...
gladxml
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now