In the first part of this series, you learned how to add records to a database. In this article, you will learn how to search the records in a database and update them using the DataGrid control. The DataGrid is one of the most powerful, useful, and flexible controls in the .NET Framework Class Library.
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).
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
Next Time
In the next article I will cover authenticating users.
About the Author
Anand Narayanaswamy is a Microsoft MVP (Microsoft Most Valuable Professional) who works as a freelance Web/Software developer and technical writer. He runs and maintains learnxpress.com, and provides technical support to users. His areas of interest include Web development, Software development using Visual Basic, and in the design and preparation of courseware, technical articles, and tutorials. He can be reached at anandnswamy@rediffmail.com.
# # #
