Solved

problem to access database

Posted on 2002-05-21
13
186 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. 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 : Go t…

733 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