Solved

problem to access database

Posted on 2002-05-21
13
181 Views
Last Modified: 2010-04-07
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
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 7029543
first I would move the closing of the recordset and connection to outside of the if then else.

This code looks fine and it seems to be only one query.

CJ
0
 
LVL 1

Expert Comment

by:raid999
ID: 7031912
to open connection use this
<!-- #include file="adovbs.inc" -->
<%
Dim dbc
Dim strConn
Dim strpath

'this procedure is called wherever a connection is needed. Returns 'dbc' as active connection
sub openConn()
     'use appropriate connection string
     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("db/scart.mdb")

     Set dbc = Server.CreateObject("ADODB.Connection")
     dbc.open strConn
end sub

sub closeConn()
     if isobject(dbc) then
          if dbc.State = adStateOpen then
               dbc.Close
          end if
          set dbc = nothing
     end if
end sub
%>

And then to use the connection include the page in all files like this
<!-- #include file="db.asp" -->
and inside that new page use this only
sqlAdd ="sqlStatment"
call openConn()
dbc.execute sqlAdd
0
 

Author Comment

by:ginaa
ID: 7036438
Thanks, I have tried all your comments.
For single connection, it's OK.
And then I write an unix client program.
This program connect to server port 80.
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(single connection),  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.
It seems, when first one connected, it lock full database file. So who do I call or set to let it lock record only?
Thanks
0
 

Author Comment

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

Author Comment

by:ginaa
ID: 7041219
Finally, I use PostgreSQL on linux as database server and PostgreSQL ODBC driver for WinNT ASP to query.
Then everything is OK.
So, is it possible 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 19

Expert Comment

by:cheekycj
ID: 7057842
Hi.. sorry for the late response.. but do you still need help with this?

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

 

Author Comment

by:ginaa
ID: 7058142
Thanks,
This problem can be solved by using SQL server.
But any comment or solution for MSaccess are still welcome.
Ginaa
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 7059249
It maybe me just an issue with MS Access (though I am not sure why) but since an upgrade to a better Database fixed it, leads me to believe the code is fine.

CJ
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 7120362
Access databases, under Options, have properties 'record locking,' and 'default open mode.'

Set the first to 'No Locks,' and the second to 'Shared.'

I'm not sure whether the MS Access driver, in conjunction with ADODB has a locking protocol, but in using Oracle with ODBC and ADODB we are setting locking to 'Optimistic.'

It sounds to me like the ASP is not waiting if the DB is in use.  Perhaps there is a way to get your page to wait maybe a half second if the DB access fails, and try again.  Maybe even more than once.  (In a logging function that I've written, if I can't access the text file to write to it, I try repeatedly in the error handler until half a second has gone by.  Text file access is quick, but if you can't gain access to it you can't write...)

make sure that your table, 'user,' is indexed.  (That will speed acces to it, minimizing collisions.)

Also, ODBC and connections have settings for how much time constitutes a failure to communicate.  Make this number as large as you can tolerate.  This will build in tolerance.

Finally, as a style issue, I would avoid exclamation marks in error messages.  It's like being shouted at, and doesn't really help get the message across....  Check out the error messages that Microsoft has built into the OS for various errors, and you will see that there are almost no '!'s.

Brian Wren
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 8148403
This question has been abandoned. I will make a recommendation to the
moderators on its resolution in a week or two. I appreciate any comments
that would help me to make a recommendation.
<note>
   In the absence of responses, I may recommend DELETE unless it is clear
   to me that it has value as a PAQ.  Silence = you don't care
</note>

Cd&
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8152783
IMHO: PAQ
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 8187109
It is time to clean this abandoned question up.  

I am putting it on a clean up list for CS.

<recommendation>
zero point PAQ

</recommendation>

If anyone participating in the Q disagrees with the recommendation,
please leave a comment for the mods.

Cd&
0
 

Accepted Solution

by:
Chmod earned 0 total points
ID: 8240229
As recommended

Chmod
Community Support Moderator @Experts Exchange
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

743 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

13 Experts available now in Live!

Get 1:1 Help Now