Solved

ASP database connection

Posted on 2002-05-01
7
269 Views
Last Modified: 2012-06-27
I have an ASP/COM site that is setup to connect to a sql7 database on another server, as far as I can tell - the COM object has all the right info. going thru it (server's IP address, databasename, etc..) - but it doesn't seem to connect. Does sql7 need to be on the ASP site's machine. I'm just wondering if maybe the problem is related to support files and not the ASP/COM pages & code.
0
Comment
Question by:tyy8
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 20

Expert Comment

by:jitganguly
Comment Utility
It doesn't have to be in the same server. What error are you getting ?  How are connecting ? DSN/DSN less ? Did you set up everything. Did you install SQL client on web server ?
Here are some connection string examples

 This page contains sample ADO connection strings for ODBC DSN / DSN-Less, OLE DB Providers, Remote Data Services (RDS), MS Remote, and MS DataShape.  
Also included are ADO.NET connection strings for each the .NET Data Providers (MySQL, ODBC, OLE DB, Oracle, and SQL Server).
 
 
 These sample connection strings are compiled by  Carl Prothman, a Microsoft Visual Basic MVP.  If you  have an ADO connection string that is not listed below,  please email it to him and it will add to this page.        

    Table of Contents
 .NET Data Provider Connections  MySQL .NET Native Provider
 ODBC .NET Data Provider
 OLE DB .NET Data Provider
 Oracle .NET Data Provider  
 SQL Server .NET Data Provider
   
 
 ODBC DSN Connections  DSN
 File DSN
 
 
 ODBC DSN-Less Connections     ODBC Driver for Access
 ODBC Driver for AS/400
 ODBC Driver for dBASE
 ODBC Driver for Excel
 ODBC Driver for Informix
 ODBC Driver for MySQL
 ODBC Driver for Oracle  
 ODBC Driver for Paradox
 ODBC Driver for SQL Server
 ODBC Driver for Sybase
 ODBC Driver for Sybase SQL Anywhere
 ODBC Driver for Text
 ODBC Driver for Teradata
 ODBC Driver for Visual FoxPro
 
 
 OLE DB Data Link Connections  Data Link File (UDL)
 
 
 OLE DB Data Provider Connections     OLE DB Provider for Active Directory Service
 OLE DB Provider for AS/400 (from IBM)
 OLE DB Provider for AS/400 and VSAM (from Microsoft)
 OLE DB Provider for Commerce Server
 OLE DB Provider for DB2
 OLE DB Provider for DTS Packages
 OLE DB Provider for Exchange
 OLD DB Provider for Internet Publishing
 OLE DB Provider for Index Server
 OLE DB Provider for Microsoft Jet
 OLE DB Provider for Microsoft Project
 OLE DB Provider for MySQL
 OLE DB Provider for ODBC Databases
 OLE DB Provider for OLAP Services
 OLE DB Provider for Oracle (from Microsoft)
 OLE DB Provider for Oracle (from Oracle)
 OLE DB Provider for Pervasive
 OLE DB Provider for Simple Provider
 OLE DB Provider for SQLBase
 OLE DB Provider for SQL Server
 OLE DB Provider for SQL Server via SQLXMLOLEDB
 OLE DB Provider for Sybase Adaptive Server Anywhere
 OLE DB Provider for Sybase Adaptive Server Enterprise
 OLE DB Provider for UniData and UniVerse
 OLE DB Provider for Visual FoxPro
 
 
 ADO URL Connections    ADO Recordset
 
 
 Remote Data Service (RDS) Connections  RDS Data Control - Connect Property
 RDS Data Control - URL Property
 
 
 MS Remote Provider Connections  MS Remote - Access (Jet)
 MS Remote - SQL Server
 
 
 Data Shape Provider Connections     MS DataShape - SQL Server  
 

 

ODBC DSN Connections
Using an ODBC DSN (Data Source Name) is a two step process.

1) You must first create the DSN via the "ODBC Data Source Administrator"
program found in your computer's Control Panel (or Administrative Tools
menu in Windows 2000). Make sure to create a SYSTEM DSN (not a USER
DSN) when using ASP. You can also create the DSN via Visual Basic code.

2) Then use the following connection string - with your own DSN
name of course.

 ODBC - DSN

oConn.Open "DSN=mySystemDSN;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;

 ODBC - File DSN

oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For more information, see:  About ODBC data sources and  
How to Use File DSNs and DSN-less Connections

Note: The problem with DSN is that Users can (and will) modify or delete them
by mistake, then your program won't work so well. So it's better to use a DSN-Less
or OLE DB Provider connection string - with a Trusted Connection if possible!

 

ODBC DSN-Less Connections
 ODBC Driver for Access

For Standard Security:

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=c:\somepath\mydb.mdb;" & _
           "Uid=admin;" & _
           "Pwd=;"
If you are using a Workgroup (System database):

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=c:\somepath\mydb.mdb;" & _
           "SystemDB=c:\somepath\mydb.mdw;", _
           "myUsername", "myPassword"
If want to open up the MDB exclusively:

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=c:\somepath\mydb.mdb;" & _
           "Exclusive=1;" & _
           "Uid=admin;" & _
           "Pwd=;"
If MDB is located on a Network Share:

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=\\myServer\myShare\myPath\myDb.mdb;" & _
           "Uid=admin;" & _
           "Pwd=;"
If MDB is located on a remote machine:
- Use an ADO URL with a remote ASP web page
- Or use a "MS Remote" or RDS connection string
- Or use an XML Web Service via SOAP Toolkit or .NET
- Or upgrade to SQL Server and use an IP connection string
 
If you don't know the path to the MDB (using ASP)

<%  ' ASP server-side code
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=" & Server.MapPath(".") & "\myDb.mdb;" & _
           "Uid=admin;" & _
           "Pwd=;"
%>
This assumes the MDB is in the same directory where the ASP page is running.
Also make sure this directory has Write permissions for the user account.
 

If you don't know the path to the MDB (using VB)

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=" & App.Path & "\myDb.mdb;" & _
           "Uid=admin;" & _
           "Pwd=;"
This assumes the MDB is in the same directory where the application is running.

For more information, see:  Microsoft Access Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Access Driver, click here

 

 ODBC Driver for AS/400 (from IBM)

oConn.Open "Driver={Client Access ODBC Driver (32-bit)};" & _
           "System=myAS400;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For more information, see:   A Fast Path to AS/400 Client/Server

 

 ODBC Driver for dBASE

oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
           "DriverID=277;" & _
           "Dbq=c:\somepath;"
Then specify the filename in the SQL statement:

oRs.Open "Select * From user.dbf", oConn, , ,adCmdText
Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to
update dBase DBF files. (Q238431).

For more information, see:  dBASE Driver Programming Considerations

To view Microsoft KB articles related to Microsoft dBASE Driver, click here

 

 ODBC Driver for Excel

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=c:\somepath\mySpreadsheet.xls;" & _
           "DefaultDir=c:\somepath;"
For more information, see:  Microsoft Excel Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Excel Driver, click here

 

 ODBC Driver for Informix

If using INFORMIX 3.30 ODBC Driver

oConn.Open "Dsn='';" & _
           "Driver={INFORMIX 3.30 32 BIT};" & _
           "Host=myHostname;" & _
           "Server=myServerName;" & _
           "Service=myServiceName;" & _
           "Protocol=olsoctcp;" & _
           "Database=myDbName;" & _
           "UID=myUsername;" & _
           "PWD=myPassword;" & _

' Or
oConn.Open "Dsn=myDsn;" & _
           "Host=myHostname;" & _
           "Server=myServerName;" & _
           "Service=myServiceName;" & _
           "Protocol=onsoctcp;" & _
           "Database=myDbName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
If using INFORMIX-CLI 2.5 ODBC Driver

oConn.Open "Driver={Informix-CLI 2.5 (32 Bit)};" & _
           "Server=myServerName;" & _
           "Database=myDbName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;" & _

For more information, see: Informix Developer Zone,  Connection to ODBC Data Source,

 

 ODBC Driver for MySQL (via MyODBC)

To connect to a local database

oConn.Open "Driver={mySQL};" & _
           "Server=MyServerName;" & _
           "Option=16834;" & _
           "Database=mydb;"
To connect to a remote database

oConn.Open "Driver={mySQL};" & _
           "Server=db1.database.com;" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=mydb;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For more information, see:  Programs Known to Work with MyODBC

 

 ODBC Driver for Oracle

For the current Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
           "Server=OracleServer.world;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For the older Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
           "ConnectString=OracleServer.world;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For more information, see:  Connection String Format and Attributes

To view Microsoft KB articles related to Microsoft ODBC for Oracle, click here

 

 ODBC Driver for Paradox

oConn.Open "Driver={Microsoft Paradox Driver (*.db)};" & _
           "DriverID=538;" & _
           "Fil=Paradox 5.X;" & _
           "DefaultDir=c:\dbpath\;" & _
           "Dbq=c:\dbpath\;" & _
           "CollatingSequence=ASCII;"
Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE)
to update Paradox ISAM fDBF files. (Q230126).

For more information, see:  Paradox Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Paradox Driver, click here

 

 ODBC Driver for SQL Server

For Standard Security:

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
For Trusted Connection security:

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=;" & _
           "Pwd=;"
' Or
oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Trusted_Connection=yes;"
To Prompt user for username and password

oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "DataBase=myDatabaseName;"
To connect to SQL Server running on the same computer:

oConn.Open "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
To connect to SQL Server running on a remote computer (via an IP address):

oConn.Open "Driver={SQL Server};" & _
           "Server=xxx.xxx.xxx.xxx;" & _
           "Address=xxx.xxx.xxx.xxx,1433;" & _
           "Network=DBMSSOCN;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
   Pipes (Q238949)
 

For more information, see:  SQLDriverConnect (ODBC)

To view Microsoft KB articles related to ODBC Driver for SQL Server, click here

0
 
LVL 20

Accepted Solution

by:
jitganguly earned 50 total points
Comment Utility
Sorry its too messy, you can refer to this page

http://www.able-consulting.com/ADO_Conn.htm#ODBCDriverForSQLServer
0
 

Author Comment

by:tyy8
Comment Utility
It is a DSNless connection - this site already works on another server - so I know that the connection string is fine. The web server doesn't have sql7 on it.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
If you install the SQL Server 7 management console (MMC) only can you then connect to the SQL Server. Is the server using windows authentication or are you using a plain user-account like sa (not recommended).

CJ
0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
Didn't write that properly. Can you connect from another machine to the SQL Server (MMC is just a tool to very).

CJ
0
 
LVL 19

Expert Comment

by:webwoman
Comment Utility
Does the IUSR_machine account from the web server have the right permissions to access the remote SQL server? Are you setting the connection string with a valid username/password combo for that SQL server?

If the remote SQL server doesn't recognize that IUSR_machine account/domain or account/password in the DSN/connection string as an authorized user, it won't connect.
0
 

Author Comment

by:tyy8
Comment Utility
I'm using windows authentication, not the IUSR_machine account.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Reading Date Settings from Server 6 49
Visual Project Bridge error 5 56
Out the count variables 6 47
INNER JOIN ? 8 29
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now