VB6.0 SQL server operations (5) -- Data Query

In the previous study, we have learned some basic operations for Operating SQL Server with VB6.0. The original Article links are as follows:

VB6.0 SQL server operations (1) -- how to connect to a database

VB6.0 SQL server operations (2) -- Data Entry

VB6.0 SQL server operations (iii) -- data deletion

VB6.0 SQL server operations (4) -- data modification

Today, let's learn about Data Query:

In VB, You can query data in a database not only by executing SQL statements, but also by using controls or conditions for (such as ADO) data retrieval.

1. Simple Query

You can use the SELECT statement to limit the data source for a simple query. Use keywords and clauses in select statements.

The following describes several common simple queries:

1) Use as to change the column name of the queried data

In most cases, the fields used to create tables in SQL Server are named in English. These field names are inconvenient for general users to display, you can use as in the query to change the field name when it is displayed.

For example, use as to change the column name of the queried data.

Privatesub modify modify_click () adodc. recordsource = "select ID as number, mailbox as email from mail_info" adodc. refreshend sub

 

2) Calculate the query result

In programs written in VB, data in the database must be calculated to obtain the required values.

By using Arithmetic Operators in a query statement and setting names for calculated fields in combination with as, the calculated data can be displayed in the query results.

Example: Calculate the query result

Privatesub cmdacount_click () adodc. recordsource = "select card number, hourly cost of the computer, the time of the Computer (hourly cost of the Computer * The Time of the computer) as charge amount from the student computer fee table" adodc. refreshend sub

 

3) Comparison condition Query

A query statement can contain comparison operations. Common comparison operators include =, <,>, <>, and!>. ,! <,> =, <= ,! =. Using these operators to connect expressions to form a comparison condition, the system will reject the Boolean value returned by the query condition to determine whether the data meets the query condition, only data that meets the conditions will appear in the query result set.

These comparison conditions must be used with the WHERE clause in the query statement, but VB provides the filter attribute to specify the filter condition for the data in the recordset. This attribute can selectively block records in the recordset object based on conditions. The syntax of this attribute is as follows: recordset. Filter = string

For example, filter attributes are used to query comparison conditions.

'Set the connection, data source, and initialization program privatesub form_load () adodc. connectionstring = "provider = sqloledb; Data Source = .; initailcatalog = student; uid = sa; Pwd = 123456 "adodc. recordset = "select stuid as student ID, stunameas name, stuage as age from student_info" set DataGrid. datasource = adodc combo1.additem "student ID" combo1.additem "name" inherit "Age" combo2.additem ">" combo2.additem "<" combo2.additem "> =" combo2.additem "<=" combo2.additem "=" End Su B 'Query Information and error location privatesub contains query_click () on error resume next if combo1.text <> "" andcombo2.text <> "" Then adodc. recordset. filter = adodc. recordset. fields (combo1.listindex ). name & combo2.text & "" & trim (txtquery. text) & "" end if err then msgbox "Please enter the correct information! ", Vbokonly," warning "End sub

 

2. Fuzzy search

Fuzzy query is used to query similar information in VB.

Fuzzy query can be implemented by executing the like statement in the SQL statement. The like statement is used to determine whether the given data information matches the specified pattern. (The mode can contain regular and wildcard characters .) Like can also be used in the filter attribute of ADO.

The following describes how to use the like and wildcard in the filter attribute:

 

3. query Date and Time

To query date and time data, you can use the ADO filter attribute in VB to limit the filtering conditions or execute the query SQL statement.

1) query data of a specified date and time

In SQL statements, you can use operators (>, =, <, like, and so on) to query data of the date or time type. The date and time strings must be written in a certain format, for example
0:00:01 ".

For example, query data of a specified date and time.

Privatesub implements query_click () on error resume next adodc. recordset. filter = "Date of Birth =" '& trim (txtyear. text) & "-" & trim (txtmonth. text) & "-" & trim (txtday. text) & "0:00:00" 'If err thenmsgbox "Please enter the correct information! ", Vbokonly," error "End sub privatesub form_load () adodc. connectonstring = "provider = sqloledb; Data Source = .; initailcatalog = student; uid = sa; Pwd = 123456 "adodc. recordsource = "select stuid as student ID, stunameas name, stuage as age, stuborndate as birth date from student_info" set DataGrid. datasource = adodcend sub

 

2) query data by year, month, or day

When designing database system applications, you often need to query data for a certain year, month, or day. If you use the method of specifying the date and time to query, though this can be done, the process is very troublesome. In this case, year, month, day, and other functions can be used to extract the year, month, day, and other information about the date and time data in the database. Then, the query method based on the specified date and time is used, you can easily query data of a year, month, or day.

For example, query data by year, month, or day

Private sub functions query_click () dim STC as string on error resume next select case combo1.listindex case 0 STC = "year" Case 1 STC = "month" Case 2 STC = "day" End select adodc. recordsource = "select stuid as student ID, stuname as name, stuage as age, stuborndate as birth date from student_info where" & STC & "(stuborndate) = '" & txtquery. text & "'" adodc. refresh if err then msgbox "Please enter the correct information! ", Vbokonly," error "\ end subprivate sub form_load () adodc. connectonstring = "provider = sqloledb; Data Source = .; initail catalog = student; uid = sa; Pwd = 123456 "adodc. recordsource = "select stuid as student ID, stuname as name, stuage as age, stuborndate as birth date from student_info" set DataGrid. datasource = adodc combo1.additem "by year" combo1.additem "by month" combo1.additem "by day" End sub

You can use VB6.0 to perform data query operations on SQL server. If you have any suggestions or suggestions, I hope you will not be able to understand them.