Added: April/27/2004 at 8:04pm | IP Logged

First of all. Use a DSN-LESS connection if possible. They perform better and are easier to set up. Read this article and the related articles listed as well.

SYSTEM DSN Connection

If using a SYSTEM DSN connection a SYSTEM DSN must be set up on the server. This has to be done by the server admins.

Here is an example of accessing a SYSTEM DSN called "mydatabase"

<%
Dim DataConn
Dim CmdSimpleSelect
Dim MYSQL
   
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdSimpleSelect = Server.CreateObject("ADODB.Recordset")
   
DataConn.Open "DSN=mydatabase"
   
MYSQL = "SELECT ID, NAME, EMAIL, MESSAGE FROM some_table"
   
CmdSimpleSelect.Open MYSQL, DataConn
%>

<%= CmdSimpleSelect("ID") %><br>
<%= CmdSimpleSelect("NAME") %><br>
<%= CmdSimpleSelect("MESSAGE") %><br>
<%= CmdSimpleSelect("EMAIL") %><br>

<%
CmdSimpleSelect.Close
Set CmdSimpleSelect = Nothing
DataConn.Close
Set DataConn = Nothing
%>

To create a system DSN you basically do the following.

In the Control Panel, double click the icon for the ODBC Datasource Administrator. In Windows 2000, the ODBC Administrator is located under Start Menu|Settings|Control Panel|Administrative Tools. Click on the System DSN tab, then click Add. Select the Microsoft Access Driver and click Finish. You will now see a select database dialog. Enter the desired DSN in the Datasource name field. You can leave the description blank or put something there if you want. It doesn't matter. Now click on the Select button, then browse down to find the location of the web you have recently created. The path should be something like C:\inetpub\wwwroot\myweb. Once you locate the web's directory, continue to browse until you find the database for this web (inetpub\wwwroot\myweb\_database\mydatabase.mdb) When you open the folder the database is in the MDB file will appear to the left. Select the MDB file and click ok. Continue to click Ok until you have closed all open windows.

Remember that you will have to ask the server admins to create this for you if you do not have direct access to the server. They may prefer that you simply use a DSN-LESS connection and may not be willing to do this.

NOTE: There is also an advanced screen in the ODBC screens as well and if your Access Database has a password set on it you MUST go into that screen and enter the Username & Password information. It will not work otherwise. Even if you specify the Username & Password in the code the setting from the ODBC will override it.

DSN-LESS Connection

You put in the physical path to the database like so.

<%
DataConn.Open "DBQ=C:\Inetpub\wwwroot\aspprotect\_database\mydatabase.mdb; Driver={Microsoft Access Driver (*.mdb)}"
%>

You can also use Server.MapPath if you are familiar with that.

<%
DataConn.Open  "DBQ=" & Server.MapPath("_database/mydatabase.mdb") & ";Driver={Microsoft Access Driver (*.mdb)}"
%>

You cannot specify the physical path using http syntax like this.

This is an example on what not to do.
<%
DataConn.Open  "DBQ=http//www.mysite.com/_database/mydatabase.mdb;Driver={Microsoft Access Driver (*.mdb)}"
%>
This is an example on what not to do.

It just doesn't work that way so don't even try it.

IMPORTANT

If running on a WinNT/WIN2000 web server with the NTFS file system it is very important that permissions are set on the directory the database is in.
The bottom of this article has more info on permissions and how they are set.

Also, since this code is accessing a database your system needs to have the proper ODBC drivers installed. Most systems will already have this installed but if you need to install the drivers or get updated drivers please go to the following site.

http://www.microsoft.com/data/
Look for the MDAC download.

A very common and extremely bothersome error encountered when running ASP apps that connect to a database is the "80004005" error. It comes in many varieties. The error usually occurs if the Everyone or IUSR account does not have Read, Write, Delete permission ( Change permission on NT4; Modify permissions on Windows 2000) on the folder on the server where the database resides. This permission is needed because site visitors via the web browser must be able to access the database via the ASP code.

Generally, the required permissions on this folder are set by the server administrator. This means the person/people that setup the server and keep it running, the people that have physical access to it. Just because you are the admin for a particular site that doesn't not make you the server admin. It is important that you understand the difference. Correct permissions settings are critical to the operation of the ASP code and they must be set by the server admin. For the most part you can not set these permissions using settings in Frontpage or FTP programs. Don't use CHMOD from your FTP program either. It can overwrite things an mess up your web and it's security.

Also realize when testing and running ASP apps with PWS on Windows 95/98, you will not need to set any permissions on the database. You only need to worry about this when running on a NT or 2000 web server using the NTFS file system.

Below are some examples of the many forms the "80004005" error can take.

[Microsoft][ODBC Microsoft Driver] Not a valid password.

Microsoft JET Database Engine (0x80040E4D)

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x6a4 Thread 0x744 DBC 0x25fafe4 Jet'.

These errors are basically because the database could not be found or accessed. If you see any variety of these errors it is most likely because the permissions have not been correctly set or you have not specified the data path correctly. It is also possible something is wrong on the server. Most of the time it is not because of a server problem.

These permissions can for the most part only be set by a server admin who has direct access to the web server. If you are not the server admin you will need to ask for them to set these permissions for you. If using a DSN-LESS connection you may also need to ask them what the physical path info is though the following URL may help you figure that out.
http://www.powerasp.com/content/hintstips/physical-path.asp

How Permissions are set.

Below is a quick summary of how permissions work and what Server Admins need to do to set them in case you are curious.

ASP pages are accessed by anonymous users via the web browser
When users access these pages via a web browser IIS will use (by default) a Windows NT account called iUSR_<machinename> or "everyone".

To set the permissions you have to be on the server and browse to the folder the database is in. Then right click and choose properties. Then you give that account change or modify permissions on the folder that the database is in. Change or modify means... r w x d (read/write/execute/delete). In the future when you put another database in that same directory it will usually inherit the permissions and you will not have to ask again. If you delete the directory and recreate it you will lose it's permissions and they will have to be set again.

Hopefully this gives you a better idea of what is going on when you can't get your ASP scripts and databases working correctly.

Related Info
Using ASP to create, edit, or delete information in a database.

Microsoft Knowledge Base Article Links

Added: March/28/2006 at 9:28pm | IP Logged

Hi

Can you help me on how make a connetion to database using global.asa?

I have tried many times but error. The purpose is I want to make a one time connection to database so my my application don't have to make and close connection many times. I believe it is not good for the application speed. I really need your help on the database connection and the best way to do it so that i dont have to repeat making a connection each time to open new recordset in my asp page.

Below is the sample of my global.asa which is not complete yet. Please advice on where should put the conn.close and set conn to nothing. If possible please reply to my email.

<script language="vbscript" runat="server">

sub Application_OnStart
 Dim Conn, ConnStr
 Set Conn = Server.CreateObject("ADODB.Connection")
 ConnStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &  Server.MapPath("dbase\DB_Master.mdb") & ";"
 Set Application("ConnectionString") = ConnStr
 
End sub

sub Session_OnStart

End sub

sub Session_OnEnd
 
End sub

sub Application_OnEnd

End sub

</script>

Added: March/28/2006 at 11:22pm | IP Logged

well, get rid of most of that crap and don't use server.mappath either.

all you want to do is store the connection string in an application variable...

personally I dont see why you just dont include a page specifying the data connection in every page of your application. there is no need to use the global.asa but it you really want to its this simple...

example... (just the appliation_onstart section)

Sub Application_OnStart

Application("ConnectionString") = "DBQ=C:\Inetpub\wwwroot\mysite\database\somedatabase.mdb;Dri ver={Microsoft Access Driver (*.mdb)};UID=Admin;Password="
 
End sub

If you dont know the physical path use server.mappath ONCE to figure it out

http://www.powerasp.com/content/hintstips/physical-path.asp


BTW: Your reasoning for why you want to use the global.asa makes ZERO sense as far as saving resources and not opening and closing things. You need to forget about whatever it is your thinking. You can't jsut open things once like what your saying. If anything write some nice little functions to open and close your connections or just copy and paste the same code with slight modifications. Other than that you still gotta do it each time. There is no getting around that.

Added: March/29/2006 at 8:31pm | IP Logged

Hi

I am having problem to add new record to my database. Never face this problem before. At first the error is "Current recordset does not support updating. This may be a limitation of the provider or of the selected locktype". Then I try to reset permission for my working folder at IIS(which I think I had never need to reset before but it works at different machine).

When I try to add again there is another error "operation is not allowed in this context".

I am really stuck now, not sure what's wrong with my code. Please help asap. Thanks.

This is my code.

<link href="css/phn.css" rel="stylesheet" type="text/css">
<%

dim conn
dim rs, rs2, mysql, sql2
dim sTagNo, result, sInvDate, sUserId
dim errMsg, testadd

sTagNo = request.Form("txtTagNo")
sInvDate = request.Form("optInvDay") & "/" & request.Form ("optInvMonth") & "/" & request.Form("optInvYear")
//sUserId = session("sUserId")
sUserId = request.form("txtUserId")

set conn = server.CreateObject("ADODB.Connection")
conn.open Application("ConnectionString")

'    Check for Tag No in db
'    If already exist then prompt error msg
'    exit  
'    else
'    Proceed to save in db 

 '** 1. To check for any duplicate Tag No
 
 mysql = "Select * from Entries where TagNo='"& sTagNo &"'"&";"
 'set rs=conn.Execute(mysql)
  set rs = Server.CreateObject("ADODB.Recordset")
  rs.open mysql,conn,1,3 'adopenstatic,adlockoptimistic
 
  if rs.EOF then
  'Add data to database
  
  On Error Resume Next
  testadd = rs.Supports(adAddNew)
         response.Write(testadd)
  
                 rs.AddNew
  rs.Fields("TagNo") = request.Form("txtTagNo")
  rs.Fields("ItemNo") = request.Form("optItemNo")
  rs.Fields("LotNo") = Ucase(request.Form("txtLotNo"))
  rs.Fields("Quantity") = int(request.Form("txtQty"))
  rs.Fields("Location") = Ucase(request.Form("txtLocation")
  rs.Fields("InvDate") = FormatDateTime(sInvDate,"dd/mm/yyyy")
  rs.Fields("EntryDate") = FormatDateTime(Now(),"dd/mm/yyyy")
  rs.Fields("EntryTime") = FormatDateTime(Now(),4)
  rs.Fields("UserID") = ("testing")  'sUserId
  rs.Update
  Call CloseAll
           
  if Err then
   errMsg = "Error Description : " & Err.Description
  else
   errMsg = "Data is successfully saved!"
  end if
 else
  result = rs("TagNo")
  'errMsgs = result & " is already exist. Can not save duplicate Tag No!"
  errMsg = result & " is already exist. Can not save duplicate Tag No!"
  Call CloseAll
 end if
 
Sub CloseAll
 rs.close
 set rs=nothing
 conn.close
 set conn=nothing
end sub

%>
<script language="javascript">
parent.status('<%=errMsg%>')
</script>

Added: March/29/2006 at 10:23pm | IP Logged

Please really need your help. I got this error 'Database or object is read only' when try to add new record. Try everyhing but still got error to update db. Any suggestion and i need to get this done quick.pls help

from global.asa

ub Application_OnStart
  Dim Conn, ConnStr
  Application("ConnectionString") = "DSN=StocktakeDB;"
 
End sub

from mycode.asp

set conn = server.CreateObject("ADODB.Connection")
conn.open Application("ConnectionString")

''** 1. To check for any duplicate Tag No
 
 mysql = "Select * from Entries where TagNo='"& sTagNo &"'"&";"
  set rs = Server.CreateObject("ADODB.Recordset")
   rs.open mysql,conn,1,3
 
  if rs.EOF then
  'Add data to database
  
  On Error Resume Next
         testadd = rs.Supports(adAddNew)
         response.Write(testadd)
  
         rs.Add New
         rs.Fields("TagNo") = "123456"

          rs.Fields("UserID") = ("testing")'sUserId
            rs.Update

rs.close
 set rs=nothing
 conn.close
 set conn=nothing

Added: March/30/2006 at 12:32pm | IP Logged

I suggest you ask your questions in the microsoft asp newgroups..
http://www.powerasp.com/content/links/default.asp

I simply do not have the time or patience to help you.