Look at the case study VFP: Modify table structure with SQL command

In looking at the example of VFP: Modify the data table record with SQL statements in this paper, the use of SQL statements to modify the value of the table record, the actual VFP can also be used in SQL language ALTER statement dynamically modify the table structure. While this can be done, I do not think it is possible to dynamically modify the table structure in the program, as it may cause instability and other surprises in the program.

The operation of the table structure in VFP is to increase the field, modify the field, rename the field and delete the field four items, the statement that modifies the table structure in SQL is an ALTER statement, then the format of the ALTER statement corresponding to the four actions can be summarized as follows:

Add field: Alter TALBE table name add field name type (width [, decimal places])

Modify field: ALTER TABLE name ALTER COLUMN field name type (width [, decimal places])

Rename field: ALTER TABLE name rename column field name to new field name

Delete field: ALTER TABLE Name drop column field name

This ALTER statement looks cumbersome, so this article has designed an example to illustrate these operations on the table structure, as shown in the following diagram:

In this case, the form init begins by querying the "number" from the People information table, name, sex, and basic salary four fields and output the query results as "Staff information table 2.dbf", and then set it as the data source of the table, then we modify the table structure of the operation on this table. The Click event code for the four command buttons on the form corresponds to the application of the four format of the ALTER statement, where:

1. Click event for "Add Field" button: Add a field named "line number" in "Person Information table 2", the data type is numeric and the number of decimal places is 0, and then use the Loop statement to append the line number of each record;

2. The "Modify field" button click event: "Base salary" in staff information table 2, the type of the field is originally a currency type, in this event, change it to a numeric type with a decimal digit of zero;

3. Click event of the Rename field button: Change the name of the field in person information table 2 to "name";

4. Click event of the Delete field button: Delete the Number field.

The production process is simple:

I. Create a new form, set its Caption property value to "Get started with programming-modify table structure with SQL Commands", set the AutoCenter property value to. T, and Save as "modify the table structure with SQL commands. Scx". Add four command buttons to the form, which is set to add fields, modify fields, rename fields, and delete fields, and add a table control below the command button, with the default grid1 for the Name property value, caption. The RecordSourceType property value also uses the default "1-alias".

Two. Add event code:

1. Init event code for the form:

Select number, name, sex, basic salary from staff information table into table personnel information table 2
Go top
This.grid1.recordsource= "Staff Information table 2"

2. Click event code for the "Add Field" button:

Thisform.grid1.recordsource= "" Local
s,c As Integer
s=reccount ()
c=thisform.grid1.columncount+1
ALTER Table Personnel Information Table 2 ADD row number N (2,0) for
I=1 to s go
    i
    replace line number with RECNO ()
endfor
go top thisform . Grid1.columncount=c
Thisform.grid1.Columns (thisform.grid1.columncount). Header1.caption= "line number"
thisform.grid1.recordsource= "personnel information table 2"
Thisform.grid1.refresh
this.enabled=. F.

3. Click event code for the Modify field button:

Thisform.grid1.recordsource= ""
ALTER TABLE staff information table 2 ALTER column base salary N (4,0)
Thisform.grid1.recordsource= "Staff Information table 2"
Thisform.grid1.refresh
This.enabled=.f.

4. Click event code for the "Rename field" button:

Thisform.grid1.recordsource= ""
Thisform.grid1.columncount=-1
ALTER TABLE staff information table 2 Rename column name to names
Thisform.grid1.recordsource= "Staff Information table 2"
Thisform.grid1.refresh
This.enabled=.f.

5. Click event code for the Delete field button:

Thisform.grid1.recordsource= ""
Thisform.grid1.columncount=-1
ALTER TABLE staff Information table 2 Drop column number
Thisform.grid1.recordsource= "Staff Information table 2"
Thisform.grid1.refresh
This.enabled=.f.

6. Unload event code for the form:

Close all
Delete file personnel Information Table 2.dbf
if file ("Person Information table 2.") BAK ")
   Delete file personnel Information Table 2. BAK
endif

Three. Run "Modify table structure with sql command. Scx".

This example code is debugged in the win2003+vfp6.0 environment.

See the full set of "rookie also learn VFP" tutorial