Several methods of ODBC even SQL Server database
This article is written in some users who want to know this technology in the 9CBS forum. Because I recently saw that many netizens mentioned this question in the forum, I wrote this article to readers.
SQL Server is an efficient relational database system that is closely integrated with operating systems such as Windows NT / 2000 and Windows 9x. It is also one of several large-scale relational databases in today's market (SQL Server / Oracle / Sybase, etc.). For today's complex B / S server system, SQL Server is a good choice.
First, build a database DB_TEST used to test
Step 1: Start the SQL Server 7.0 database, enter the "SQL Server Enterprise Manager" management interface, right-click [Server Name] Select [New Key] [Database] to enter the "Database Porperties" interface. As shown in Figure 1.
figure 1
Step 2: Enter the database name DB_TEST in "Name" in Figure 1. Tick in the check box in front of "Automatical", even if SQL Server can automatically add the size of the database file as needed. Click OK to complete the creation of the database.
Second, then take the DB_TEST database as an example, how to use the ODBC to connect several ways to the SQL Server database.
ASP scripts can be accessed in three ways: ● Idc (Internet Database Connector) Method ● RDS (Remote Data Service)
1. Internet Database Interface (IDC) IDC is a traditional database query tool that defines and executes the SQL command for the database query and returns a page of a page specified in the specified data format to the browser. The maximum feature of using IDC access to the database is simple, almost no need to program access to the database. However, today I rarely use the IDC mode to connect the database, so I will not describe here.
2. ActiveX Data Object (ADO)
ADO (ActiveX Data Object) is a set of dedicated object sets for optimized access to the database. It provides a complete site database solution for ASP, which functions on the server side, providing homepage content with database information, by executing SQL commands, Let users enter, update, and delete the information of the site database in the browser screen. The main advantages of ADO are ease of use, high speed, occupancy memory and disk space, so it is very suitable for database access technologies as server-side. The ADO mainly includes three objects of Connection, Recordset, and Command. Their main features are as follows: Connection Object: Responsible for opening or connecting to a database file; RecordSet object: Acquisition of the content of the database; Command object: Database Database Decoction Query instruction, and execute SQL Server stored procedures.
There are two ways to connect to the database using the ADO component:
1, directly connect the database method
<%
DIM CONN, STRCONN
Set conn = server.createObjec ("adodb.connection")
StrConn = "driver = {SQL Server}; server = servername;" & _
"UID = Username; PWD = Password;" & _
"Database = DatabaseName" Conn.open Strconn, 1, 1
%>
2. Create a data source (DSN) method
All types of ODBC data sources are all types, that is, user data sources, system data sources, and file data sources. Data Source Name (DSN), according to Microsoft's official documentation, DSN means "application is used to connect to the name of Connection", that is, it is a symbol representing ODBC connection . A DSN is configured, which has already contained projects such as database file names, directory, database drivers, user ID, passwords, etc. Therefore, when establishing a connection, you don't have to consider the database file name, where is it, as long as it gives it DSN in ODBC. The whole process of creating DSN is given below. (Take the Win2000 operating system, set the system DSN as an example)
Double-click Administrative Tools in Control Panel -> Double-click the "ODBC" icon to enter the ODBC Data Source Manager interface. Select the System DSN page to enter the "Create New Data Source" interface, as shown in Figure 2.
Then select the driver as "SQL Server", as shown in Figure 2. Press [Finish] to enter "Create to SQL Server" interface, enter the name "sjy", description, server. As shown in Figure 3.
Press [Next]. Press [Next] by default settings, tick in the check box before "Change the Default Database" option, select your database. As shown in Figure 4.
Press [Next], keep the default, press [Finish], in this way, a data source SJY is established.
Below is the code of connecting the database through the data source, for the security of the database, we write the connection code in the Golbal.asa file and call it on the page.
