VB Database record query method 4

Source: Internet
Author: User
Tags dbase mdb database table definition
To query database records in VB, it must be determined based on how the database is opened. Taking vb3.0 as an example, there are three types of dataset objects: Table, dynaset, and snapshot. The following four query methods are applicable to databases opened by different object methods above.
1. query using the seek method. This method only applies to table-type data tables opened in the OpenTable mode. Before querying, you must create an index file for the query field. Because an index file has been created, the query speed is very fast, in this way, the query result is to move the pointer to the first record that meets the condition. For example:
Set TB = dB. OpenTable ("Demo ")
TB. Index = "nameindex"
TB. Seek "=", "James"
2. query with the filter attribute. The filter attribute is only available for dynaset and snapshot objects. Therefore, it can only be used for both objects and cannot be used for tables. Filters are used to filter data. As long as we specify a filter condition, we can filter the required records. It should be noted that we need to open the dataset filtered out by the filter attribute to operate on it. For example:
Set dy1 = dB. createdynaset ("Demo ")
Dy1.filter = "name like 'Liu *'"
Set dy2 = dy1.createdynaset ()
3. query using the find method. Find is applicable to dynaset and snapshot objects. There are two methods: findfirst and findnext. One record is queried each time. For example:
Set DY = dB. createdynaset ("Demo ":)
S = "salary> 160 and name like 'Liu *'"
Dy. findfirst s
Dy. findnext s
4. Use SQL to query. SQL is a structured data query language. SQL can only be used for Dynaset and Snapshot dataset objects. This method generates a DataSet object based on SQL conditions. For example:
S = "select * from demo where name like 'Liu *'"
Set dy = db. createDynaset (s)
[Note]: In this article, the db is a database object. The open method is as follows:
For example, set db = OpenDatabase ("C: \ VB \ SAMPLE. MDB ").
About VB Database Programming
Zheng Jianmin, Chengdu
Visual Basic (VB) is a Visual programming environment launched by Microsoft based on the Basic language. It is favored by many computer enthusiasts for its simple and powerful functions.
VB Database programming can be divided into three categories (from easy to difficult) based on their difficulty level );
● Use Database Control items and bind control items
● Programming with database object variables
● Directly call the ODBC2.0API
When using VB for database programming, we usually select one of the three basic methods to design the database application solutions, now we will compare the adaptation scope and advantages and disadvantages of the above three design methods.
1. Use Database Control items and bind control items
1.1 advantages
It is the least encoded among the three methods.
No need to know details about the CDBC2.0API
Allow the use of standard and third-party vendor-developed controls
Simplified error handling
Supports all dynamic set methods and attributes
1.2 disadvantages
You cannot access snapshot objects (snapshop) or table objects (all belong to record set objects)
You cannot access a database set, such as a table definition (TableDefs) field (Fieds), an index (Indexes), or a query definition (QueryDefs)
Only some ODBC2.0 management functions can be accessed
Cannot perform real transaction processing
Limited error diagnosis Functions
1.3 applications
For small and medium-sized database tables (usually less than one thousand records), you can only browse the summary.
The length of the result set corresponding to a basic SQL query is limited (generally, the number of records in the result set is smaller than one hundred, and these records are retrieved from one or two tables with limited length)
There are few data input/output items in the application (usually only one or two tables with limited length are involved, and the number of fields in the table is about 10, and there is no link integrity limit
2. Programming Using database object variables
2.1 Advantages
ODBC2.0 management functions can be accessed in a program
Multiple record set types can be controlled: Dynaset, Snapshop, and Table record set object
Allows you to access stored procedures and query actions.
Database collection objects can be accessed, such as TableDefs, Fields, Indexes, and QueryDefs. They have real transaction processing capabilities, including starting transactions (Begintrans), committing transactions (CommitTrans), and rolling back transactions (Rollback)
2.2 disadvantages
It is more encoded than the Data Control item method.
Only Indirect Error Handling and error recovery are allowed.
No fine-grained control over each database operation
Operations on inabundant Resources in the result set and result set are restricted.
Compared with the method that directly uses the ODBC2.0API function
2.3 Applications
The application needs to dynamically create tables, fields, and indexes during execution.
Applications involve complex transactions that synchronously update several tables (but logically maintain consistency)
Applications use result sets instead of FORMS of Dynaset, such as Snapshots or Tables. This is the key to design.
The application has a very large table with more than 1000 records.
Applications have complex data input/output items that involve many internal related fields and include database integrity or consistency rules.
The application needs to perform some additional operations and query and post-processing of the result set, especially for formatting of high data.
Applications need to use complex ODBC management functions to select, configure, verify, and create various data sources.
The application needs to "display" the basic structure of the database during execution
Applications need to use complex multi-code indexing methods to retrieve or update records.
3. directly call the ODBC2.0API
3.1 advantages
You can directly participate in the development, management, and standardization of result sets.
Provides more control over the result set cursor, and provides more cursor types and execution actions.
Determine the consistency level of ODBC drivers and SQL
Better control of Windows execution scheduling and resource utilization
Similar to other methods, this method may have the best performance.
3.2 disadvantages
A large amount of code is required compared with the other two methods.
The code is complex and requires programmers to have experience in programming API calls.
On the network, there is a lack of security for library error handling DURING Visual Basic runtime. Therefore, errors during code runtime may cause serious consequences.
3.3 applications
If the system environment is a large-scale multi-user environment in Client/Server mode, the application must be able to accurately solve possible system errors and failures.
The application emphasizes resource usage. How to directly control memory and network server resources is the primary consideration.
Applications use ultra-large-scale databases. For example, database tables may contain tens of thousands or hundreds of thousands of records.
Overview of VB Database Access methods
Chen Peng, Nanjing Hehai University
Visual Basic (VB) is a Visual, event-driven Windows application development tool, it is easy to use and powerful in GUI Design, drawing, tabulation, computing, communication and multimedia development, so it is increasingly favored by developers. At the same time, VB also has powerful functions far from Foxpro in database development. VB can read and Access multiple databases, such as Access, Excel, DbaseX, Foxpro, Btrieve, and ODBC, and can use the database engine of VB to create an Access database. Therefore, VB has been widely used in the development and construction of management information system (MIS. Now, I will give a brief introduction to some methods of the VB Application database.
There are usually three ways for VB to access the database: first, Control Data Control access through the database; second, program access through the database object variables provided by VB; and third, access odbc api functions through the ODBC interface. Among the three methods, the first method is the most convenient, flexible, and easy to master. At the same time, it can best reflect the object-oriented features of Visual Basic. Therefore, the Foxpro2.5 database is used as an example, this section describes how to access a database using a Data Control:
1. Add the Data Control in the Form window.
Double-click the left button on the Data Control button of the tool window, and the object appears in the middle of the Form window (the Control name is Data1). Adjust the widget size and position with the mouse. If this Control is not available in the toolbox, open Tools/Custom in the main menu, select the Microsoft Data Control check box in the list, and then add the Control to the toolbox.
Ii. Set the connected database
Click Data1, press F4, open the Properties window, set the Connect attribute to Foxpro2.5, and set DatabaseName to c:/foxprow/student. dbf (assuming the file already exists on the disk ).
3. Add field display and editing controls (Database bundling controls)
Add the DGrid control to the Form window. If not, open the main menu Tools/Custom, select the Apex Data BroundGrid check box in the list, and then add the control to the toolbox. In the Form window, click this control (DGrid1), press F4 to open the Properties window, set Datasource property to Data1, right-click the control DGrid1 in the Form window, and select Retrieve Fields; right-click the control DGrid1, select Edit, and adjust the control and related fields with the mouse. Right-click the control DGrid1 and choose Properties. In the displayed window, select the Colums tag, select Colum1 from the drop-down list, change the Caption attribute to "student ID", select Colum2, and change the Caption attribute to "name ", select Colum3 and 4, change the Caption attribute to "gender" and "professional", and click "OK.
Add a button control to the Form window, set the Caption attribute to "exit", double-click the control (Command1), and write "END" in the code window to save the disk.
In this case, an application with the database read/write and browsing functions is created. Press F5 to run the application and click the Data1 buttons to view the changes in the current record.
On this basis, we can also use the relevant attributes and methods of Database Control controls for more flexible operations and control.
1. Set Data1.Visiable = false' to make the control invisible.
2. Place the buttons Command2, 3, 4, 5, 6, set the Caption attribute of 7 to "APPEND", "first record", "previous", "Next", "Last Record", and "delete", and double-click each button, write the Event code separately:
Sub Command1-Click () 'close the application
End Sub
Sub Command2-Click () 'append record
Data1.Recordset. Addnew
Data1.Recordset. Update
Data1.Recordset. MoveLast
End Sub
Sub Command3-Click () 'Move to first record
Data1.Recordset. MoveFirst
End Sub
Sub Command4-Click () 'Move to the previous record
Data1.Recordset. MovePrevius
If Data1.Recordset. Bof Then
Data1.Recordset. MoveFirst
End If
End sub
Sub Command3-Click () 'Move to tail record
Data1.recordset. movelast
End sub
Sub Command4-Click () 'moves to the next record
Data1.recordset. movenext
If data1.recordset. EOF then
Data1.recordset. movelast
End if
End sub
In addition, the Database Control controls include many attributes and methods such as bookmark and recordcount, allowing you to perform various convenient operations on the database.
It can be seen that the Database Control controls have powerful functions in database operations. In addition, the recordsource attribute of the Database Control control can be assigned a value using an SQL statement to effectively call an SQL statement. This gives play to the powerful function of the SELECT statement and makes the Database Control (Data Control) more powerful) icing on the cake.
Delete database records
As you know, by default, VB deletes a record only as the last deletion flag and does not actually delete it. To delete a record, you can use the following methods provided by VB: begintrans, committrans, and rollback. The begintrans method starts to record database changes, the committrans method confirms database changes, and the rollback method can restore deleted or modified records. They can be nested. Therefore, to restore deleted records, you should use the rollback method after the begintrans method and before using the commitrans method.
Operate Database files without Data Control
As you know, if you use the data control in VB to access the database, some large DLL support is required for program execution. This is a big problem for the distribution of installation disks. So can I access the database without using the Data Control in VB?
In VB, there is only one method to access the database without the Data Control: to open the database file as a binary file. But the biggest difficulty with this method is that you must understand the structure of database files.
For example, Foxbase's DBF File (I don't know the file structure of other databases, you can check it): it consists of a record header and a record. The record header consists of two parts: record header = database description + field description. Descriptions of the FOXBASE database are listed below: Descriptions of the Start address in FOXBASE Database
1 1 FOXBASE database file mark
2-4 3 Last modified Date, 3 bytes: year, month, and day
5-8 4 Total number of file records
9-10 2 record head length
11-12 2 length of each record
13-22 20 not used
Field descriptions are listed below. Each field consists of 32 bytes.
Field description meaning of the number of starting address bytes
1-11 11 field name
12 1 field type (C, N, D, L, etc)
13-16 4 address of this field in the file
17 1 Field Length
18 1 decimal places
19-32 14 not used
With the Field Information Descriptions, you can use the composite variable + binary file to operate the database.
Convert text to access MDB
The text file type is a bridge between common applications and databases in many software applications. You can use the text isam driver and SQL to convert the text file into an access mdb database file. First, create a schema. ini file for the text file. Then, you can use the following code to implement the conversion:
Dim dB as database, TBL as tabledef
Set DB = dbengine. createdatabase (App. Path & "/mymdb. mdb", dblanggeneral, dbversion_0)
Set TBL = dB. createtabledef ("Temp ")
TBL. Connect = "text; database = C:/vbpj/Data"
TBL. sourcetablename = "customer # TXT"
DB. tabledefs. append TBL
DB. Execute "select temp. * into newtable from temp"
DB. tabledefs. Delete TBL. Name
DB. Close
Set TBL = nothing
Set DB = nothing
Lock tables in the database
Put the expression true = false into the validationrule attribute of the table to lock it. Hardlocktable? This function is implemented.
Public mydb as database
Dim Dummy As Integer
Function HardLockTable (ByVal whichAction As String ,? ByVal aTable As String) As Integer
On Error GoTo HardLockTableError
HardLockTable = True
Select Case whichAction
Case "Lock"
MyDB. TableDefs (aTable). ValidationRule = "True = False"
MyDB. TableDefs (aTable). ValidationText =? "This table locked "&? "ValidationRule on" & Now
Case "UnLock"
MyDB. TableDefs (aTable). ValidationRule = ""
MyDB. TableDefs (aTable). ValidationText = ""
Case "TestThenUnLock"
If MyDB. TableDefs (aTable). ValidationRule = "True = False" Then
MyDB. TableDefs (aTable). ValidationRule = ""
MyDB. TableDefs (aTable). ValidationText = ""
End If
End Select
'Optional, see next suggestion
Exit Function
Hardlocktable = false
Msgbox error $ & "error" & "in hardlocktable trying" & "to" & whichaction & "& atable
Resume hardlocktableerrorexit
End Function
Dummy = hardlocktable ("Lock", "testtable ")
Dummy = hardlocktable ("unlock", "testtable ")
Close all data connections
If data controls such as Dao, rdo, or ADO are used in the Code, all enabled recordset, database, and workspace should be disabled when exiting. Although the object can be automatically deregistered, the data connection will not be disconnected immediately, and some memory may not be reallocated by the system.
The following code closes all opened Dao workspace and releases the occupied memory.
Private sub form_unload (cancel as integer)
On Error resume next
Dim ws as workspace
Dim dB as database
Dim RS as recordset
For each ws in workspaces
For each dB in WS. Databases
For each rs in db. recordsets
Rs. Close
Set rs = nothing
DB. Close
Set DB = nothing
WS. Close
Set Ws = nothing
End sub
How do I delete data from the FoxPro database?
If you use VB to operate the FoxPro database, you will often encounter the following problem: You can only perform del on the record, that is, place a delete mark on the record, but not like in the database, use "pack" to delete this record. when the number of records is large, programs made with controls such as DBGrid will encounter errors. what should we do? In fact, you cannot pack DBF Files in VB. You can only set the isam setting deleted to on. This is equivalent to the set deleted on OF FoxPro. If you don't believe it, you can try it.
VB Application collection ---- access to VB External Database
The most striking feature of VB is also the most interesting for programmers. It is the powerful function of accessing multiple databases. In VB, non-MS Access databases are called External databases, such as dBASE, FoxPro, and ODBC. Using Data Control to Access external databases in VB is similar to accessing an Access database. I will not go into details here. Now I will mainly read the method of generating objects to Access external databases. Access to external databases (taking dBASE as an example) is roughly divided into the following steps:
Step 1: Create an Access database and create a new table in the database, that is, an additional table. Set
Each attribute value of an additional table is used to establish a link with the database you want to access. The program code is as follows:
SetDb = OpenDatabase ("MYDB. MDB ")
Td. Connect = "dBASE: DATABASE = c:/DATADIR"
Td. SourceTableName = "au thor"
Td. Name = "dBASEAuthorTable"
Db. tabledefsAppendTd
Db. close
Step 2: Set the INI file path. Access to external databases is used in VB Programs. After the application generates an EXE file, an INI file must be provided. If the INI file cannot be found, the database cannot be accessed and the error message "notfoundin stollabkisams" is displayed. After setting the INI file path, the VB program searches for the INI file with the same name as the application in the WINDOWS subdirectory. Code for setting the INI file path:
SetDataAccessOption1, "Path/Filename"
Step 3: edit the. ini file to set different types of databases. The following is the INI File Code:
PageTimeout = 5
MaxBufferSize = 128
LockRetry = 20
CommitLockPetry = 20
ReadAheadPages = 16
Paradox3.x = C:/VB/pdx110.DLL
FoxPro2.0 = C:/VB/xbs110.DLL
FoxPro2.5 = C:/VB/pdx110.DLL
DBASE = C:/VB/pdx110.DLL
DBASE = C:/VB/pdx110.DLL
Btrieve = C:/VB/btrv110.DLL
PageTimeout = 600
Paradoxusername = Login User
Paradoxnetpath = P:/pdxdb/
Collatingseguence = ASCII
Centry = off
Data = American
Mark = 47
Deleted = on
In this way, you can access the external database you want to access by accessing the new database mydb. MDB and the table "dbaseau thortabk.
When using this method to access external databases, it should be noted that the additional table only establishes a link in the added database, and the actual location of the table still exists in the original database, when the table is deleted from the attached database, the link is deleted, and the actual table is not deleted.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.