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

In the previous blog, we have learned some simple methods to operate SQL Server using VB, as follows:

Using VB to operate SQL Server (I) -- how to connect to a database

Using VB to operate SQL Server (2) -- data input (ADD)

Vb SQL server operations (iii) -- data deletion (deletion)

Today, we continue to improve the knowledge network and learn how to modify data:
In VB, you can modify data not only by executing SQL statements, but also by using data binding controls. Data Binding controls use less code to modify data. In particular, some table-type data binding controls can modify data without code, such as the DataGrid Control.

1. Use Update Statement to modify dataThe update statement is executed using the execute method of the ADO object to modify the data in the data table. The syntax is as follows:
CNN. Execute "update data table name set field 1 = expression 1, Field 2 = expression 2 ...... Where clause"

You can use the WHERE clause in the update statement, and modify the data that meets the conditions. If you use where to specify the conditions, the update statement modifies all the data in the table.

This statement is usually used for batch changes. This method is more convenient than the ADO update method to modify data one by one, however, some table controls in VB can implement batch data modification without code.

2. Table data binding controls modify dataIn VB, you can use the table data binding and modification control to modify data without code. You only need to set some attributes of the table data binding control, such as the DataGrid Control, bind the data to the corresponding data source, and set its allowupdate attribute to true to promptly update the modified data in the table to the database.
This attribute can return or set a value to indicate whether the user can modify the data in the DataGrid Control. Syntax:
DataGrid. allowupdate = Boolean expression

Example: Use the DataGrid Control to modify data

Private sub form_load () 'sets the connection string adodc1.connectionstring = "provider = sqloledb; persist Security info = true; user id = sa; initial catalog = student; Data Source =. "'set the data source adodc1.commandtype = ad1_text partition =" select * From student_info order by student_id "'to bind the data source set datagrid1.datasouce = adodc1' to the DataGrid Control to update the data in the table" maid ".

3. Use the text data binding control to modify data

Unlike the DataGrid Control, When you modify data after binding data to a text control, because the modified data is only written into the cache, You need to submit the modification through the update method of ADO. In addition, ado also provides the cancelupdate method, which is used to cancel the changes made to the data before the update method is used. The code for using the cancelupdate method in the program is as follows: adodc1.recordset. cancelupdate

Example: Use a text control to bind and modify data

'Set the connection string and data source, and bind the data source private sub form_load () adodc1.connectiongstring = "provider = sqloledb; persist Security info = true; user id = sa; initial catalog = student; data Source =. "adodc1.recordsource =" select * From course_info "set text1.datasource = adodc1 text1.datafield =" Course name "End Sub" Submit and save data private sub commad#click () adodc1.recordset. update msgbox "saved successfully! ", Vbokonly," prompt "End Sub" undo data modification private sub command2_click () adodc1.recordset. cancelupdateend sub

Master these basic operations in practice, make the code easier to understand, and make the program more flexible.

To be continued ......