Improvement of "Add" code generated by "Data Form Wizard" in VB.NET
Li Mingliang In the database application development, we know that the main tasks of data operation are: "Browse, Edit, Delete, Add, Add Record", using VB.NET "Data Form Wizard" to help us quickly create Windows windows that implement the above features Body and related code, this automatically generated code is completely practical and available. However, the author found in use, during the "Data Form Wizard" operation, if we choose "single control's single record" display style, if the control binding the field in the data source is not allowed If the NULL value, run the automatically generated form, and there will be an error after "Add".
To avoid this error, the existing solution is: "Add" function does not use the code generated by the Data Form Wizard, but a new form and then uses a non-data binding structure to separately implement the "Add" function separately. The author has not necessary to build a form, and only the code generated by the Data Form Wizard can simultaneously implement "browsing, editing, deletion, increase" function in the same form, as follows Through an example statement (Note: Development Tools for Microsoft Visual Studio .NET 2003, the sample database is "PUBS" in SQL Server 2000; the following code is in vb as an example, but the method is equally applicable to other development languages in VS.NET) .
First, there is a field that is not allowed to bind to non-TextBox controls for NULL values.
As shown in the following figure, it belongs to such a case, and the column field "contract" binds to the column field "contract" next to the "contract" in the form is not allowed to have NULL values. After running the form and "load", click Add "to" Add "record (number 24 record), but click the navigation button (such as the last" >> "), the record position cannot be moved to Add records.
Figure 1 Form and code are automatically generated by the Data Form Wizard (Menu Action: "File / Add New Item", then select "Data Form Wizard"), the name of the data form instance is "demo", data The name of the set is "DS", the data connection is as an example in SQL Server 2000 "Pubs" sample database, select "Authors" table, select "Single Record" display style of "single control", other Data Form Wizard "The default value. The improvement method of this class is to first modify the "Add" button Click Event Processing code as shown below.
Private sub btnadd_click (byval sender as system.Object, byval e as system.eventargs) handles btnadd.click
Try
Me.BindingContext (Objds, "Authors"). EndCurrentedIt ()
Me.BindingContext (Objds, "Authors"). SuspendBINDING () "temporarily suspend data binding
Me.BindingContext (Objds, "Authors"). AddNew () 'Adds new records
'The following two layers first find out the column fields that are not allowed to be NULL in the Authors table, then identify the binding object bound to this field.
'According to the binding control attributes, different initial values cannot be empty in the new record.
DIM I as integer
For i = 0 to objds.authors.columns.count - 1 'Traversing all column fields in the Authors table
'Find out the column fields that are not allowed to be null in the Authors table
If Objds.Tables ("authors"). Columns (i). Algerowdbnull = false thendim j AS integer
For J = 0 to me.bindingContext (Objds, "Authors"). Bindings.count - 1 'Traversing All Binding Objects
'Find binding to binding objects that are not allowed to null fields
If Me.BindingContext (Objds, "Authors"). Bindings (j) .bindingmemberinfo.bindingfield = _
Objds.authors.columns (i) .Columnname THEN
'If the property of the contained control is TEXT
If BindingContext (Objds, "Authors"). Bindings (j) .propertyName () = "text" then
'Newly added records cannot be NULL fields to be assigned to the first value "", the statement can also be replaced with next to the comment statement!
Me.BindingContext (Objds, "Authors"). Current (j) = ""
'BindingContext (Objds, "Authors"). Current (i) = BindingContext (Objds, "Authors"). _
'Bindings (j) .control.text ()
EXIT for 'Exit the current for loop, look for the next column field that is not allowed to be null
Else
'If the properties of the binding control are Checked (other binding properties ..., you should be able to find it yourself.)
If BindingContext (Objds, "Authors"). Bindings (j) .propertyname = "checked" then
'The newly added record cannot be assigned to the beginning value true, which can be used instead of the four lines of next to the four lines!
Me.BindingContext (Objds, "Authors"). Current (j) = TRUE
'DIM TMPOBJ AS Object
'tmpobj = bindingContext (Objds, "Authors"). Bindings (j) .control
'tmpobj = ctype (tmpobj, checkbox)
'Me.BindingContext (Objds, "Authors"). Current (j) = tmpobj.checked
EXIT for 'Exit the current for loop, look for the next column field that is not allowed to be null
END IF
END IF
END IF
NEXT
END IF
NEXT
'If the new increase does not need to use the Row_Changing event to perform numeric verification, you can enable the note line below
'Me.BindingContext (Objds, "Authors"). EndCurrentedIt ()
Me.BindingContext (Objds, "Authors"). ResumeBinding () 'Restore data binding
'Mobile table location to new additive record
Me.BindingContext (Objds, "Authors"). Position = me.bindingContext (Objds, "Authors"). Count - 1catch Endedit as System.Exception
System.windows.Forms.MessageBox.show (Endedit.Message)
Me.BindingContext (Objds, "Authors"). CancelCurrendEdit () 'error, cancel new increase
Me.BindingContext (Objds, "Authors"). ResumeBinding ()
END TRY
Me.objds_positionchangechanged ()
End Sub
The essence of the above BTNAdd.Click event handling code is: temporarily suspend data binding, add a record, and then assign a value of column fields that are not allowed to be null values (if you do not need to use RowChanging events to get new record data inspection It can also be assigned only the NULL value and cannot be bound to the field assignment of the text box TextBox, and finally the data binding is restored.
In addition, the clicked event handler of the four navigation buttons (first, previous, next, last, last) (for simplification, only one "<<" button in the above "<" button is handled The program takes an example, and the other button is inseparable.
Private sub btnnavprev_click (Byval E AS System.EventArgs) Handles btnnavprev.click
TRY 'uses the TRY sentence for error handling (NULL and uniqueness)
Me.BindingContext (Objds, "Authors"). Position = (Me.BindingContext (objDs, "authors"). Position - 1)
Catch exception
MessageBox.show (ex.Message) 'Display error message
EXIT SUB 'If there is any error, you are not allowed to move
END TRY
Me.objds_positionchangechanged ()
End Sub
If you want to use the RowChanging event to perform a data inspection (suggestions), you will also need to increase the following RowChanging event processing code.
Private sub row_changing (byval e as ds.authorsrowchangeeent), BYVAL EVENGEVENT
'Value the new increase
If E.Action = DATAROWAction.Add Then
'For the sake of simplicity, the field AU_ID cannot be "" as an example.
If editau_id.text = "" "
Throw (New Exception ("Data cannot be empty")))
END IF
END IF
'Verify the recorded editing value
'If e.action = dataAROWACTION.Change Then
'Fill in the editing verification code here
'End if
'Other verification, join it on it
End Sub
To start the RowChanging event handling, add the following statement in the appropriate position of the program (I am the statement Objds.merge (ObjDataSetTemp) in the loadDataSet () method). AddHardler Objds.authors.authorsrowChanging, Addressof row_changing
Second, although there is a field that is not allowed to be a null value, it can be bound to the TextBox class control.
For convenience, we use the "Data Form Wizard" to create a new form (Note: New form can be built in the same project of the previous form, the name of the data form is "demo2", the new data set name For "DS2"), the data form is a "EMPLOYEE" table bound to the "PUBS" database. As shown in Figure 2, all fields that are not allowed to be NULL can be bound to the TextBox class control.
After running the form, you can add a record (record 44 record) after "loading", but if you do not enter any value after the record is added, click any of the navigation buttons (such as the previous "<"), An error occurred as shown in Figure 3.
This type of error is lighter, it can use the first case solution, of course, if you are too much, you can also use the following simpler method: in the "Data Form Wizard" automatically generated, only For the four navigation buttons (first, previous, next, the next, the last one) Click the event handler code for appropriate modification (note: or modify the code modification method of the four navigation buttons in the first case) Other code does not increase, everything is 0k, try again, you will see the picture-friendly interface, prompt you to input the column fields that do not allow null values, and then change the record position.
Third, summary
When a field that is not a null value is bound to the data form, in order to avoid new recording errors, the above author introduces you can find a solution in the code, and we can also find a solution in the data set: " In the architecture of the data set, you can set a default value for column fields that do not allow NULL values. "Of course, which solution is to be applied or if you select.
The first case is given the main solution, please refer to the full code below the end.
Public Class Demo
Inherits System.Windows.Forms.form
'In order to simplify, "the code" generated by Windows Form Designer "is removed, remember to follow the following requirements when the" Data Form Wizard "is executed.
The name of the 'data form instance is "demo", the name of the data set is "DS", and the data connection is in SQL Server 2000.
'The "PUBS" sample database is an example, select the "Authors" table, "single record" display style, the default values used in other.
Private sub btncel_click (Byval e as system.Object, byval e as system.eventargs) Handles btncel.click
Me.BindingContext (Objds, "Authors"). CancelCurrendEdit ()
Me.objds_positionchangechanged ()
End Sub
Private sub btndelete_click (Byval E AS System.EventArgs) Handles Btndelete.Click
IF (Me.BindingContext (Objds, "Authors"). Count> 0) Thenme.BindingContext (Objds, "Authors"). Removeat (Me.BindingContext (Objds, "Authors"). Position)
Me.objds_positionchangechanged ()
END IF
End Sub
Private sub btnadd_click (byval sender as system.Object, byval e as system.eventargs) handles btnadd.click
Try
Me.BindingContext (Objds, "Authors"). EndCurrentedIt ()
Me.BindingContext (Objds, "Authors"). SuspendBinding ()
Me.BindingContext (Objds, "Authors"). AddNew ()
DIM I as integer
For i = 0 to objds.authors.columns.count - 1
If Objds.Tables ("authors"). Columns (i). Albnull = false the
DIM J AS INTEGER
For J = 0 to Me.BindingContext (Objds, "Authors"). Bindings.count - 1
If Me.BindingContext (Objds, "Authors"). Bindings (j) .bindingmemberinfo.bindingfield = _
Objds.authors.columns (i) .Columnname THEN
If BindingContext (Objds, "Authors"). Bindings (j) .propertyName () = "text" then
Me.BindingContext (Objds, "Authors"). Current (j) = ""
'BindingContext (Objds, "Authors"). Current (i) = BindingContext (Objds, "Authors"). _
'Bindings (j) .control.text ()
EXIT for
Else
If BindingContext (Objds, "Authors"). Bindings (j) .propertyname = "checked" then
Me.BindingContext (Objds, "Authors"). Current (j) = TRUE
'DIM TMPOBJ AS Object
'tmpobj = bindingContext (Objds, "Authors"). Bindings (i) .control
'tmpobj = ctype (tmpobj, checkbox)
'Me.BindingContext (Objds, "Authors"). Current (i) = tmpobj.checked
EXIT for
END IF
END IF
END IF
NEXT
END IF
NEXT
Me.BindingContext (Objds, "Authors"). ResumeBinding () Me.BindingContext (Objds, "Authors"). Position = me.bindingContext (Objds, "Authors"). Count - 1
Catch Endedit as system.exception
System.windows.Forms.MessageBox.show (Endedit.Message)
Me.BindingContext (Objds, "Authors"). CancelCurrendEdit ()
Me.BindingContext (Objds, "Authors"). ResumeBinding ()
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnupdate_click (Byval E AS System.EventArgs) Handles btnupdate.click
Try
'Try updating the data source.
Me.UpdatedataSet ()
Catch EUPDATE AS System.Exception
'Add an error handling code here.
'Display error message (if any).
Objds.rejectchanges () 'I added the statement
System.windows.Forms.MessageBox.show (EUPDATE.MESSAGE)
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnload_click (byval sender as system.object, byval e as system.eventargs) Handles btnload.click
Try
'Try load the data set.
Me.LoadDataSet ()
Catch eload as system.exception
'Add an error handling code here.
'Display error message (if any).
System.windows.Forms.MessageBox.show (Eload.Message)
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnnavfirst_click (Byval e as system.EventArgs) Handles btnnavfirst.click
Try
Me.BindingContext (Objds, "Authors"). Position = 0
Catch exception
Messagebox.show (ex.Message)
EXIT SUB
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnlast_click (Byval e as system.EventArgs) Handles btnlast.click
'Me.BindingContext (Objds, "Authors"). Position = (me.objds.tables ("authors"). Rows.count - 1)
Try
'Note the next statement, I have made modifications.
Me.BindingContext (Objds, "Authors"). Position = Me.BindingContext (Objds, "Authors"). Count - 1catch EX AS Exception
Messagebox.show (ex.Message)
EXIT SUB
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnnavprev_click (Byval E AS System.EventArgs) Handles btnnavprev.click
TRY 'uses the TRY statement for error check (NULL and uniqueness)
Me.BindingContext (Objds, "Authors"). Position = (Me.BindingContext (objDs, "authors"). Position - 1)
Catch exception
Messagebox.show (ex.Message)
EXIT SUB 'If there is any error, you are not allowed to move
END TRY
Me.objds_positionchangechanged ()
End Sub
Private sub btnnavnext_click (Byval e as system.Event, byval e as system.eventargs) Handles btnnavnext.click
'If BindingContext (Objds, "Authors"). Position = BindingContext (Objds, "Authors"). Count - 1 TEN'
'MessageBox.show ("data to top!")
'Exit Sub
'End if
Try
Me.BindingContext (Objds, "Authors"). Position = (Me.BindingContext (Objds, "Authors"). Position 1)
Catch exception
Messagebox.show (ex.Message)
EXIT SUB
END TRY
Me.objds_positionchangechanged ()
End Sub
Private subjds_positionchanged ()
Me.lblnavlocation.text = ((me.bindingcontext (objds, "authors"). Position 1) .tostring "") _
Me.BindingContext (Objds, "Authors"). Count.tostring)
End Sub
Private sub btnceLall_Click (Byval e as system.Object, byval e as system.eventargs) Handles BtnceLallLall.click
Me.objds.rejectchanges ()
Me.objds_positionchangechanged () 'I added statement
End Sub
Public Sub UpdatedataSet ()
'Creating a new dataset to save changes to the master data set.
Dim objDataSetChanges as DS = New DS
'Stop current editing.
Me.BindingContext (Objds, "Authors"). EndCurrentedIt () Gets the changes made to the main data set.
ObjdatasetChanges = CType (Objds.getchanges, DS)
'Check if any changes have been made.
IF (NOT (ObjDataSetchange) IS Nothing) THEN
Try
'Need to make some changes, so try to call Update method
'And pass the data set and any parameters to update the data source.
Me.UpdatedataSource (ObjDataSetChanges)
Objds.merge (ObjDataSetChanges)
Objds.acceptchanges ()
Catch EUPDATE AS System.Exception
'Add an error handling code here.
Throw EUPDATE
END TRY
'Add code to check if there is any possible data in the data set.
'Push the error in the wrong object error.
END IF
End Sub
Public Sub loadingDataSet ()
'Create a new data set to save the record returned from the FillDataSet call.
'Use a temporary data set because of the existing data set
'Need to resize data binding.
DIM ObjDataSetTemp as DS
ObjdatasetTemp = New DS
Try
'Try to fill in the closing data set.
Me.FillDataSet (ObjDataSetTemp)
Catch EfillDataSet As System.Exception
'Add an error handling code here.
Throw EfillDataSet
END TRY
Try
'Clearing old records in the data set.
Objds.clear ()
'Combing the record into the main data set.
Objds.merge (ObjDataSetTemp)
'I am here to join the RowChanging event
AddHardler Objds.authors.authorsrowChanging, Addressof row_changing
Catch eloadMerge as system.exception
'Add an error handling code here.
Throw EloadMerge
END TRY
End Sub
Public Sub UpdatedataSource (Byval Changedrows AS DS)
Try
'When there is a hang change, you only need to update the data source.
IF (NOT (CHANGEDROWS) IS NOTHING
'Open the connection.
Me.oledbConnection1.Open ()
'Try updating the data source.
OLEDBDATAADAPTER1.UPDATE (CHANGEDROWS)
END IF
Catch updateException as system.exception
'Add an error handling code here.
Throw UpdateException
Finally
'Whether it causes an exception to turn off the connection.
Me.oledbConnection1.close ()
END TRY
End Sub
Public Sub FillDataSet (ByVal Dataset As DS)
'Close the constraint check before filling the data set.
'This allows the adapter to fill the data set without considering
'Dependency between tables.
Dataset.enforceconstraints = false
Try
'Open the connection.
Me.oledbConnection1.Open ()
'Try to fill the data set via OLEDBDataAdapter1.
Me.oledBDataAdapter1.Fill (Dataset) Catch Fillexception As System.Exception
'Add an error handling code here.
Throw Fillexception
Finally
'Re-open constraint check.
Dataset.enforceconstraints = TRUE
'Whether it causes an exception to turn off the connection.
Me.oledbConnection1.close ()
END TRY
End Sub
Private sub row_changing (byval e as ds.authorsrowchangeeent)
'Value the new increase
If E.Action = DATAROWAction.Add Then
'For the sake of simplicity, the field AU_ID cannot be "" as an example.
If editau_id.text = "" "
Throw (New Exception ("Data cannot be empty")))
END IF
END IF
'Verify the recorded editing value
'If e.action = dataAROWACTION.Change Then
'Fill in the editing verification code here
'End if
'Other verification, join it on it
End Sub
END CLASS
The above code is commissioned in the "PUBS" sample database in Microsoft Visual Studio .NET 2003 SQL Server 2000.
