(VB.net) use the DataGrid to implement the functions of searching, editing, modifying, updating, and deleting.

imports system
imports system. Data
imports system. Data. sqlclient
imports system. Configuration

Public class webtest
Inherits system. Web. UI. Page
Protected withevents ddlsalesarea as system. Web. UI. webcontrols. dropdownlist
Protected withevents dgsfdetail as system. Web. UI. webcontrols. DataGrid
Protected withevents dgsave as system. Web. UI. webcontrols. DataGrid
Protected withevents btnsearch as system. Web. UI. webcontrols. Button
Dim myconn as sqlconnection
Private sub page_load (byval sender as system. Object, byval e as system. eventargs) handles mybase. Load
'Users who place the initialization page hereCodeDgsave. edititemindex = 2 editable row number
Myconn = new sqlconnection (configurationsettings. appsettings ("sqlserverconnectionstring "))

 

End sub
'Query data button
Private sub btnsearch_click (byval sender as system. Object, byval e as system. eventargs) handles btnsearch. Click
Binddata ()
Binddata2 ()
Dgsave_binddatagrid ()
End sub

'Execute the SQL statement that does not return the result set
Private sub executesql (byval strsql)
Try
Dim myconn as sqlconnection
Dim mycommand as sqlcommand
Myconn = new sqlconnection (configurationsettings. appsettings ("sqlserverconnectionstring") 'read from web. config
Myconn. open ()
Mycommand = new sqlcommand (strsql, myconn)
Mycommand. executenonquery ()
Myconn. Close ()
Catch ex as exception
Response. Write ("<script language = 'javascript '> alert ('" + ex. Message + "'); </SCRIPT> ")
Finally
Myconn. Close ()
End try

End sub
'Bind the data and re-display the data
Private sub binddata ()
Dim ls_dept as string
Dim ls_ SQL as string
Dim li_count as integer
Dim I as integer
Dim dset1 as Dataset
Dim mycommand as sqldataadapter
Ls_dept = ddlsalesarea. selectedvalue. tostring ()
Ls_ SQL = ls_ SQL & "select * From pub_telphone where dept = '" & ls_dept &"'"
Mycommand = new sqldataadapter (ls_ SQL, myconn)
Dset1 = new dataset
Mycommand. Fill (dset1, "phone ")
Dgsfdetail. datasource = dset1.tables ("phone"). defaultview
Dgsfdetail. databind ()
Li_count = dgsfdetail. Items. Count

For I = 1 to li_count

If (I mod 2 = 0) then
'Dgsfdetail. selecteditemstyle. backcolor = ""

Else

End if

Next

End sub

'Dgsave binding
Private sub binddata2 ()
Dim ls_dept as string
Dim ls_ SQL as string
Dim dset2 as Dataset
Dim mycommand as sqldataadapter
'Ls _ dept = ddlsalesarea. selectedvalue. tostring ()
Ls_dept = ""
Ls_ SQL = ls_ SQL & "select * From pub_telphone where dept = '" & ls_dept &"'"
Mycommand = new sqldataadapter (ls_ SQL, myconn)
Dset2 = new dataset
Mycommand. Fill (dset2, "phone ")
Dgsave. datasource = dset2.tables ("phone"). defaultview
Dgsave. databind ()
End sub

'Bind the DataGrid

Private sub dgsave_binddatagrid ()
Dim ls_dept as string
Dim ls_ SQL as string
Dim dset2 as Dataset
Dim mycommand as sqldataadapter

Ls_dept = ""
Ls_ SQL = ls_ SQL & "select * From pub_telphone where dept = '" & ls_dept &"'"
Mycommand = new sqldataadapter (ls_ SQL, myconn)
Dset2 = new dataset
Mycommand. Fill (dset2, "phone ")
Dgsave. datasource = dset2.tables ("phone"). defaultview

Dim phonetable as datatable = dset2.tables ("phone ")
Dim phoneview as dataview = phonetable. defaultview

phoneview. allowdelete = false
dim addrow as datarowview = phoneview. addnew ()
addrow ("user_name") = "Dashan"
addrow ("user_account") = ""
addrow ("Dept ") = ""
addrow ("telphone") = ""
addrow ("fax") = ""
addrow ("mobile ") = ""
dgsave. edititemindex = 0
dgsave. databind ()
end sub

'click to edit the updated code.
private sub dgsave_updatecommand (byval sender as system. object, byval e as system. web. UI. webcontrols. datagridcommandeventargs) handles dgsave. updatecommand
dim ls_username, ls_account, ls_mobile, ls_telphone, ls_fax, ls_dept as string
dim strsql as string
dim TB as textbox

TB = ctype (E. item. cells (0 ). controls (0), textbox)
ls_username = TB. text
TB = ctype (E. item. cells (1 ). controls (0), textbox)
ls_account = TB. text
TB = ctype (E. item. cells (2 ). controls (0), textbox)
ls_dept = TB. text
TB = ctype (E. item. cells (3 ). controls (0), textbox)
ls_fax = TB. text
TB = ctype (E. item. cells (4 ). controls (0), textbox)
ls_telphone = TB. text
TB = ctype (E. item. cells (5 ). controls (0), textbox)
ls_mobile = TB. text

'Strsql = strsql & "Update pub_telphone set user_name = '" & ls_username & "', user_account = '" & ls_account & "', DEPT = '" & ls_dept &"',"
'Strsql = strsql & "Mobile = '" & ls_mobile & "', telphone = '" & ls_telphone & "', fax = '" & ls_fax &"'"
'Strsql = strsql & "where user_name = '" & ls_username &"'"
'Insert SQL statement

Strsql = strsql & "insert into pub_telphone (user_name, user_account, DEPT, telphone, fax, mobile) values ('" & ls_username & "', '" & ls_account &"', '"& ls_dept &"', '"& ls_fax &"', '"& ls_telphone &"', '"& ls_mobile &"')"

Executesql (strsql)
Dgsave_binddatagrid ()
'Switch the item out of the editing mode dgsave. edititemindex =-1
End sub
'Edit button code
Private sub dgsave_editcommand (byval sender as system. Object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsave. editcommand
Dgsave. edititemindex = E. Item. itemindex
Binddata2 ()
End sub

'Code executed when the page changes
Private sub dgsave_pageindexchanged (byval source as system. Object, byval e as system. Web. UI. webcontrols. datagridpagechangedeventargs) handles dgsave. pageindexchanged
Dgsave. currentpageindex = E. newpageindex
Binddata2 ()
End sub
'Deleted code
Private sub dgsave_deletecommand (byval sender as system. Object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsave. deletecommand
If (dgsave. Items. Count = 1) then
If (dgsave. currentpageindex <> 0) then
Dgsave. currentpageindex = dgsave. currentpageindex-1
End if
End if
Dim strsql = "delete from pub_telphone where user_name = '" & E. Item. cells (0). Text &"'"
Executesql (strsql)
Binddata2 ()

End sub

'Cancel
Private sub dgsave_cancelcommand (byval source as system. Object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsave. cancelcommand
Dgsave. edititemindex =-1
Binddata2 ()
End sub

Private sub initializecomponent ()

End sub

'Updated code

Private sub dgsfdetail_updatecommand (byval source as system. Object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsfdetail. updatecommand
Dim ls_username, ls_account, ls_mobile, ls_telphone, ls_fax, ls_dept as string
Dim TB as textbox
Dim strsql as string

TB = ctype (E. item. cells (0 ). controls (0), textbox)
ls_username = TB. text
TB = ctype (E. item. cells (1 ). controls (0), textbox)
ls_account = TB. text
TB = ctype (E. item. cells (2 ). controls (0), textbox)
ls_dept = TB. text
TB = ctype (E. item. cells (3 ). controls (0), textbox)
ls_fax = TB. text
TB = ctype (E. item. cells (4 ). controls (0), textbox)
ls_telphone = TB. text
TB = ctype (E. item. cells (5 ). controls (0), textbox)
ls_mobile = TB. text

Strsql = strsql & "Update pub_telphone set user_name = '" & ls_username & "', user_account = '" & ls_account & "', DEPT = '" & ls_dept &"',"
Strsql = strsql & "Mobile = '" & ls_mobile & "', telphone = '" & ls_telphone & "', fax = '" & ls_fax &"'"
Strsql = strsql & "where user_name = '" & ls_username &"'"
Executesql (strsql)
Dgsfdetail. edititemindex =-1
Binddata ()
'Switch the item out of the editing mode dgsave. edititemindex =-1
End sub
'Delete button code
Private sub dgsfdetail_deletecommand (byval source as object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsfdetail. deletecommand
'Add code to delete data from data source.
Dim strsql as string
If (dgsfdetail. Items. Count = 1) then
If (dgsfdetail. currentpageindex <> 0) then
Dgsfdetail. currentpageindex = dgsfdetail. currentpageindex-1
End if
End if
'Dim mydeletebutton as button

'Mydeletebutton = ctype (E. Item. findcontrol ("delete"), button)

'Mydeletebutton. Text = "delete this row"
'Mydeletebutton. Attributes. Add ("onclick", "Return confirm ('Do you really want to delete the nth row" + E. Item. itemindex. tostring () +? ');")
Strsql = strsql & "delete from pub_telphone where user_name = '" & E. Item. cells (0). Text &"'"
Executesql (strsql)
Binddata ()
End sub

'Edited code
Private sub dgsfdetail_editcommand (byval source as object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsfdetail. editcommand

Dgsfdetail. edititemindex = E. Item. itemindex
Binddata ()

End sub
'Canceled code
Private sub dgsfdetail_cancelcommand (byval source as object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs) handles dgsfdetail. cancelcommand
Dgsfdetail. edititemindex =-1
Binddata ()
End sub

'Save the code
'Private sub dgsfdetail_itemcreated (byval sender as system. Object, byval e as system. Web. UI. webcontrols. datagriditemeventargs)
'E. item. Attributes. Add ("onmouseout", "This. style. backgroundcolor = currentcolor; this. style. cursor = 'default ';")
'E. item. attributes. add ("onmouseover", "currentcolor = This. style. backgroundcolor; this. style. backgroundcolor = '# ffffcc'; this. style. cursor = 'hand ';")
'End sub

'Private sub dgsfdetail_itemdatabound (byval source as system. Object, byval e as system. Web. UI. webcontrols. datagridcommandeventargs)

'E. item. backcolor = color. Blue

'End sub
End Class