Visualized BASIC 6.0
Two connections to SQL Server
Abstract: This article introduces two methods for connecting Visual Basic 6.0 applications to Microsoft SQL Server through ado, that is, source database connection and passive database connection. The internal mechanisms of the two connection methods are described respectively, compare the advantages and disadvantages of the two methods with instances. Key words: Ado; ODBC; DSN; database VB connecting to the SQL Server database through ADO can be divided into source database connection and passive database connection, identify whether they are determined by using the DSN (data source name.
1
Passive database connectionIn fact, the "passive Database" connection does not mean that the data source is not required. If there is no data source, the connection is on paper, but you do not need to configure the ODBC Data Source. You can use ADO to connect through code.
1.1 ADO (ActiveX Data Object)In Visual Basic6.0, there are three data access interfaces: ActiveX Data Object (ADO), remote data object (rdo), and data access object (DAO ). Each of these three interfaces represents different stages of development of the technology. The latest one is ADO, Which is simpler and more flexible than rdo and Dao. For new projects, ADO should be used as the data access interface. ADO is an interface developed by Microsoft database applications and a high-level database access technology built on top of ole db. It encapsulates the interfaces provided by ole db. Compared with the ole db provider, the ADO interface allows programmers to perform data interaction at a higher level. ADO technology can be applied not only to relational databases, but also to non-relational databases. You can use a unified method to access different file systems, greatly simplifying programming and increasing program portability.
1.2
Establish a passive database connectionBefore connecting to the database, select Microsoft ActiveX Data Objects 6.0 library and Microsoft ActiveX Data Objects recordset 2.6 library from "project"-"Reference" in the Visual Basic 2.6 menu. Use the connectionstring attribute in the connection (connection) as the main element of the ADO object model to connect. The connectionstring can be a read/write string type. Specify a connection string to tell ADO how to connect to the database. Example: This section describes the passive database connection by taking the establishment of a login system as an example. Create a student database in Microsoft SQL Server 2000, and create a login_user (name, password) data table in the student database. The name of the Microsoft SQL Server server is data_server. The Code is as follows: Option explicitpublic loginsucceeded as Boolean 'reference Microsoft ActiveX Data Objects 2.6 library' defines the connection object and record set dim conn as ADODB. connectiondim RS as ADODB. recordset 'Response to the cancel button click event private sub login cancel_click () 'set global variable to false' login loginsucceeded = falseme is not prompted. hideend sub 'click the logon button to respond to the event private sub cmdok_click () set conn = new ADODB. connection set rs = new ADODB. recordset 'establish a passive database connection Conn. connectionstring = "driver = {SQL Server}; server = data_server; uid = sa; Pwd =; database = student "Conn. connectiontimeout = 50conn. opendim STR as string 'Connection object set Rs. activeconnection = conn' sets the cursor type Rs. cursortype = adopendynamicif trim (txtusername. TEXT = "") thenmsgbox "user name cannot be blank. Please enter the user name again! ", Vbokonly + vbexclamation," warning "txtusername. setfocuselse 'sets the query string STR = "select * From login_user where name ='" & trim (txtusername. text) & "'" Rs. open strif Rs. EOF = true thenmsgbox "this user does not exist. Please enter the user name again! ", Vbokonly + vbexclamation," warning "txtusername. setfocuselse 'login successful, connection main window if (TRIM (txtpassword. text) = trim (RS. fields ("password") thenloginsucceeded = trueme. hidemain. showelsemsgbox "Incorrect password. Please re-enter the password! "," Warning "txtpassword. setfocussendkeys "{home} + {end}" End ifend sub passive database connection eliminates the need to configure the ODBC environment, saving the trouble of manually setting DSN, the software written in this method has wide adaptability and meets the requirements of professional software.
2. source database connectionThe primary task of connecting to the source database is to register the data source name (DSN) and register the data source by configuring the ODBC environment before programming the database, connect, access, and operate the data source.
2.1
ODBCODBC (Open Database Connectivity) is an open database system application interface specification developed by Microsoft. It can be used to access multiple database systems at the same time in applications. It can be seen that the biggest advantage of ODBC is that it can process all databases in a unified manner.
2.2 DSN (
Data Source NameODBC data sources are divided into the following three types: (1) User DSN: only users who create data sources can use their own data sources, and can only be used on the current computer. (2) system DSN: a dsn that can be used by any user or program that uses your computer. (3) file DSN: in addition to the system DSN function, it can be used by other users on other computers.
2.3
Create an ODBC Data Source(1) Start the control panel. (2) double-click the ODBC icon to start the ODBC data source. (3) switch to the "user DSN" tab and click "add ". Select sqlserver as the data source driver. (4) Name the data source. Follow the prompts to create an ODBC data source.
2.4
Create an active database connectionCreating a data source is the first step for connecting to the source database. The following also uses the connection (connection), the main element of the ADO object model, to connect to the source database. Connectionstring is the property name of the connection object. It provides the specific information required for the data provider or service provider to open a connection to the data source, including database, DSN, uid, and so on. (1) Database: A string expression that specifies the name of the database on the server. (2) uid: A string expression that specifies the user ID (User Account Name) for the ODBC data source ). (3) PWD: string expression, which specifies the user password for the ODBC data source. (4) DSN: A string expression that specifies the name of the connected ODBC data source. Use the configured ODBC data source for connection. Example: This section describes how to establish a query system. Create an ODBC data source named stud and connect to the database student through ADO. The Code is as follows: dim conn as ADODB. connectiondim RS as ADODB. recordsetprivate sub form_load () set conn = new ADODB. connectionset rs = new ADODB. recordsetconn. connectionstring = "DSN = stud; uid = sa; Pwd =; database = student" Conn. opendim STR as stringset Rs. activeconnection = conn Rs. cursortype = adopendynamic STR = "select * From login_user" Rs. open STR txtusername. TEXT = Rs. fields ("Name") txtpassword. TEXT = Rs. fields ("password") end sub requires you to configure the ODBC environment and register the data source before connecting to the source database. This method has some limitations. After the program is finally completed and distributed to the user, you also need to configure ODBC for the user. Because the parameters are different, you cannot grasp the configuration.
3
ConclusionAdo encapsulates and implements all the functions of Microsoft's powerful data access interface ole db, which features versatility and high efficiency. Using ADO to connect to a passive database saves the trouble of manually setting the DSN and makes the ADO object model more flexible.
