First knowledge of VB Database Development Instance 5 (data query)

Design Analysis

 

Generally, there are three methods for first database query.

Method 1: Use query parameters to construct an SQL SELECT statement, and then use the ADO command object or

Recordset

Method 2: Use the adocommand object to create a parameter query.

Method 3: first execute the SELECT statement to obtain database data and save it to the recordset object.

And then execute the find method of the recordset object to find the matching records, or

Sets the filter attribute of the recordset object to filter matching records.

 

Technical Essentials

 

The additem method of the msflexgrid control is used to add a row of data to the control.

Syntax: object. additem (string, index)

The object is the msflexgrid Control name, and the string is the string containing data. If you want to add multiple columns of data in the row at the same time, you can use a tab (vbtab) to separate strings. The position of the newly added row in the control. The index of the first row is 0. If the index is omitted, the newly added row becomes the last row of the control.

You can use the clear method to clear data in the msflexgrid control. For example, msflexgrid1.clear can also use the removeitem method to delete specified rows, for example, deleting 2nd rows.

Msflexgrid1.removeitem (2)

 

Create a parameter query using the command object

Steps:

(1) create a command object and use? As the query parameter in the SELECT statement. For example:

Objcmd. commandtext = "select * from system user where username like? "

(2) create a parameter object using the creatparameter method of the command object and add it to the parameters set of the command object.

For example, create a parameter object named "User Name" and add it to the parameters set of command object objcmd.

Dim parm as new parameter set parm = objcmd. createparameter ("User Name", advarchar, adparaminput, 10) objcmd. Parameters. append parm

 

(3) set the value of the query parameter. For example

Objcmd ("username") = "admin"

(4) execute the execute method of the command object to complete the query

Note: If the default value is not specified when a query parameter is created, you must set the value of the query parameter before executing the execute method. Otherwise, an error occurs.

Design Program Interface

Write code

 

Dim objCn As Connection, objCmd As Command

Private sub into query_click () dim objrs as new recordset 'defines the partial record set object objcmd ("username ") = "%" & txtuser & "%" 'obtain the query parameter objcmd ("Identity") = "%" & txtstatus & "%" set objrs = objcmd. execute () msflexgrid1.cols = objrs. fields. the count is displayed as listing for I = 0 to objrs. fields. count-1 msflexgrid1.textmatrix (0, I) = objrs. fields (I ). name next 'displays the query result dim N as integer n = 0 while not objrs. EOF msflexgrid1.additem (objrs! Username & vbtab & objrs! Password _ & vbtab & objrs! Identity) n = n + 1 objrs. movenext Wend: displays the number of records label4 = "Total" & N & "query results" End Sub ". When the form is loaded, a database connection and command object are created, and a parameter query is created. Private sub form_load () set objcn = new connection 'instantiate the connection object objcn strcn = "provider = Microsoft. jet. oledb.4.0; persist Security info = false; "& _" Data Source = "&" D: \ vb98 \ Data Query \ instance 5.mdb" objcn. connectionstring = strcn 'create database connection objcn. open

'Create the command object objcmd set objcmd = new command set objcmd. activeconnection = objcn with objcmd. commandtext = "select * from system user where username like? "& _" And identity like? ". Commandtype = ad1_text end with 'creates the dim parm as new parameter set parm = objcmd parameter for the command object objcmd. createparameter ("User Name", advarchar, adparaminput, 10) objcmd. parameters. append parm set parm = objcmd. createparameter ("Identity", advarchar, adparaminput, 10) objcmd. parameters. append parm label4 = "" 'clear tag 4 end subprivate sub form_unload (cancel as integer) set objcmd = nothing objcn. close set objcn = nothingend sub

 

Running result