This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in VB.NET
Following on from the Updating Custom Object tutorial, we will look into how we can add the Update functionality, allowing us to edit an object and then commit changes to the database. By using objects, we could first make the changes and then decide whether or not to save these changes to the database.
However, in this example, we will use the class of the previous tutorial to add a method to update the object. If you have not followed that tutorial yet, it is recommended. You can find it here.
Once you have the project opened up, open up the Car.vb from App_Code. There are two things we are going to add: A constructor that takes an int parameter; and an UpdateMethod that takes a Car object parameter.
Let's go ahead and add the constructor first. This will be different from the constructor we already have; it will be more like a method, without returning anything. The constructor will look something like this:
''' <summary>
''' Contructor for Car object, set the Car object from ID passed to it.
''' </summary>
''' <param name="theCarID">Int32 value of the ID of the Car to get.</param>
Public Sub New(ByVal theCarID As Integer)
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As SqlCommand
cmd = New SqlCommand("sp_GetCarByID", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@CarID", theCarID)
connection.Open()
Dim objReader As SqlDataReader = cmd.ExecuteReader()
Do While objReader.Read()
SetObjectData(objReader)
Loop
objReader.Close()
connection.Close()
Catch
connection.Close()
End Try |
Notice here we are referencing a new Stored Procedure, one that will retrieve the record from the database that matches the ID parameter we pass to it. The Stored Procedure will look something like this:
CREATE PROCEDURE dbo.sp_GetCarByID
@CarID int
AS
SELECT * FROM Cars WHERE ID = @CarID
|
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
We also need another Stored Procedure - one to Update the Car object we pass. It should look something like this:
CREATE PROCEDURE dbo.sp_UpdateCar
@ID int,
@Make varchar(50),
@Model varchar(50)
AS
UPDATE Cars
SET Make = @Make, Model = @Model
WHERE ID = @ID
|
Now we have our Stored Procedures, we can finish up with the class by adding the Update method:
''' <summary>
''' Updates a Car in the database.
''' </summary>
''' <returns>Returns Boolean value indicating if the method finished without errors.</returns>
Public Shared Function UpdateCar(ByVal theCar As Car) As Boolean
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As New SqlCommand("sp_UpdateCar", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@ID", theCar.ID)
cmd.Parameters.AddWithValue("@Make", theCar.Make)
cmd.Parameters.AddWithValue("@Model", theCar.Model)
connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
Return True
Catch
connection.Close()
Return False
End Try
End Function
|
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
Notice we are passing in a Car object, and then using its Properties to pass to the Stored Procedure. We return true if the Update was successful.
We are now done with modifying the class. We can move back to our ASPX page and make changes to the Repeater. What we are going to do is to display all the records in a Repeater, and provide a LinkButton for record to be edited. We will also provide two textboxes to edit the Make and Model, and then a button to commit the changes. The Repeater will look something like this:
<asp:Repeater ID="repeater_Cars" runat="server">
<HeaderTemplate>
<table width="350"><tr>
<td><strong>ID</strong></td>
<td><strong>Make</strong></td>
<td><strong>Model</strong></td>
<td><strong>Action</strong></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("ID") %></td>
<td><%# Eval("Make") %></td>
<td><%# Eval("Model") %></td>
<td><asp:LinkButton ID="lnk_Edit" runat="server" CommandArgument='<%# Eval("ID") %>'
CommandName="EditCar" Text="Edit" /></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate> |
Notice we are using the ItemCommand event of the Repeater to handle the edits. By doing this, we are able to set a CommandName and CommandArgument for each LinkButton that is rendered, for each object in the collection. We will use the ID for the CommandArgument, because the ID is the unique identifier. Let's finish the form and add our textboxes and button:
ID: <asp:Literal ID="lit_ID" runat="server" /><br />
Make: <asp:TextBox ID="fld_Make" runat="server" /><br />
Model: <asp:TextBox ID="fld_Model" runat="server" /><br />
<asp:Button ID="btn_Update" runat="server" Text="Update" Enabled="false" />
|
We also include a Literal control to display the ID, as this is not editable. We disable the button on load, and will enable when an edit is clicked.
Moving to the code-behind, we will first need to make sure we are referencing our custom class:
Next, we bind the Repeater on Page_Load:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If (Not IsPostBack) Then
BindRepeater()
End If
Protected Sub BindRepeater()
repeater_Cars.DataSource = Car.GetAllCars()
repeater_Cars.DataBind()
End Sub
|
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Next, we create the OnItemCommand handler. This can be added manually, or by clicking on the Repeater in Design view, going to Properties and double-click on the ItemCommand Event field.
This first checks to see if the correct command is being called, and then it converts the CommandArgument to the ID of the Car object, which it then retrieves from the database using our new constructor, and then fills in the literal and textboxes as well as enabling the button.
Finally, we add the handler for the button click event:
Protected Sub btn_Update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Update.Click
If (Not String.IsNullOrEmpty(fld_Make.Text)) AndAlso (Not String.IsNullOrEmpty(fld_Model.Text)) Then
Dim carToEdit As New Car(Convert.ToInt16(lit_ID.Text))
carToEdit.Make = fld_Make.Text
carToEdit.Model = fld_Model.Text
If Car.UpdateCar(carToEdit) Then
lit_ID.Text &= " [Updated]"
fld_Make.Text = ""
fld_Model.Text = ""
BindRepeater()
btn_Update.Enabled = False
End If
End If |
Now if this application is run, you will see that all Cars are displayed from the collection straight away. We are then able to click on the Edit link of each object, and edit them right on the page. We can even improve the functionality of this by including a ScriptManager and UpdatePanel. The editing will then seem instantaneous.
|