ASP.NET Data Controls Part 1: DataGrids
Pages: 1, 2, 3
Let's now write the code to perform all of the various functions that we have indicated in the DataGrid control.
First, we have the Page_Load() event:
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'---sets the initial page size to be 4
If Session("page_size") Is Nothing Then _
Session("page_size") = 4
'---if loading for the first time,
'---set the page size and also the page index
If Not IsPostBack Then
txtPageSize.Text = Session("page_size")
DataGrid1.PageSize = txtPageSize.Text
DataGrid1.CurrentPageIndex = Session("curr_page")
Load_Data()
End If
'---displays the selected employee information---
If Request.QueryString("emp_id") <> "" Then
Dim ds As New DataSet
SqlDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Employee WHERE emp_id='" & _
Request.QueryString("emp_id") & "' " & Session("sortBy")
SqlDataAdapter1.Fill(ds, "employee_record")
DataGrid2.DataSource = ds
DataGrid2.DataBind()
End If
End Sub
Here is the Load_Data() method, which loads the data from the database and binds it to the DataGrid control:
Private Sub Load_Data()
Dim ds As New DataSet
SqlDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Employee " & Session("sortby")
SqlDataAdapter1.Fill(ds, "employee")
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
The code for the Hide Names button:
Private Sub hidedetails_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles hidedetails.Click
'---hides the second, third and fourth column---
DataGrid1.Columns(1).Visible = False
DataGrid1.Columns(2).Visible = False
DataGrid1.Columns(3).Visible = False
End Sub
The code for the Show Names button:
Private Sub showdetails_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles showdetails.Click
'---displays the second, third and fourth column---
DataGrid1.Columns(1).Visible = True
DataGrid1.Columns(2).Visible = True
DataGrid1.Columns(3).Visible = True
End Sub
The code for the edit() method:
Public Sub edit(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---sets the row to edit---
DataGrid1.EditItemIndex = e.Item.ItemIndex
Load_Data()
End Sub
The code for the cancel() method:
Public Sub cancel(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---deselects the row to edit---
DataGrid1.EditItemIndex = -1
Load_Data()
End Sub
The code for the update() method:
Public Sub update(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
Dim tbox As TextBox
Dim fname, lname, emp_id As String
'---retrieves the data from the row to be updated---
tbox = e.Item.Cells(2).Controls(0)
fname = tbox.Text
tbox = e.Item.Cells(3).Controls(0)
lname = tbox.Text
'---retrieves the key for the row---
emp_id = DataGrid1.DataKeys(e.Item.ItemIndex)
'---updates the database---
Dim sql As String = "UPDATE Employee SET fname='" & _
fname & "' , lname='" & lname & _
"' WHERE emp_id='" & emp_id & "'"
Dim conn As New SqlConnection("server=localhost; " & _
"user id =sa; password=;database=pubs")
Dim comm As New SqlCommand(sql, conn)
conn.Open()
comm.ExecuteNonQuery()
conn.Close()
DataGrid1.EditItemIndex = -1
Load_Data()
End Sub
The code for the sort() method:
Public Sub sort(ByVal s As Object, _
ByVal e As DataGridSortCommandEventArgs)
' session object is used to support paging
Session("sortby") = e.SortExpression
Load_Data()
End Sub
The code for the delete_record() method:
Public Sub delete_record(ByVal s As Object, _
ByVal e As DataGridCommandEventArgs)
'---retrieves the key for the row---
Response.Write(DataGrid1.DataKeys(e.Item.ItemIndex))
'---codes to delete row here----
'
'-------------------------------
End Sub
The code for the changepage() method:
Public Sub changepage(ByVal s As Object, _
ByVal e As DataGridPageChangedEventArgs)
'---displays the next page---
DataGrid1.CurrentPageIndex = e.NewPageIndex
Session("curr_page") = e.NewPageIndex
Load_Data()
End Sub
The code for the Change button:
Private Sub changepagesize_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles changepagesize.Click
'---changes the size of the page---
DataGrid1.PageSize = CLng(txtPageSize.Text())
Session("page_size") = DataGrid1.PageSize
Load_Data()
End Sub
Run this application and you should now be able to display the records in multiple pages. You can also edit and delete any rows you desire:
 |
| Figure 10. Running the example application |
Summary
The DataGrid is a very powerful and useful control, if you know how to use it properly. Don't be fooled by its appearance; you still need to write substantial code to make it work the way you want it to work. By if you take some time to trace the code and understand the documentation, it is really worth the effort!
Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.
Return to ONDotnet.com
Is this your favorite ASP.NET Data control?
You must be logged in to the O'Reilly Network to post a talkback.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 37 of 37.
-
Update Data grid
2007-01-10 20:53:30 ASHA [Reply | View]
sir,
i have followed the same code to retive the row value but the value is not updated to the new value.
-
datagrid
2006-10-18 00:25:05 Meenakshipoyyali [Reply | View]
When i try to delete the records its showing this error. What is its meaning?
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
-
Need help
2006-05-16 00:04:42 baagii [Reply | View]
Hello there all,
Since i am beginner with asp.net........Got some errors while i was doing...........I got this.....If anyone could help me or let me know how to fix it............
Login failed for user 'BATBAYAR\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'BATBAYAR\ASPNET'.
Source Error:
Line 157: SqlDataAdapter1.SelectCommand.CommandText = _
Line 158: "SELECT * FROM Employee " & Session("sortby")
Line 159: SqlDataAdapter1.Fill(ds, "employee")
Line 160: DataGrid1.DataSource = ds
Line 161: DataGrid1.DataBind()
Any ideas,advices would be big help!
Thanks in advance.....
baagii
-
Problem in Update with Data Grid
2005-11-11 00:30:56 ad_dev [Reply | View]
I am using following code for Data Grid Update & Delete, but update command is not working properly. When i select a row and edit it's content and then i press update button then it is taking old value at the time of update not new. Please help me in this regard.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim con As New OleDb.OleDbConnection()
Dim com As New OleDb.OleDbCommand()
Dim str3 As String
con.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=sis;Data Source=ROSHAN"
con.Open()
str3 = "select * from student"
com.CommandText = str3
com.Connection = con
Dim dataadapter As New OleDb.OleDbDataAdapter(com)
Dim ds As New Data.DataSet()
dataadapter.Fill(ds, "student")
studentgrid.DataSource = ds.Tables("student")
studentgrid.DataBind()
End Sub
Public Sub studentgrid_edit(ByVal sender As Object, ByVal E As
DataGridCommandEventArgs)
studentgrid.EditItemIndex = E.Item.ItemIndex
studentgrid.DataBind()
End Sub
Sub studentgrid_Cancel(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
studentgrid.EditItemIndex = -1
studentgrid.DataBind()
End Sub
Sub studentgrid_Update(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
'=============================================================
'------------Retrive Data From Row To Be Updated-----------
Dim nmbox As TextBox
Dim stname, enrid As String
nmbox = E.Item.Cells(3).Controls(0)
stname = nmbox.Text
'-------------Retrive the key for the row------------------
enrid = studentgrid.DataKeys(E.Item.ItemIndex)
'-------------Update The Database--------------------------
Dim con As New OleDb.OleDbConnection()
Dim com As New OleDb.OleDbCommand()
Dim str3 As String
str3 = "update student set stdname='" & stname & "' where
enrcode='" & enrid & "'"
con.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=sis;Data Source=ROSHAN"
con.Open()
com.CommandText = str3
com.Connection = con
com.ExecuteNonQuery()
con.Close()
End Sub
Sub studentgrid_delete(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
Dim enrid As String
'-------------Retrive the key for the row------------------
enrid = studentgrid.DataKeys(E.Item.ItemIndex)
'-------------Delete The Database--------------------------
Dim con As New OleDb.OleDbConnection()
Dim com As New OleDb.OleDbCommand()
Dim str3 As String
str3 = "delete from student where enrcode='" & enrid & "'"
con.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=sis;Data Source=ROSHAN"
con.Open()
com.CommandText = str3
com.Connection = con
'com.ExecuteNonQuery()
con.Close()
End Sub
End Class
-
Error when run the code
2005-06-27 00:20:42 KAMAL [Reply | View]
lvoely code.
but its generates the following error.
plz help me.
- 'edit' is not a member of 'ASP.WebForm1_aspx'.
Line 33:
-
Error when run the code
2005-09-14 02:47:24 Saheb [Reply | View]
Hi KAMAL,
Could you please explain me how you removed the error?I was also trying to use the code but it showed error as you had explained-Also please give solutions to the further problems which you encountered while using this code-hoping to hear from you soon.
Thanks
-
asp.net
2005-04-06 02:18:03 wishi [Reply | View]
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If (Not IsPostBack) Then
ddl_databind()
End If
End Sub
Public Sub ddl_databind()
Dim sqlcon As New SqlConnection("server=Shikhaws;user id=sa;password=sa;database=northwind")
Dim sqlcmd As New SqlCommand("SELECT FirstName,EmployeeId FROM Employees", sqlcon)
sqlcon.Open()
DropDownList1.DataSource = sqlcmd.ExecuteReader()
DropDownList1.DataTextField = "FirstName"
DropDownList1.DataValueField = "EmployeeId"
DropDownList1.DataBind()
sqlcon.Close()
End Sub
Public Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Dim sb As New StringBuilder
Dim ddl As New DropDownList
sb.Append("DropDownList1.selecteditem value=")
sb.Append(DropDownList1.SelectedItem.Value)
sb.Append("
")
sb.Append("dropdownlist.selecteditem=")
sb.Append(ddl.SelectedItem)
lblmessage.Text = sb.ToString()
Dim sqlcon As New SqlConnection("server=localhost;uid=sa;pwd=;database=northwind")
Dim ds As New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT Title FROM Employees where EmployeeId= " & DropDownList1.SelectedItem.Value, sqlcon)
da.Fill(ds, "EmployeeId")
TextBox1.Text = ds.Tables(0).Rows(0)("Title")
End Sub
End Class
-
Works Gr8 Gud article - Infact Getting Small error
2005-03-24 22:50:59 MulapalliPrasad [Reply | View]
Every thing works fine , But when i click on update i am getting error saying DATA TYPE MISMATCH.... please help me in this regard.
I've checked the data types at the back end .. they are absolutely fine as per my vision.
thanks in advance.
Prasad
Here goes my code.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\lakshmi\jobs\jobs.mdb';"
Dim MySQL As String = "UPDATE usermaster SET username='" & _
username & "' , phone='" & CInt(phone) & _
"' WHERE userid='" & userid & "'"
Dim MyConn As New OleDbConnection(strConn)
Dim Cmd As New OleDbCommand(MySQL, MyConn)
Dim Usercount As Integer
MyConn.Open()
Cmd.ExecuteNonQuery()
MyConn.Close()
DataGrid1.EditItemIndex = -1
Load_Data()
End Sub
-
about print datagrid
2005-03-23 01:44:31 sun9582 [Reply | View]
Respected Sir,
How to print the datagrid in the ASP.NET with backhand of Oracle.
I want to get the print of the datagrid data
so, give reply me as soon as possibel on this e-mail, nirav_bodiwala@hotmail.com
thank you,
-
Remove row dialog
2004-12-31 01:28:15 Flax [Reply | View]
I was wondering if this example would be a correct place to handle a dialog screen.
Most of the time you want to ask the user if he / she is sure about removing the row and i had hard time finding a good working example for this
-
Code behind
2004-08-11 02:45:25 stuart_taylor [Reply | View]
Hi, love the article, i'm new to this whole .NET thing, and am getting to grips with the C# flavour, i notice that the code behind is in VB, is it easy to convert?
any help would be appreciated.
-
To all who say Rubbish
2004-01-16 06:44:32 anonymous2 [Reply | View]
What the hell do you want? The author to write all of your custom code for you. This is a very good article, that gives good examples for the code needed for custom datagrids!
-
Datagrids example
2003-11-10 08:31:21 anonymous2 [Reply | View]
Exactly what I needed to update and delete records from a Datagrid! Thank you very much!
Paul McP.
-
Very Nice and Tidy...
2003-08-28 06:34:08 anonymous2 [Reply | View]
this is great.
it helps me lot.
thanks.
-
dats gud
2003-08-21 08:09:38 anonymous2 [Reply | View]
hi! you have explained it very well and all the things are good and most of the things are working but i need u know that if i click on the grid on the rows then it should give me the field name of the particular cell how can i do that. if you can manage to explain this then i can be very helpful. Ta
-
ASP.NET Data Controls Part 1: DataGrids -
2003-08-18 21:59:43 anonymous2 [Reply | View]
very good topic. Thanks for your information provided on the web.
-
ASP.NET Data Controls Part 1: DataGrids
2003-06-29 13:34:20 anonymous2 [Reply | View]
In Wei-Meng Lee's article above, I can find no way to click on the page number to get to the next page. In my copy, it only shows page number 1 and no ability to click on it!
Could you please tell me what's missing from the article?
Thanks,
Larry
rosenzl@optonline.net
-
source code?
2003-06-12 11:53:25 anonymous2 [Reply | View]
can we download the full source code for this? i am curious as to how the ...aspx?emp_id={0} is called. does it go straight to a new page? i don't get it
please reply to
webmaster@kendalltech.com
-
Rubbish!
2003-04-25 08:10:25 anonymous2 [Reply | View]
The thing doesn't work, is poorly and is poorly explained. -
Rubbish!
2003-07-25 20:56:13 anonymous2 [Reply | View]
U so good u come and give yur source code and explain it lah...imbecile -
To the person who wrote Rubbish!
2003-05-05 23:08:17 anonymous2 [Reply | View]
I hate people like you saying rubbish to other people's hard work.
If you aren't smart enough to understand, admit it. -
To the person who wrote Rubbish!
2003-12-18 08:34:29 anonymous2 [Reply | View]
I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.
-
To the person who wrote Rubbish!
2003-12-18 08:34:24 anonymous2 [Reply | View]
I think hate is a harsh word. I agree with the guy who wrote "Rubbish" because that explains this article perfectly. It doesn't work and it is poorly explained.
-
help
2003-03-21 01:26:05 enigma5 [Reply | View]
can you guide me how can i specify more than one fields in 'DataNavigateUrlField'..
for eg:
DataNavigateUrlField="Order ID" DataNavigateUrlFormatString="WebForm2.aspx?Order ID={0}&ItemID={1}"
how can i specify this itemId in DataNavigateUrlField with Order ID
as i m sending as anonymous please reply me on nausheen.ramzan@eintelligencesoft.com
thnx
thnx
i am a fresher in asp.net.
Can anyone give me all the info in dataGrids
in a layman's langauge.
thanks