Building Data Aware Web Applications with ASP.NET

Share |

The need for updating records arises when a user needs to change information such as his or her email address, telephone number, or other item that may frequently change. In order to update a record it is necessary for a user to feed in a key piece of data such as a username or Userid number. Based on this input, only the records for the specified user would be displayed on the DataGrid. After that the user can edit the entries and update them. The Grid also provides a facility to cancel the entries before updating them.

Searching For A Record

Firstly, let us see how to search for a record. Our user-interface (see Figure 1) contains a text box, a button, and an invisible DataGrid. The text box is used to enter search criteria—in this case a user name. Clicking the button will activate a DataGrid used to display the records.

Figure 1

The Getusers function (see Listing 1) does a majority of the work. It uses the SQL statement to pull records from the database, Wintergreen. Here the user name is used as the criteria for search. The SQL string and connection statement objects are passed as parameters to the SQLDataAdapter class and subsequently filled with dataset and table name.

Listing 1

public function Getusers(byval username as string) as dataset
   dim strconn as string
   dim sqlstring as string
   dim myconn as sqlconnection
   dim myadap as sqldataadapter
   dim ds as new dataset()
   
   strconn = "Data Source=localhost;User ID=sa;password=;Initial Catalog = Wintergreen"
   sqlstring = "select * from Employees"
   sqlstring += " where username = '" + username +"'"
   myconn = new sqlconnection(strconn)
   myadap = new sqldataadapter(sqlstring,myconn)
   myadap.fill(ds, "Employees")
   return ds
   end function

The button event (see Listing 2) calls the search function by accepting a Textbox control argument. Finally, the DataGrid is populated with its DataSource property. Keep in mind that the DataGrid is filled with records only upon calling the built-in DataBind() method of the SQLClient namespace.

Listing 2

Sub Button1_Click(sender As Object, e As EventArgs)
dgrid.visible = true
dim ds as dataset
ds = Getusers(textbox1.text)
dgrid.datasource = ds.tables("Employees").defaultview
dgrid.databind()
End Sub

The Figure 2 displays a sample look and feel of our DataGrid filled with a record from the database.

Figure 2

You can notice that there is a link titled "Edit". Click it in order to update the records. Upon clicking, the DataGrid automatically displays two other links titled "Update" and "Cancel". Moreover, each field's value will automatically become visible in the textboxes inside the grid so that you can edit the relevant information. (See Figure 3).

Click here for larger image

Figure 3

If you click the Update link then the database will be updated with the values that you have entered. If, after entering new values, you don't want to update then you can make use of the Cancel link. Each of the links, fires the appropriate event upon execution as shown in Listings 3, 4 and 5.

Listing 3

sub dgrid_edit(s as object, e as datagridcommandeventargs)
   dgrid.edititemindex = e.item.itemindex
   bindgrid
end sub

Listing 4

sub dgrid_update(s as object, e as datagridcommandeventargs)

   dim intid as integer
   dim txtname as textbox
   dim strname as string

   dim txtage as textbox
   dim intage as integer

   dim txtaddr as textbox
   dim straddr as string

   dim txtem as textbox
   dim strem as string

   intid = dgrid.datakeys(e.item.itemindex)
   txtname = e.item.cells(1).controls(0)
   strname = txtname.text

   txtage = e.item.cells(2).controls(0)
   intage = txtage.text

   txtaddr = e.item.cells(3).controls(0)
   straddr = txtaddr.text

   txtem = e.item.cells(4).controls(0)
   strem = txtem.text

   strsql = "Update Employees Set name = @name, age = @age,email = @email,
address = @address where id = @id"
   cmdsql = new sqlcommand(strsql,myconn)

   cmdsql.parameters.add("@name",strname)
   cmdsql.parameters.add("@age",intage)
   cmdsql.parameters.add("@address",straddr)
   cmdsql.parameters.add("@email",strem)
   cmdsql.parameters.add("@id",intid)

   myconn.open()
   cmdsql.executenonquery()
   myconn.close()
   dgrid.edititemindex = -1
   bindgrid
   end sub

Note that the index number of the first cell of the grid starts from 1 and so on. The respective value is stored in a variable, which is then passed as a parameter to the add method along with the parameter name of the SQL. You should only provide the appropriate data type to the above variables. You should also be careful to enter the correct field names into the update SQL statement. Otherwise, the server will return runtime error.

Listing 5

sub dgrid_cancel(s as object, e as datagridcommandeventargs)
           dgrid.edititemindex = -1
           bindgrid
end sub

The Code Behind The DataGrid

The updating process will become active only when you call the above three methods namely dgrid_edit, dgrid_update, and dgrid_cancel in a DataGrid WebForm control as shown in listing 6:

Listing 6

<asp:datagrid id="dgrid" runat="server" 
datakeyfield="id" align="center" 
autogeneratecolumns="false" 
onitemcommand="dgrid_edit" 
oneditcommand="dgrid_edit" 
onupdatecommand="dgrid_update" 
oncancelcommand="dgrid_cancel" 
gridlines="both" 
Visible="False">

  <Columns>

   <asp:BoundColumn DataField="id" ReadOnly="True" 
    HeaderText="ID">
   </asp:BoundColumn>

   <asp:BoundColumn DataField="name" HeaderText="Name">
   </asp:BoundColumn>

   <asp:BoundColumn DataField="age" HeaderText="Age">
   </asp:BoundColumn>

   <asp:boundcolumn datafield="address" HeaderText= "Address">
   </asp:boundcolumn>

   <asp:boundcolumn datafield="email" HeaderText= "E-mail">
   </asp:boundcolumn>

   <asp:EditCommandColumn 
        ButtonType="LinkButton" UpdateText="Update"
        CancelText="Cancel" EditText="Edit">
   </asp:EditCommandColumn>

 </Columns>

</asp:datagrid>

You can very well delete one or two Boundcolumn tags from the above code snippet as the AutoGenerateColumn property is set to false (See the code inside the DataGrid tag). By default, the DataGrid will display all records from the database (AutoGenerateColumn property is true). If you don't want to show all of the fields, then you can apply the BoundColumn control as shown above. The EditCommandColumn control displays the interface for the edit, update, and cancel links.

Demo

A live demo can be seen at: http://learnxpress.europe.webmatrixhosting.net/database_part2/SearchEdit.aspx

Code Download

Download the source code: Part2_codes.zip