This question is a very valid question for most people beginning and experienced with Oracle from within ASP. Because of the variety of things that are needed, there can be many answers to this question. Here are the basics:

• SQL*Net needs to be loaded on the machine where IIS resides.
• A "System" DSN (Data Source Name) should be configured on the machine where IIS resides. Or you may use a "DSNless" connection. A connection of this type still requires you have the correct ODBC driver. See below.
• Your DSN connection consists of an ODBC driver. Here are your most utilized choices:
• Microsoft ODBC Driver for Oracle 2.00.00.6325 (Microsoft supplied driver)
• Microsoft ODBC for Oracle 2.573.3513.00 (Microsoft supplied driver)
• Oracle ODBC Driver 7.x or 8.x (Oracle supplied driver)

Most people prefer to use the Microsoft ODBC Driver for Oracle 2.0 for the reason that is seems to be more stable than the 2.5 driver from MS.

The Oracle ODBC Driver has a variety of incompatibility problems with ASP and it is not recommended to use this driver.

Make sure you’ve tested the DSN before you implement. This will prevent you from having to deal with connectivity problems at this level while developing.

• The TNSNAMES.ORA file needs to have been configured on the machine where IIS resides. This file is located in you ORANT\NETWORK (or NET80)\ADMIN\ directory. An Example configuration is provided below:

orcl.world = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = THE IP ADDRESS OF YOUR HOST OR DNS NAME)
          (Port = 1521)
        )
        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = THE IP ADDRESS OF YOUR HOST OR DNS NAME)
          (Port = 1526)
        )
    )
    (CONNECT_DATA = (SID = ORCL)  
    )
  )

Some things to note are the HOST, CONNECT_DATA, and PORT parameters.

Set AUM = Server.CreateObject("ADODB.Connection")

To configure the ODBC driver, you will first need to :

This problem seems to come up quite often. Rest assured, it is not just common to Oracle. Most of the time this problem is because the developer has not tested the SQL statement in question via a SQL tool (i.e. SQL Plus for Oracle or ISQL in SQL Server). Some of the steps you should take when debugging are:

• ALWAYS run your command through SQL*PLUS if your statement fails. This way you eliminate the fact that it could be a syntax problem with Oracle.
• If you have successfully tested the statement against the database outside of ASP, make sure your statement is syntactically correct with ADO.
• Make sure all concatenated statements have the correct spaces in them.

sqlChk = "SELECT COUNT(NAME)"

sqlChk = sqlChk & " FROM MYAPP_USERS"

sqlChk = sqlChk & " WHERE NAME = UPPER('" & Request.Form("txtUserID") & "')"

* Note: Although eliminating the number of lines for interpretation can optimize your code, some prefer readability to the slight performance increase.

If you were to look closely, you would notice that the second and third line have a space between the start of the clause and the ". Another method to insure proper spaces between concatenated statements would be to put the space at the end of each line.

Check the following:

• Insure that you can connect via SQL*PLUS. This will confirm that your TNSNAMES.ORA file is configured properly. If not, check the following:
• Verify the instance is running (i.e. Oracle is up).
• Make sure you have a TNSNAMES.ORA entry. 
• Check to see that the "TNS listener" service is running on the machine where Oracle resides.
• Check to make sure that you have the correct ODBC driver.
• If you can connect via SQL*PLUS, check the following:
• Check to make sure that you have the correct ODBC driver.