ASP methods for connecting SQL Server 2008 databases through ODBC application tips

Create a database connection file DSN file "Method

Creating an ODBC DSN file

Before you create a database script, you must provide a way to locate, identify, and communicate with the database. The database driver uses data Source Name (DSN) to locate and identify specific ODBC-compliant databases and pass information from the WEB application to the database. Typically, a DSN contains database configuration, user security, and location information, and you can get a table in a Windows NT registry key or a text file.

With ODBC, you can select the type of DSN you want to create: User, System, or file. The user and system DSN are stored in the Windows NT registry. System DSN allows all users to log on to a specific server to access the database, and the user DSN uses the appropriate security credentials to restrict the database to a specific user's connection. File DSNs are used to get tables from text files, provide access to multiple users, and can be easily transferred from one server to another by replicating DSN files. For these reasons, the examples in this topic will use File DSNs.

You can create a DSN-based file by opening Control Panel on the Windows start menu. Double-click the ODBC icon, and then select the file DSNproperty page, clicking Add , selecting the database driver, and then clicking Next . Follow the instructions later to configure the DSN that applies to your database software.

To configure a file DSN for a Microsoft Access database

1. in the " Create a new data source dialog box, select from the list box . Microsoft Access Driver , and then click Next ".
2. Type your DSN file name, and then click Next ".
3. Click the Complete "creates a data source.
4. in the " ODBC Microsoft Access 97 installation program dialog box, click Select ". Select the Microsoft Access database file (*.mdb), and then click OK .

Attention For performance and reliability reasons, we highly recommend that you use the customer - The server database engine configures data that is driven by such a Web application that must meet simultaneous access by more than 10 users. Although ASP can use any ODBC-compliant database, it is designed for use with client-server databases and has undergone rigorous testing, including microsoft® SQL Server, Oracle and so on.

ASP supports shared file databases such as microsoft® Access or Microsoft ® FoxPro ) as a valid data source. Although Some examples in ASP documents use shared file databases, we recommend that you use this type of database engine only for development or limited configuration scenarios. Shared file databases may not be well suited for client - server databases that meet high demand, high-quality Web applications .

Configure SQL Server database file DSN

Note If the database resides on a remote server, contact your server administrator to obtain additional configuration information; The following procedure uses the ODBC default settings for SQL Server, which may not apply to your hardware configuration.

1. in the " Create a new data source dialog box, select from the list box . SQL Server , and then click Next ".
2. Type a name for the DSN file, and then click Next ".
3. Click the Complete "creates a data source.
4. Type the name, login ID, and password of the server that is running the SQL service program.

1. in the " Create a new data source for SQL Server dialog box, in the Server list box, type the name of the server that contains the SQL Server database, and then click Next ".

1. Select how you want to verify your login ID.

1. If you want to select SQL Server Authentication, enter a login ID and password, and then click Next ".
2. in the " Create a new data source for SQL Server dialog box, set the default database, the driver for the stored procedure settings, and the ANSI identity, and then click Next ". (To get more information, click Help .) )
3. in the dialog box (also known as " Create a new data source for SQL Server "), select a character conversion method, and then click Next ". (For more information, click Help .) )
4. in the next dialog box (also named " Create a new data source for SQL Server "), select login Settings.
5. Note Typically, You can only use logs to debug database access issues.
6. in the "ODBC Microsoft SQL Server installation programdialog box, clicktest Data Source". If the DSN is created correctly, theTest Resultsdialog box will indicate that the test completed successfully.
   SQL Server Connection and security information
   If you are developing an ASP database application to connect to a remote SQL Server database, consider the following issues:
   • Connection Scenarios -You can choose TCP/IP socket and named pipe methods to access the remote SQL Server database. When using Named pipes, the database user must be confirmed by Windows NT Before the connection is established, so the only appropriate SQL Server Users who have access to the identity and do not have a Windows NT user account on the computer may be denied access to the named pipe. As an alternative, a connection that uses a TCP/IP socket can connect directly to the database server without having to use an intermediary computer that uses a named pipe. Because you can connect directly to the database server by using a TCP/IP socket connection, with SQL Server confirmation, the user can gain access without having to pass the confirmation of Windows NT.
     Note using TCP/IP sockets when connecting to a remote database can improve performance.
   • Security -If you use SQL Server of the Integrated or mixed security features, and the SQL Server database is located on a remote server, you cannot use Windows NT requests / Response confirmation. That is, you cannot forward a Windows NT request / response ID to a remote computer, only Basic authentication, which is based on the user's user name and password information.
     For more information on this topic, see http://www.microsoft.com/sqlsupport/
     The Microsoft SQL Server Technical Support Home page on.

Configuring Oracle Database File DSN

First, make sure that the Oracle user software is correctly installed on the computer on which you want to create the DSN. For more information, contact your server administrator or consult your database software documentation.

1. in the " Create a new data source dialog box, select from the list box . Microsoft ODBC for Oracle , and then click Next ".

1. Type a name for the DSN file, and then click Next ".

1. Click the Complete "creates a data source.

1. Enter the user name, password, and server name, and then click Determine ".

Attention DSN file with a . DSN Extension, located in \programs\common files\odbc\data The Sources directory.

For more information about creating a DSN file, visit the Microsoft ODBC Web site:http://microsoft.com/odbc/.

====================================================

The first step in accessing database information is to establish a connection with the database source. ADO provides a Connection object that you can use to establish and manage connections between applications and ODBC databases. The Connection object has a variety of properties and methods that you can use to open and close the database connection and issue a query request to update the information.

To establish a database connection, you should first create an instance of the Connection object. For example, the following script creates a Connection object, and then opens the database connection:

<%
 ' Create a Connection object
 Set cn = Server.CreateObject (' ADODB. Connection ")
 ' Open a Connection; the string refers to the DSN
 CN. Open "FILEDSN=MyDatabase.dsn"
%>

Note The DSN string cannot contain spaces, either before or after the equal sign (=).

In this case, the Connection object's Open method references a DSN-based file that contains information about the location and configuration of the database. You can also explicitly reference the provider, data source, user ID, and password without referencing DSN.

Connection process

<%
 Set cn = Server.CreateObject ("ADODB.") Connection ")" Creates a database connection object
 Set rscustomers = Server.CreateObject ("ADODB. Recordset ")" To create a database query object
 CN. Open "Filedsn=sqllink.dsn" opens database
 strSQL = select Username,password from [mydatabase].[ DBO]. [user] where username= ' duguying ' "
 rscustomers.open strSQL, CN  ' runs SQL statements
%>

Processing database return Data

Set username1 = rsCustomers ("username") 'Get the result of the username field
  Set password1 = rsCustomers ("password") 'Get the result of the password field
  Do Until rsCustomers.EOF
  Response.Write username1 & "" & password1 & "<BR>"
  rsCustomers.MoveNext
  Loop 'Get all returned records by loop