Access DB ASP connection problems

Hey all

Sorry, you'll have to excuse my newbieness.

I'm developing a simple web app in DRW to display some products stored in an
Access DB.

The web site seems to work fine, but I keep getting this error:
Provider error '80004005'
Unspecified error
/giftything/product_pages/productdetail.asp, line 17

Line 17 is as follows:
rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING

It seems to me that when I open a page it maintains exclusive access to the
database and nothing else can access it. If I try to load another page I just
get the above error.

This eventually times out after about 2 minutes or so and you can then load
another page, but then I'm faced with the same problem when trying to open
another page.

Is there a way to close the DB connection when a page has finished loading?

Or is this problem something else entirely?

Any help anyone can give will be much appreciated. Thanks in advance

Georges

Re: Access DB ASP connection problems

Georges Erhard wrote:
> Hey all
>
> Sorry, you'll have to excuse my newbieness.
>
> I'm developing a simple web app in DRW to display some products
> stored in an Access DB.
>
> The web site seems to work fine, but I keep getting this error:
> Provider error '80004005'
> Unspecified error
> /giftything/product_pages/productdetail.asp, line 17
>
> Line 17 is as follows:
> rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING

OK, it looks like you're using DW, a RAD tool that promotes bad programming
practices and creates incredibly long, unreadable variable names ;-)

The bad practice I'm referring to here is the failure to use an explicit
connection object. This is bad because it defeats the purpose of session
pooling, a technology built into ADO that allows connections to be re-used,
saving cpu and resources. Here is some info about session pooling:
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp

The better technique is to instantiate a connection object:
dim cn
set cn=createobject("adodb.connection")

Open it:
cn.open MM_connDBGiftything_Products_STRING

and use it in your page, closing and destroying it as soon as you are
finished (just remember to close and destroy all recordsets created using
this connection object first). This releases the connection and allows it to
be put in the session pool.

When another page goes to create a connection, instead of just creating a
new one, ADO will look in the session pool, and, if a connection is
available, it will grab it and use it, only creating a new one if none are
available in the pool.

>
> It seems to me that when I open a page it maintains exclusive access
> to the database and nothing else can access it. If I try to load
> another page I just get the above error.

This is almost always due to file system permissions being set incorrectly.
In order for multi-user activity to be allowed for an Access database, a
locking file (database_name.ldb) needs to be created, modified and deleted
by the users using the database. The first user creates the ldb file,
subsequent users modify it, and the last user deletes it. If the users do
not have Modify permissions for the folder containing the mdb file, only
exclusive access to the database will be allowed.

So, what does this mean?
If you are in an intranet environment, and your website is using Integrated
Authentication with Anonymous access disabled, then ALL users must be
granted Modify permissions to the folder containing the database file.
If Anonymous access is turned on, then the Internet Guest Account
(IUSR_) requires that permission (sometimes, the
IWAM_ account also requires those permissions).
These articles cover the problem:
http://www.aspfaq.com/show.asp?id=2009
http://www.aspfaq.com/show.asp?id=2205



Here is some more reading for you to do:
http://www.aspfaq.com/show.asp?id=2126

http://mvp.unixwiz.net/techtips/sql-injection.html
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
http://www.aspfaq.com/show.asp?id=2467

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access DB ASP connection problems

"Bob Barrows [MVP]" wrote:

> Georges Erhard wrote:
> > Hey all
> >
> > Sorry, you'll have to excuse my newbieness.
> >
> > I'm developing a simple web app in DRW to display some products
> > stored in an Access DB.
> >
> > The web site seems to work fine, but I keep getting this error:
> > Provider error '80004005'
> > Unspecified error
> > /giftything/product_pages/productdetail.asp, line 17
> >
> > Line 17 is as follows:
> > rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING
>
> OK, it looks like you're using DW, a RAD tool that promotes bad programming
> practices and creates incredibly long, unreadable variable names ;-)
>
> The bad practice I'm referring to here is the failure to use an explicit
> connection object. This is bad because it defeats the purpose of session
> pooling, a technology built into ADO that allows connections to be re-used,
> saving cpu and resources. Here is some info about session pooling:
> http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp
>
> The better technique is to instantiate a connection object:
> dim cn
> set cn=createobject("adodb.connection")
>
> Open it:
> cn.open MM_connDBGiftything_Products_STRING
>
> and use it in your page, closing and destroying it as soon as you are
> finished (just remember to close and destroy all recordsets created using
> this connection object first). This releases the connection and allows it to
> be put in the session pool.
>
> When another page goes to create a connection, instead of just creating a
> new one, ADO will look in the session pool, and, if a connection is
> available, it will grab it and use it, only creating a new one if none are
> available in the pool.
>
> >
> > It seems to me that when I open a page it maintains exclusive access
> > to the database and nothing else can access it. If I try to load
> > another page I just get the above error.
>
> This is almost always due to file system permissions being set incorrectly.
> In order for multi-user activity to be allowed for an Access database, a
> locking file (database_name.ldb) needs to be created, modified and deleted
> by the users using the database. The first user creates the ldb file,
> subsequent users modify it, and the last user deletes it. If the users do
> not have Modify permissions for the folder containing the mdb file, only
> exclusive access to the database will be allowed.
>
> So, what does this mean?
> If you are in an intranet environment, and your website is using Integrated
> Authentication with Anonymous access disabled, then ALL users must be
> granted Modify permissions to the folder containing the database file.
> If Anonymous access is turned on, then the Internet Guest Account
> (IUSR_) requires that permission (sometimes, the
> IWAM_ account also requires those permissions).
> These articles cover the problem:
> http://www.aspfaq.com/show.asp?id=2009
> http://www.aspfaq.com/show.asp?id=2205
>
>
>
> Here is some more reading for you to do:
> http://www.aspfaq.com/show.asp?id=2126
>
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
> http://www.aspfaq.com/show.asp?id=2467
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Thanks Bob for your reply.

I have been reading through the articles you posted links to (trying to
understand them, albeit not very well) and, well the only thing I can say is
"EEEK!". I feel as though I am in well over my head.

I think I understand the overall concept of what is going on, I'm just not
sure how to apply this knowledge.

Looking at the connection code that you provided, how do I apply this?
Let me try to clear this question up a little.

At the moment the connection code I have is as follows:
<%
Dim rsAllProducts
Dim rsAllProducts_numRows

Set rsAllProducts = Server.CreateObject("ADODB.Recordset")
rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING
rsAllProducts.Source = "SELECT * FROM products"
rsAllProducts.CursorType = 0
rsAllProducts.CursorLocation = 2
rsAllProducts.LockType = 1
rsAllProducts.Open()
rsAllProducts_numRows = 0
%>

As you indicated this is generated by Dreamweaver and is quite excessive.
Thing is I can see what is going on. Where the Recordset is created and a SQL
statement attached. Now taking your connection code (below):

<%
dim cn
set cn=createobject("adodb.connection")

cn.open MM_connDBGiftything_Products_STRING
%>

Basically I'm struggling with this because I have no knowledge of ASP or
VBScript Syntax - problem number 1.

I understand that this is opening the database through the DSN, but I don't
know how to create a recordset from this point on and assign a SQL statement
to it.
Do you know of any web-sites that contain sample code that I could look at?
Is it the same as what I already have or completely different?

See what I mean. I'm a complete newbie to this. Apologies if I'm being a
complete muppet.

Thanks
Georges

Re: Access DB ASP connection problems

Georges Erhard wrote:
>
> At the moment the connection code I have is as follows:
> <%
> Dim rsAllProducts
> Dim rsAllProducts_numRows
>
> Set rsAllProducts = Server.CreateObject("ADODB.Recordset")
> rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING
> rsAllProducts.Source = "SELECT * FROM products"
> rsAllProducts.CursorType = 0
> rsAllProducts.CursorLocation = 2
> rsAllProducts.LockType = 1
> rsAllProducts.Open()
> rsAllProducts_numRows = 0
> %>
>
> As you indicated this is generated by Dreamweaver and is quite
> excessive.
> Thing is I can see what is going on. Where the Recordset is created
> and a SQL
> statement attached. Now taking your connection code (below):
>
> <%
> dim cn
> set cn=createobject("adodb.connection")
>
> cn.open MM_connDBGiftything_Products_STRING
> %>
>
> Basically I'm struggling with this because I have no knowledge of ASP
> or
> VBScript Syntax - problem number 1.
>
> I understand that this is opening the database through the DSN

No, it's opening a connection to the database, a connection that can be used
to open multiple recordsets and execute multiple commands.

Regarding that "DSN", you need to replace it with a dsn-less ole db
connection string. See:
http://www.aspfaq.com/show.asp?id=2126

To apply it for your simple code above, which is opening a server-side
forward-only cursor (which happens to be the default cursor that will be
opened if you do not set any of those properties), simply use the
connection's Execute method:

'always assign your sql statement to a variable for easy debugging:
dim sql
sql="select * from products"

Set rsAllProducts = cn.Execute(sql,,1) '1=adCmdText


> , but I don't
> know how to create a recordset from this point on and assign a SQL
> statement
> to it.
> Do you know of any web-sites that contain sample code that I could
> look at?

The problem is, many of the websites (and books for that matter) take the
easy way out and teach you how to use dynamic sql, which is the least
effective as well as least secure way of executing queries. However, people
have mentioned www.asp101.com, which has many tutorials. Just be sure to go
back and look at the posts I cited after you have finished the tutorials to
see the better way (parameters) of working with databases.

Also, you can find all the documentation about vbscript and ADO at
msdn.microsoft.com/library. For example, the ADO documentation is at:
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadoo verview.asp

The vbscript documentation is at:
http://msdn.microsoft.com/library/en-us/script56/html/0a8270 d7-7d8f-4368-b2a7-065acb52fc54.asp


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access DB ASP connection problems

"Bob Barrows [MVP]" wrote:

> Georges Erhard wrote:
> >
> > At the moment the connection code I have is as follows:
> > <%
> > Dim rsAllProducts
> > Dim rsAllProducts_numRows
> >
> > Set rsAllProducts = Server.CreateObject("ADODB.Recordset")
> > rsAllProducts.ActiveConnection = MM_connDBGiftything_Products_STRING
> > rsAllProducts.Source = "SELECT * FROM products"
> > rsAllProducts.CursorType = 0
> > rsAllProducts.CursorLocation = 2
> > rsAllProducts.LockType = 1
> > rsAllProducts.Open()
> > rsAllProducts_numRows = 0
> > %>
> >
> > As you indicated this is generated by Dreamweaver and is quite
> > excessive.
> > Thing is I can see what is going on. Where the Recordset is created
> > and a SQL
> > statement attached. Now taking your connection code (below):
> >
> > <%
> > dim cn
> > set cn=createobject("adodb.connection")
> >
> > cn.open MM_connDBGiftything_Products_STRING
> > %>
> >
> > Basically I'm struggling with this because I have no knowledge of ASP
> > or
> > VBScript Syntax - problem number 1.
> >
> > I understand that this is opening the database through the DSN
>
> No, it's opening a connection to the database, a connection that can be used
> to open multiple recordsets and execute multiple commands.
>
> Regarding that "DSN", you need to replace it with a dsn-less ole db
> connection string. See:
> http://www.aspfaq.com/show.asp?id=2126
>
> To apply it for your simple code above, which is opening a server-side
> forward-only cursor (which happens to be the default cursor that will be
> opened if you do not set any of those properties), simply use the
> connection's Execute method:
>
> 'always assign your sql statement to a variable for easy debugging:
> dim sql
> sql="select * from products"
>
> Set rsAllProducts = cn.Execute(sql,,1) '1=adCmdText
>
>
> > , but I don't
> > know how to create a recordset from this point on and assign a SQL
> > statement
> > to it.
> > Do you know of any web-sites that contain sample code that I could
> > look at?
>
> The problem is, many of the websites (and books for that matter) take the
> easy way out and teach you how to use dynamic sql, which is the least
> effective as well as least secure way of executing queries. However, people
> have mentioned www.asp101.com, which has many tutorials. Just be sure to go
> back and look at the posts I cited after you have finished the tutorials to
> see the better way (parameters) of working with databases.
>
> Also, you can find all the documentation about vbscript and ADO at
> msdn.microsoft.com/library. For example, the ADO documentation is at:
> http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadoo verview.asp
>
> The vbscript documentation is at:
> http://msdn.microsoft.com/library/en-us/script56/html/0a8270 d7-7d8f-4368-b2a7-065acb52fc54.asp
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>


Thanks again you have been a great help.

I'll let you know how I get on.