VB Database record query method 4

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 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. Statement Public mydb as database Dim Dummy As Integer Function 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 Hardlocktableerrorexit: 'Subflushdbengine 'Optional, see next suggestion Exit Function Hardlocktableerror: Hardlocktable = false Msgbox error $ & "error" & "in hardlocktable trying" & "to" & whichaction & "& atable Resume hardlocktableerrorexit End Function Example 'Lock Dummy = hardlocktable ("Lock", "testtable ") 'Unlock 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 Next DB. Close Set DB = nothing Next WS. Close Set Ws = nothing Next ' 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: DimDbasDatabase DimTdasNewTabledef 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: [Options] SystemDB = c:/MyPATH/SYSTEM. MDA [ISAM] PageTimeout = 5 MaxBufferSize = 128 LockRetry = 20 CommitLockPetry = 20 ReadAheadPages = 16 [InstallableISAMs] 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 [ParadoxISAM] PageTimeout = 600 Paradoxusername = Login User Paradoxnetpath = P:/pdxdb/ Collatingseguence = ASCII [DBASE] 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.