ASP DSN help needed

**krack_it_up** · 04-03-2002, 06:11 PM

Ok, I can create an odbc connection to my access database, and use that connection on the asp. The problem is, after I open the page a few times in the browser, I get an error saying that I dont have permissions to access the ('unknown') file. Wait a bit and then it works again. I think the page is locking the database somehow. Any ideas on how to prevent or change this? I am using vbscript in my asp. I can post code if it would help. TIA

**mefisto3** · 04-03-2002, 06:45 PM

yeah. i think posting the code would help.

i am not sure but it sounds to me like u have a limited number of connections available to connect to database. when u click on the page u open up a connection. after u run the query, maybe u don't close it properly and therefore it is not released until it times out. thus when u open your page few times all the connections get used up and u can't connect anymore. after a while they time out and get released and u can access the db again.

**krack_it_up** · 04-03-2002, 07:16 PM

That is what I was thinking, I just dont know how to change the locking issue.

Here is the code...

<HTML>
<HEAD>
<TITLE>Day 3</TITLE>
</HEAD>
<BODY>
<P> bla bla bla </P>
<%
DIM orsq
SET orsq = server.CreateObject("ADODB.recordset")
sqls = "SELECT a.ProblemCode, c.ItemName AS Item, b.CategoryName AS Category, d.ActionName AS [Action] "
sqls = sqls & " FROM ProblemCodes a JOIN Categories b ON a.Category = b.Category "
sqls = sqls & " JOIN Items c ON a.Item = c.Item JOIN Actions d ON a.[action] = d.[action];"
'write the sql code to debug
Response.Write "<FONT COLOR=blue>" & sqls & "</FONT>"
orsq.open sqls, "DSN=Help;UID=Net;pwd=changed"
orsq.movefirst
Response.Write "<TABLE BORDER='1'>"
DO WHILE NOT orsq.EOF
Response.Write "<TR><TD>" & orsq("Category") & "</TD>"
Response.Write "<TD>" & orsq("Action") & "</TD>"
Response.Write "<TD>" & orsq("Item") & "</TD></TR>"
orsq.movenext
LOOP
Response.Write "</TABLE>"
orsq.close
SET orsq = nothing
%>
</BODY>
</HTML>

The database is a simple access db. The sql works fine. I dunno why it is locking the database if that is indeed what is happening.

**GrayCalx** · 04-04-2002, 08:39 PM

Originally posted by krack_it_up
That is what I was thinking, I just dont know how to change the locking issue.

...

The database is a simple access db. The sql works fine. I dunno why it is locking the database if that is indeed what is happening.

Not sure if you're hosting or on your own machine, but check to make sure the directory and the file itself have read/write access for IUSER... that usually does it.

[edit]
Oooh, also make sure you don't have access open when you query it, I know I've had problems with that too.
[/edit]

**Pestilence** · 04-05-2002, 09:31 AM

Originally posted by GrayCalx
Oooh, also make sure you don't have access open when you query it, I know I've had problems with that too.

I've done that so many times it's not even funny. That sounds like it might be your problem. Also I don't believe you need to have access completely shut down. I believe it's ok to have it open as long as you only see the list of tables, but aren't in design view or anything.

**krack_it_up** · 04-05-2002, 12:38 PM

Ok, but here's the problem. I am trying to make a helpdesk solution that will allow our local helpdesk staff (HD) to log in, AND allow the techs at our remote hospitals to log in on the intranet simultaneously. The HD creates new tickets when a call comes in, and the techs close the tickets over the web. But if what you are saying is true, then our HD staff cant be in the access front end the same time the techs are in on the web. Come to think of it, that may be exactly the problem. Looks like I'll have to think up another solution... maybe if I used linked tables.

**Pestilence** · 04-05-2002, 03:30 PM

Could you not create a web interface for both the help desk and the hospital people and have it so that when the techs try to close it the error message redirects them to a page to try and close the ticket later? You could create a COM object as well that the page would pass off all the values to and the COM object would repeatedly hit the database at certain time intervals to see if it could close the ticket by updating that row. That way I think the techs could move onto another page and still be assured that the data would go through.

**krack_it_up** · 04-05-2002, 07:01 PM

Humm.... interesting idea... The idea, though, was to have the Techs close out the tickets from the computers they just fixed. That way, they have all the machine info right there. Problem is they wont be able to just leave the page open sitting there till it becomes avaliable. Potentially the HelpDesk people will never close the application. We figure that between 3 of them, at least one of them is on the phone and in the system at any given time.

On the positive side, I have been talking to the powers that be and believe that this will actually justify the SQL server license I have been pushing for. It seems really odd, though, that you can not have both interfaces operating at the same time. Thanks for the input. Any other ideas or suggestions are more than welcome.

**GrayCalx** · 04-09-2002, 06:42 PM

Originally posted by krack_it_up

On the positive side, I have been talking to the powers that be and believe that this will actually justify the SQL server license I have been pushing for. It seems really odd, though, that you can not have both interfaces operating at the same time. Thanks for the input. Any other ideas or suggestions are more than welcome.

What about creating a local web frontend for the local users?

**krack_it_up** · 04-09-2002, 11:52 PM

Well, my VBA skills are mych greater than my ASP and HTML skills. I would love to build a web front end, but unfortunately as of now, I can not get the functionality in it that is needed. I am learning, but it is not fast enough.

Thread: ASP DSN help needed

Thread Tools

Display

ASP DSN help needed

Posting Permissions