Establish a connection between VB 6.0 and SQL Server

VB + SQL made a login interface, run the error "object variable or with block variable is not set", and then click debugging, it is found that the assignment to the record set is not successful, finally, we found that the database was not connected successfully.

VB connects to the SQL Sever database through ADO and can be divided into source database connection and passive database connection. The difference between them is whether the DSN (data source name) is used.

1.Passive database connection

The so-called passive database connection does not mean that no data source is required. If no data source is available, what is the connection? Only passive database connection does not need to be configured with ODBC data sources.CodeYou can achieve the connection.

The implementation of passive database connection requires that Microsoft ActiveX Data Object 6.0 library and microsoftactivex Data Object recordset 2.6 library be referenced in VB 3.6, and then connected by using the connectionstring attribute of the connection object.

Taking a simple login system as an example, you must first create a database named student in the SQL Server database. The VB Code is as follows:

Option explicitpublic loginsucceeded as booleandim conn as ADODB. connectiondim RS as ADODB. recordsetprivate sub partition cancel_click () loginsucceeded = falseme. hideend subprivate sub cmdok_click () set conn = new ADODB. connectionset rs = new ADODB. recordset 'establish a passive database connection Conn. connectionstring = "driver = {SQL Server}; server = .; uid = sa; Pwd =; database = student "Conn. connectiontimeout = 50conn. opendim STR as stringset Rs. A Ctiveconnection = connrs. cursortype = adopendynamicif trim (txtusername. Text = "") thenmsgbox "user name cannot be blank. Please enter the user name again! ", Vbokonly + vbexclamation," warning "txtusername. setfocuselsestr = "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

Server = .; the parameter is used to access the local server, and can also be written as server = (local); or set the local server name (generally the computer name, which can be viewed on the SQL Server server ). Uid = sa; Pwd =; used to specify the login name and password for secure logon, if you log on with the sa Login name, you can set whether a password is required by right-clicking the security of the SQL Server Object resource manager -- login name -- SA, and then confirming that the PWD value is consistent with the set password. Database = student; used to set the name of the accessed database.

2. source database connection

ODBC data sources are classified into the following three types::

(1) User DSN: only the user who creates the data source can use the data source they have created, and can only be used on the current computer.

(2) system DSN: any user who uses your computer andProgramCan be used.

(3) file DSN: in addition to the system DSN function, it can also be used by other users on other computers.

 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.

 Create an active database connection

Creating 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.

Connect using the configured ODBC Data Source.

Take establishing a logon system as an example. First, create an ODBC data source named stud and connect to the database student through ADO, the VB Code only needs to change the parameters of the connectionstring attribute of the connection object as follows:

Conn. connectionstring = "DSN = Stu; uid = sa; Pwd =; database = student"