Database-Based ASP. net Design, so that many enterprises and individuals transfer the original c/s application system to the B/S mode ,. the many controls provided in the net development environment make database binding and data operations easy, the DataGrid control is the most commonly used one, in order to simultaneously modify data in the data display web page, the DataGrid Control also provides embedded editing and processing button groups and corresponding event responses. This article will discuss the data modification technology in the DataGrid and provide a more effective data editing method. Data example: SQL> select * From mjg_temp
IDNO class name result pass
----------------------------------------
2003060001 Chinese: Chen Guoqiang t
2003060002 mathematics Li xiaoliang zhongt
2003060003 ry Yangmei liangt
2003060004 English Liu Yong poor F
2003060005 physical Liu qingshanliang t
2003060006 chemistry Wu Yong please leave F Display page for data generated by using the DataGrid Control: After you click the "edit" hyperlink corresponding to the "200306003" Record of the candidate code, the page will be changed to the editing page shown in Figure 2: The implementation of the legend above is a common application of the DataGrid, but it has the following shortcomings: 1. Batch full screen modifications cannot be provided. To modify a record, you must press the "edit" button to enter the edit status of the record. After modification, you must also press the corresponding "Update" button to save the modification; 2. After the record enters the modification state, the original content is cleared, which is not conducive to the reference when the modification is made. 3. The modification box defaults to the Textbox Control, making it difficult to judge the validity of the Input Modification value, data check and restriction capabilities are almost impossible. 4. Frequent page refresh and reload operations make page viewers feel "dazzling" and increase server load. Click the "Update" button to save the changes. The page will be refreshed and the new data results will be displayed waiting for the next change. If we sort the exam scores in this example, if the test score originally in the third record is modified, it will appear in another location after the refresh, and many users will feel that the position of the record is changed, when the network (server) is busy again, user modifications will be in frequent waiting; 5. The definition of three events is indispensable. In this example, the response code design is required to edit the button event oneditcommand, update button event onupdatecommand, and cancel button event oncanclecommand. The DataGrid is not automatically completed. How can we overcome these defects? Back to the flexible and stable control interface like the C/S mode? First, we need to understand the working principle of the DataGrid. The Code described on the DataGrid page in the previous example is as follows: <Asp: DataGrid id = "datagrid1" runat = "server" autogeneratecolumns = "false" oneditcommand =……>
......
<Columns>
<Asp: boundcolumn datafield = "IDNO" readonly = "true" headertext = "Candidate code">
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "name" readonly = "true" headertext = "candidate name">
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "class" readonly = "true" headertext = "test course">
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "result" headertext = "Exam Score">
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "pass" headertext = "pass">
</ASP: boundcolumn>
<Asp: editcommandcolumn buttontype = "linkbutton" updatetext = "Update" headertext = "modify" canceltext = "cancel" edittext = "edit">
</ASP: editcommandcolumn>
</Columns>
</ASP: DataGrid> There are a total of five columns for data binding, and the sixth column is the edit button group. The first three columns, including "Candidate code", "candidate name", and "exam subject", are all "readonly" attributes, indicating that the three columns cannot be edited when the DataGrid is being edited, the display style does not change either. The "Exam Score" and "Pass or not" columns 4 and 5 are editable columns. Once the DataGrid is in the editing status, the two columns are changed from the Label display to the preset textbox editing box, allowing users to edit. This principle also leads to the display status and editing status of the DataGrid. Page refreshing during switching between modes is inevitable. to change the status, you must follow the "alternative" path. Due to space limitations, we will not detail the Event code of the previous example. We will introduce the alternative DataGrid editing and modification, which will be transformed in the following aspects: 1. Change the fourth and fifth columns allowed to be edited from the binding column to the template column; 2. Combine the label style of the display status of the template column with the textbox style of the editing status into dropdownlist or checkbox with both data display and modification functions (textbox itself is both display and edit, however, it lacks data restrictions and self-check capabilities), and uses the features of these controls to limit the range of values to be modified; 3. cancels the button group and corresponding events of Column 6 editing, merged into an independent button on the interface. The response of this button is to save the modified data of the current screen. The following steps are detailed. 1. Bind the column to the template column and cancel the button group in column 6.For the interface description code, see: <Asp: DataGrid id = "datagrid1" runat = "server" autogeneratecolumns = "false"> 'after the button group is canceled, the DataGrid itself has no Event Response definition.
......
<Columns> 'start to define Columns
<Asp: boundcolumn datafield = "IDNO" readonly = "true" headertext = "Candidate code">
<Itemstyle horizontalalign = "center"> </itemstyle>
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "name" readonly = "true" headertext = "candidate name">
<Itemstyle horizontalalign = "center"> </itemstyle>
</ASP: boundcolumn>
<Asp: boundcolumn datafield = "class" readonly = "true" headertext = "test course">
<Itemstyle horizontalalign = "center"> </itemstyle>
</ASP: boundcolumn> 'the first three columns are not changed and are still boundcolumn (bound columns)
<Asp: templatecolumn headertext = "Exam Score"> 'column 4 defines the start
<Itemtemplate>
<Asp: Label runat = "server" text = '<% # databinder. eval (container, "dataitem. Result") %>'> </ASP: Label>
</Itemtemplate> 'the fourth column is defined in the display mode, and the label is still used.
<Edititemtemplate>
<Asp: textbox runat = "server" text = '<% # databinder. eval (container, "dataitem. result ") %> '> </ASP: textbox>' when the fourth column is in editing mode, its initial value is the result field of the current record of the data source.
</Edititemtemplate> 'the fourth column editing mode definition still uses textbox
</ASP: templatecolumn> 'the fourth column is changed to templatecolumn (template column ),
<Asp: templatecolumn headertext = "whether to pass"> 'column 5 definition starts
<Itemtemplate>
<Asp: Label runat = "server" text = '<% # databinder. eval (container, "dataitem. Pass") %>'> </ASP: Label>
</Itemtemplate> 'the fifth column is defined in the display mode, and the label is still used.
<Edititemtemplate>
<Asp: textbox runat = "server" text = '<% # databinder. eval (container, "dataitem. pass ") %> '> </ASP: textbox>' when the Fifth Column is in editing mode, its initial value is the pass field of the current record of the data source.
</Edititemtemplate> 'The Fifth Column editing mode definition still uses textbox
</ASP: templatecolumn>
</Columns> the column definition is complete.
</ASP: DataGrid>
Ii. Merge display mode and edit mode After the above adjustment, only the binding column to the template column is converted, and the fourth and fifth columns still have two modes: Display and edit, the following code replaces the fourth column with dropdownlist and the Fifth Column with checkbox: ......
<Asp: templatecolumn headertext = "Exam Score"> 'column 4 defines the start
<Itemtemplate>
<Asp: dropdownlist id = "dropdownlist1" runat = "server" width = "60px" selectedindex = '<% # databinder. eval (container, "dataitem. result ") %> 'the fourth column is defined as the dropdownlist item, and the Object ID is specified as dropdownlist1, which has only five optional values.
<Asp: listitem value = "excellent"> excellent </ASP: listitem>
<Asp: listitem value = "good"> good </ASP: listitem>
<Asp: listitem value = "medium"> medium </ASP: listitem>
<Asp: listitem value = "poor"> poor </ASP: listitem>
<Asp: listitem value = ""> none </ASP: listitem>
</ASP: dropdownlist>
</Itemtemplate>
</ASP: templatecolumn> 'the fourth column does not have the editing mode, and only the display mode exists.
<Asp: templatecolumn headertext = "whether to pass"> 'column 5 definition starts
<Itemtemplate>
<Asp: checkbox id = "checkbox1" runat = "server" text = "passed" Checked = '<% # databinder. eval (container, "dataitem. pass ") %> '> </ASP: checkbox>' The Fifth Column is defined as a checkbox item, and the Object ID is specified as checkbox1. There are only two States available.
</Itemtemplate> 'the fifth column does not have the editing mode. Only the display mode is available.
</ASP: templatecolumn>
</Columns> the column definition is complete.
</ASP: DataGrid> After the fourth column is replaced with dropdownlist, The selectedindex attribute is dynamically bound to the result field of the current record. The former is a numeric value, and the latter is a string value. How do you assign values to the two values? There is also the Fifth Column. How can checked, the Boolean logical value of checkbox, be assigned a string value? In fact, we also need to modify the value of the SQL statement, the program code is as follows: Sub show_data 'the process of displaying data on the browsing interface
Dim SQL as string
Dim mycomm as new oraclecommand () SQL = "select IDNO, class, name, decode (result, 'you', '0', 'liang', '1 ', 'Medium ', '2', 'differential', '3', '4') as result, decode (Pass, 't', 'true', 'false ') as pass from mjg_temp"
Dim myadapter as new oracledataadapter ()
Myadapter. selectcommand = new oraclecommand (SQL, Session ("Database") 'session ("Database") is the database connection object defined and stored as session variables.
Dim mydataset as new dataset ()
Myadapter. Fill (mydataset, "mytable ")
Datagrid1.datasource = mydataset. Tables ("mytable"). defaultview
Datagrid1.databind' binds datagrid1 to the result dataset queried by the SQL statement to generate a display
Session ("Database"). Close ()
End sub Sub page_load (sender as object, e as eventargs) 'page load event Definition
If not ispostback then
Show_data
End if
End sub
Because the Oracle platform is used in the database background. the corresponding Oracle Database Control objects are defined in the program code. The decode function in the SQL statement is also a query function for Oracle, when the value of the result field is 'you', 'liang', 'zhong', or 'bad, convert the result to '0', '1', '2', and '3' respectively, and convert the result to '4' for other values '; when the pass field value is 'T', convert the result to 'true'. Otherwise, convert it to 'false '. Through this conversion, dropdownlist and checkbox can be bound to the correct parameter value. (Other database platforms also have functions similar to decode, which are not described here ). After the above modification, we will see the following ASP. NET running interface: The "Exam Score" and "pass?" items correctly display the initial values like other items. Without the common buttons for editing the DataGrid, they can still be modified at any time without page refresh. The modified data is still modified on the interface, and the scope of the modified value is also limited to the range specified by dropdownlist, there is no need to worry about possible data logic errors, and the interface effect and operability are also significantly improved. 3. Compile the program for saving the modification event After the interface is designed, only the compilation of the Event program code is saved. When writing the code, it will follow the principle below: perform a row-by-row value for the "Exam Score" and "pass?" items in the DataGrid to convert the current selectedindex value of the dropdownlist to the corresponding string value, convert the current checked logical value of the checkbox to the corresponding string value 'T' or 'F', and modify the data table based on the examinee code. The program code is as follows: Sub savebutton_click (sender as object, e as eventargs)
Dim string_tem1 as string 'defines the string variable, used to store the test score string value
Dim string_tem2 as string 'defines string variables, used to store whether string values are passed
Dim item as datagriditem 'defines the datagriditem object, which is used to locate each row in the DataGrid
Dim mycontrol1 as dropdownlist defines the dropdownlist object, which is used to locate the dropdownlist object in each row of the DataGrid.
Dim mycontrol2 as checkbox defines the checkbox object, which is used to locate the checkbox in each row of the DataGrid
Dim mycomm as new oraclecommand ()
Mycomm. Connection = SESSION ("Database ")
Session ("Database"). open ()
For each item in maid this loop processes each row in the DataGrid
Mycontrol1 = item. findcontrol ("dropdownlist1") 'findcontrol is the items method of the row object of the DataGrid.
Select case mycontrol1.selectedindex
Case 0
String_tem1 = "excellent"
Case 1
String_tem1 = "good"
Case 2
String_tem1 = "medium"
Case 3
String_tem1 = "poor"
Case 4
String_tem1 = "NONE"
End select 'case determination is used to convert the current selection sequence number of dropdownlist to the corresponding score string value.
Mycontrol2 = item. findcontrol ("checkbox1 ")
If mycontrol2.checked then string_tem2 = "T" else string_tem2 = "F" 'If you determine whether to convert the current selection of the checkbox to the corresponding string T or F
Mycomm. commandtext = "Update mjg_temp set result = '" + string_tem1 + "', pass = '" + string_tem2 + "'where IDNO ='" + item. cells (0 ). text + "'" 'item. cells (0) is the first column of the current row of the DataGrid, and its text attribute is the displayed value. Here is the candidate code. Save the modification and use it as the record location condition.
Mycomm.exe cutereader ()
Next item
Session ("Database"). Close ()
After show_data is saved and modified, the data is extracted again. The user's browsing interface is reloaded and refreshed. The sub-process is in the previous Code section.
End sub Here we need to explain why the findcontrol function is used: to take the value of a column in a row in the DataGrid, you must first locate the row and specify the itemindex value of the DataGrid. items is the corresponding row. In the code above, itemindex is not specified because all rows will be traversed. The current row is assigned to the pre-defined item object. Item contains multiple columns and obtains the value of a normal column. We can use the cells (I) attribute of the datagriditem object. The parameter I is the column number value, and 0 represents the first column. When the row has special values (in this example, the dropdownlist object of the fourth column and the checkbox object of the Fifth Column), you need to use the findcontrol method of the datagriditem object, find the embedded object of the specified ID in the current row, and then you can reference its own attributes and methods. In this example, the IDs of the two objects are specified as dropdownlist1 and checkbox1 (see the third-stage code ). After this modification, the modification of the webpage element description interface, SQL data source query statement, and event response code is complete. The modifications in the three parts are related to each other, then, you can use the unified "Save changes" button to edit and store data. Development tools: XP + ASP. NET web matrix Database environment: Oracle 9i Running Environment: 2000 SERVER + Microsoft. NET Framework +. NET Framework data provider for Oracle |
