Vb. Improvements to the add code generated by the Data Form Wizard in net

Source: Internet
Author: User
Tags exit error handling integer numeric join tostring visual studio
Data
In database application development, we know that the primary task of data manipulation is to "Browse, edit, delete, add records," and use the VB.net Data Form Wizard to help us quickly create Windows Forms and related code to implement the above features. This automatically generated code is fully functional and useful. However, the author found in the use of the "Data Form Wizard" in the course of operation, if we select a single record for individual control display style, if the field in the data source to which the control is bound is not allowed to be null, the automatically generated form is run and an error occurs when you click Add.

To avoid this error, the existing workaround is that the Add feature does not use the code generated by the Data Form Wizard, but instead creates a new form and then uses a non-data binding structure to implement the Add function separately. The author discovers that there is no need to build a new form at all, only by making appropriate improvements to the code generated by the Data Form Wizard, you can simultaneously implement the "Browse, edit, delete, add" feature in the same form, as illustrated by an example (note: The development tool is Microsoft Visual Studio.) NET 2003, the sample database is "pubs" in SQL Server 2000; The following code, which takes VB as an example, also applies to other development languages in Vs.net.

One, a field that is not allowed to be null values is bound to a non-TextBox class control

As shown in the following figure, this is the case where null values are not allowed for the column field "contract" that is bound to the check box control next to "contract" in the form. After you run the form and load, click Add to make the new record (the record numbered 24), but clicking the navigation button (such as the Last ">>") will not move the record location to the new record.



Figure one 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 ", the DataSet name is" DS ", and the data connection is" pubs "in SQL Server 2000 Example database, select the Authors table, select Single records for individual controls display style, and others take the default value of the Data Form Wizard. A better way to do this is to first modify the click event handling code for the Add button, 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 () temporary suspend data binding

Me.BindingContext (OBJDS, "authors"). AddNew () ' Add new record



' The following two-tier loop first finds the column fields in the authors table that are not allowed to be null, and then finds the binding object that is bound to the field.

' Assigns different initial values to fields that cannot be empty in new records, depending on the control properties that are bound to



Dim I as Integer

For i = 0 to objds.authors.columns.count-1 ' traverses all column fields in the authors table



' Find a column field in the authors table that is not allowed to be null

If objds.tables ("authors"). Columns (i). AllowDBNull = False Then

Dim J as Integer

For j = 0 to Me.BindingContext (OBJDS, "authors"). Bindings.count-1 ' Traverse all binding objects



' Find a binding object bound to a null field that is not allowed

If Me.BindingContext (OBJDS, "authors"). Bindings (j). Bindingmemberinfo.bindingfield = _

ObjDS.authors.Columns (i). ColumnName Then



' If the property of the control being bound to is text

If BindingContext (OBJDS, "authors"). Bindings (j). PropertyName () = "Text" Then



' Add an initial value ' to a field that cannot be null in the newly added record, which can also be replaced with a comment statement next to it!

Me.BindingContext (OBJDS, "authors"). Current (j) = ""

' BindingContext (OBJDS, "authors"). Current (i) = BindingContext (objDS, "authors"). _

' Bindings (j). Control.text ()



Exit for ' exits the current for loop, looking for the next column field that is not allowed to be null

Else



' If the property of the control being bound to is checked (other binding properties ..., you should be able to imitate it yourself.)

If BindingContext (OBJDS, "authors"). Bindings (j). PropertyName = "Checked" Then



' The field in the newly added record cannot be null to assign the initial value true, which can be replaced by a 4-line comment statement next to it!

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 ' exits the current for loop, looking for the next column field that is not allowed to be null

End If

End If

End If

Next

End If

Next



' If the newly added row does not need to use the Row_changing event for numeric validation, enable the bottom comment line

' Me.BindingContext (OBJDS, "authors"). Endcurrentedit ()



Me.BindingContext (OBJDS, "authors"). ResumeBinding () ' Restore data binding



' Move table position to new add record

Me.BindingContext (OBJDS, "authors"). Position = Me.BindingContext (objDS, "authors"). Count-1



Catch Eendedit as System.Exception

System.Windows.Forms.MessageBox.Show (Eendedit.message)

Me.BindingContext (OBJDS, "authors"). Cancelcurrentedit () "Error, canceling new add line

Me.BindingContext (OBJDS, "authors"). ResumeBinding ()

End Try

Me.objds_positionchanged ()

End Sub

The essence of the above Btnadd.click event-handling code improvement is to temporarily suspend data binding, add a new record, and then assign a value to a column field in the record that is not allowed to be a null value (if you do not need to use the RowChanging event for a new record data test, You can also assign an initial value to a field that does not allow null values and cannot bind to a text box textbox, and finally restores data binding.

In addition, the Click event handlers for the four navigation buttons (first, Previous, Next, and last) are modified as follows (for simplicity, click event handlers for one of the < buttons above, for example, other buttons are modeled).

Private Sub Btnnavprev_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnnavprev.click

Try ' uses the Try statement for error handling (NULL and uniqueness)

Me.BindingContext (OBJDS, "authors"). Position = (Me.BindingContext (OBJDS, "authors"). POSITION-1)

Catch ex as Exception

MessageBox.Show (ex. Message) ' Displays error messages

Exit Sub ' If there is an error, don't move the position

End Try

Me.objds_positionchanged ()

End Sub

If you want to use the RowChanging event for new additions or edits ... Records for data validation (recommended), you also need to add the following RowChanging event handling code.

Private Sub row_changing (ByVal sender as Object, ByVal e as Ds.authorsrowchangeevent)



' Numeric validation of newly added rows

If e.action = Datarowaction.add Then



' Here for simplicity, the field au_id cannot be ' "for example

If editau_id. Text = "" Then

Throw (New Exception ("Data cannot be blank")

End If

End If



' You can also validate the edited value of a record

' If e.action = Datarowaction.change Then

' Here to fill in the Edit validation code

' End If

' Other verification, follow the above join it



End Sub

To start RowChanging event handling, add the following statement to the appropriate location of the program (I am appended to the statement Objds.merge (OBJDATASETTEMP) in the Loaddataset () method).

AddHandler objDS.authors.authorsRowChanging, AddressOf row_changing



Two, although there are not allowed to null value of the field, but all can be bound to the TextBox class control

For the sake of convenience, we'll use the Data Form Wizard to create a new form (note: The new form can be built in the same project on the previous form, the name of the data form is "Demo2", the new dataset name is "Ds2", and the data form is the employee table bound to the pubs database. As shown in Figure II, all fields in this table that are not allowed to be null values can be bound to a TextBox class control.



After you run the form, you can add a new record (a record numbered 44) After you click Load, but if you click any navigation button (such as the previous "<") without entering a new record, the error appears as shown in Figure three.



This kind of mistake is lighter, you can use the first solution, of course, if you have too many, you can also use the following simpler way: In the Data Form Wizard automatically generated code, only four navigation buttons (first, previous, Next, The last one), click event handler code to make appropriate changes (note: or according to the first case of the four navigation buttons to modify the code to modify the method, the other code does not increase, everything on the 0k, and then try again, you will see as the figure four friendly interface, Tip You must enter a column field that does not allow null values before you can change the location of the record.



Third, summary

When a field with null values is bound to a data form, to avoid a new record error, the above article describes a solution that can be found in code, and we can also find a solution in the dataset: "In the schema of the dataset, set a default value for the column field that does not allow null values." Of course, the specific use of the solution or you are flexible to choose according to the actual situation.

The above gives the first case of the main solution code, the last place please refer to the complete code below.

Public Class DEMO

Inherits System.Windows.Forms.Form



' To simplify, the code generated by the Windows Forms designer has been removed, remembering that the Data Form Wizard executes with the following requirements.

' The name of the data form instance is ' DEMO ', the name of the dataset is ' DS ', and the data connection is in SQL Server 2000

' Pubs sample Database For example, select the authors table, single record for individual controls display style, and all other default values.



Private Sub Btncancel_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btncancel.click

Me.BindingContext (OBJDS, "authors"). Cancelcurrentedit ()

Me.objds_positionchanged ()

End Sub



Private Sub Btndelete_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btndelete.click

If (Me.BindingContext (OBJDS, "authors"). Count > 0) Then

Me.BindingContext (OBJDS, "authors"). RemoveAt (Me.BindingContext (OBJDS, "authors"). Position)

Me.objds_positionchanged ()

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). AllowDBNull = False Then

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 Eendedit as System.Exception

System.Windows.Forms.MessageBox.Show (Eendedit.message)

Me.BindingContext (OBJDS, "authors"). Cancelcurrentedit ()

Me.BindingContext (OBJDS, "authors"). ResumeBinding ()

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnupdate_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnupdate.click

Try

' Try to update the data source.

Me.updatedataset ()

Catch Eupdate as System.Exception

' Add the error handling code here.

' Displays an error message, if any.

Objds.rejectchanges () ' I added the statement

System.Windows.Forms.MessageBox.Show (Eupdate.message)

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnload_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnload.click

Try

' Try to load the dataset.

Me.loaddataset ()

Catch Eload as System.Exception

' Add the error handling code here.

' Displays an error message, if any.

System.Windows.Forms.MessageBox.Show (Eload.message)

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnnavfirst_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnnavfirst.click

Try

Me.BindingContext (OBJDS, "authors"). Position = 0

Catch ex as Exception

MessageBox.Show (ex. Message)

Exit Sub

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnlast_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnlast.click

' Me.BindingContext (OBJDS, "authors"). Position = (Me.objDS.Tables ("authors"). ROWS.COUNT-1)

Try

' Notice the Next statement I made a change

Me.BindingContext (OBJDS, "authors"). Position = Me.BindingContext (objDS, "authors"). Count-1

Catch ex as Exception

MessageBox.Show (ex. Message)

Exit Sub

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnnavprev_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnnavprev.click

Try ' uses the Try statement for error checking (null and uniqueness)

Me.BindingContext (OBJDS, "authors"). Position = (Me.BindingContext (OBJDS, "authors"). POSITION-1)

Catch ex as Exception

MessageBox.Show (ex. Message)

Exit Sub ' If there is an error, don't move the position

End Try

Me.objds_positionchanged ()

End Sub



Private Sub Btnnavnext_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnnavnext.click

' If BindingContext (OBJDS, "authors"). Position = BindingContext (objDS, "authors"). Count-1 Then ' may not

' MessageBox.Show (the data is up to the top! ")

' Exit Sub

' End If

Try

Me.BindingContext (OBJDS, "authors"). Position = (Me.BindingContext (OBJDS, "authors"). Position + 1)

Catch ex as Exception

MessageBox.Show (ex. Message)

Exit Sub

End Try

Me.objds_positionchanged ()

End Sub



Private Sub objds_positionchanged ()

Me.lblNavLocation.Text = ((Me.BindingContext (OBJDS, "authors"). Position + 1). ToString + "") _

+ Me.BindingContext (OBJDS, "authors"). count.tostring)



End Sub



Private Sub Btncancelall_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btncancelall.click

Me.objDS.RejectChanges ()

Me.objds_positionchanged () ' I added the statement

End Sub



Public Sub UpdateDataSet ()

' Create a new dataset to hold the changes made to the primary dataset.

Dim Objdatasetchanges as ds = New ds

' Stop any current edits.

Me.BindingContext (OBJDS, "authors"). Endcurrentedit ()

' Gets the changes made to the primary dataset.

Objdatasetchanges = CType (objds.getchanges, DS)

' Check to see if any changes have been made.

If (not (objdatasetchanges) are nothing) Then

Try

' You need to make some changes, so try calling the Update method

' and passing data sets and any parameters to update the data source.

Me.updatedatasource (Objdatasetchanges)

Objds.merge (Objdatasetchanges)

Objds.acceptchanges ()

Catch Eupdate as System.Exception

' Add the error handling code here.

Throw eupdate

End Try

' Add code to check to see if any of the data sets returned may have been

' The error pushed into the row object error.

End If

End Sub



Public Sub Loaddataset ()

' Create a new dataset to hold the records returned from the FillDataSet call.

' A temporary dataset was used because populating an existing dataset

' Need to rebind data binding.

Dim Objdatasettemp as DS

Objdatasettemp = New DS

Try

' Attempt to populate the temporary dataset.

Me.filldataset (objdatasettemp)

Catch Efilldataset as System.Exception

' Add the error handling code here.

Throw Efilldataset

End Try

Try

' Empty the old records in the dataset.

Objds.clear ()

' Merges records into the primary dataset.

Objds.merge (objdatasettemp)



' I'm here to add the RowChanging event

AddHandler objDS.authors.authorsRowChanging, AddressOf row_changing



Catch Eloadmerge as System.Exception

' Add the error handling code here.

Throw Eloadmerge

End Try

End Sub



Public Sub Updatedatasource (ByVal changedrows as DS)

Try

' When you have a pending change, you only need to update the data source.

If (not (changedrows) are nothing) Then

' Open the connection.

Me.OleDbConnection1.Open ()

' Try to update the data source.

Oledbdataadapter1.update (Changedrows)

End If

Catch Updateexception as System.Exception

' Add the error handling code here.

Throw updateexception

Finally

' Closes the connection regardless of whether an exception is thrown.

Me.OleDbConnection1.Close ()

End Try

End Sub



Public Sub FillDataSet (ByVal dataSet as DS)

' Closes the constraint check before populating the dataset.

' This allows the adapter to populate the dataset without considering

' Dependencies between tables.

DataSet.EnforceConstraints = False

Try

' Open the connection.

Me.OleDbConnection1.Open ()

' Attempt to populate the DataSet with OleDbDataAdapter1.

Me.OleDbDataAdapter1.Fill (DataSet)

Catch Fillexception as System.Exception

' Add the error handling code here.

Throw fillexception

Finally

' Reopen the constraint check.

DataSet.EnforceConstraints = True

' Closes the connection regardless of whether an exception is thrown.

Me.OleDbConnection1.Close ()

End Try

End Sub



Private Sub row_changing (ByVal sender as Object, ByVal e as Ds.authorsrowchangeevent)

' Numeric validation of newly added rows

If e.action = Datarowaction.add Then



' Here for simplicity, the field au_id cannot be ' "for example

If editau_id. Text = "" Then

Throw (New Exception ("Data cannot be blank")

End If

End If



' You can also validate the edited value of a record

' If e.action = Datarowaction.change Then

' Here to fill in the Edit validation code

' End If

' Other verification, follow the above join it

End Sub

End Class

The above code is debugged through in the "pubs" sample database in Microsoft Visual Studio. NET 2003 + SQL Server 2000.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.