<%@ Page Language="VB" runat="server" debug="true" explicit="true" aspcompat=true validateRequest=false%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Dim MyConn As SqlConnection 'declare connection
Dim MySQL as String 'store sql string
Sub Page_Load(sender as Object, e as EventArgs) 'for page load
MyConn = New SqlConnection("abcd") 'connects to intranet db
page_title.text="test" 'label on top of page
If Not Page.IsPostBack 'if not submit
BindData() 'first procedure to carry out
End If
End Sub
Sub BindData()
MySQL = "SELECT * FROM abctest" 'query to connect for abctest table
MyConn.Open() 'opens db connection
Dim strCmd as New SQLCommand(MySQL, MyConn) 'use conn and query to open table
Dim SQLDR as SQLDataReader ' declare a datareader
SQLDR = strCmd.ExecuteReader() 'translates info in open table
MyDataGrid.DataSource = SQLDR 'assign info to datagrid
MyDataGrid.DataBind() ' loads data
MyConn.Close() ' closes conn
End Sub
Sub MyDataGrid_Edit(sender As Object, e As DataGridCommandEventArgs) ' highlights the line you want to edit
MyDataGrid.ShowFooter = False 'hides footer
MyDataGrid.EditItemIndex = e.Item.ItemIndex ' display edititemtemplate
BindData() 'rebind data
End Sub
Sub MyDataGrid_Cancel(sender As Object, e As DataGridCommandEventArgs) ' highlights in reverse
MyDataGrid.ShowFooter = True 'shows footer
MyDataGrid.EditItemIndex = -1 'hides the edititemtemplate
BindData() 'rebinds
End Sub
Sub MyDataGrid_ItemDataBound(sender as Object, e as DataGridItemEventArgs) '
If e.Item.ItemType <> ListItemType.Header AND e.Item.ItemType <> ListItemType.Footer then ' if not header or footer, then its delete
Dim deleteButton as Button = e.Item.FindControl("Delete") 'button variable which is assigned values of delete button
deleteButton.Attributes.Add("onclick","return confirm('Are you sure you want to delete everything linked to this course?');")
End If
End Sub
Sub MyDataGrid_Delete(sender As Object, e As DataGridCommandEventArgs) '
MySQL = "DELETE from abctest where id = @Id" 'delete string
Dim DelCmd as New SQLCommand(MySQL, MyConn) 'executes connection string and delete query
DelCmd.Parameters.Add(New SqlParameter("@Id", MyDataGrid.DataKeys(e.Item.ItemIndex))) '
MyConn.Open()
DelCmd.ExecuteNonQuery()
MyConn.Close()
BindData()
End Sub
Sub MyDataGrid_Update(sender As Object, e As DataGridCommandEventArgs)
MySQL = "UPDATE [abctest] SET [name] = @name, [phone] = @phone WHERE [id] = @Id"
Dim UpCmd as New SQLCommand(MySQL, MyConn)
Dim Up_name As TextBox
Dim Up_phone As TextBox
Up_name = e.Item.FindControl("name")
Up_phone = e.Item.FindControl("phone")
UpCmd.Parameters.Add(New SqlParameter("@Id", MyDataGrid.DataKeys(e.Item.ItemIndex)))
UpCmd.Parameters.Add(New SQLParameter("@name", Up_name.Text ))
UpCmd.Parameters.Add(New SQLParameter("@phone", Up_phone.Text))
MyConn.Open()
UpCmd.ExecuteNonQuery()
MyDataGrid.ShowFooter = True
MyDataGrid.EditItemIndex = -1
MyConn.close()
BindData()
End Sub
Sub MyDataGrid_Add(sender as Object, e as DataGridCommandEventArgs)
If e.CommandName = "Insert" Then
MySQL = "INSERT INTO abctest (name,phone) VALUES (@name,@phone)"
Dim InCmd as New SQLCommand(MySQL, MyConn)
Dim In_name As TextBox
Dim In_phone As TextBox
In_name = e.Item.FindControl("add_name")
In_phone = e.Item.FindControl("add_phone")
InCmd.Parameters.Add(New SQLParameter("@name", In_name.text))
InCmd.Parameters.Add(New SQLParameter("@phone", In_phone.text))
MyConn.Open()
InCmd.ExecuteNonQuery()
MyConn.Close()
BindData()
End if
End Sub
Sub post_form(sender As Object, e As EventArgs)
page_title.text = ""
end sub
</script>
<form runat="server">
<asp:Label id="page_title" runat="server"
Font-Size="11"
Font-Name="Arial"
Font-Bold="true"
ForeColor="#00436c"
/>
<br />
<asp:DataGrid id="MyDataGrid" runat="server"
width="650"
GridLines="Horizontal"
BorderColor="#CCCCCC"
CellPadding="4"
CellSpacing="0"
BorderWidth="1"
Font-Name="Arial"
Font-Size="8"
AutoGenerateColumns="False"
OnEditCommand="MyDataGrid_Edit"
OnUpdateCommand="MyDataGrid_Update"
OnCancelCommand="MyDataGrid_Cancel"
OnDeleteCommand="MyDataGrid_Delete"
OnItemDataBound="MyDataGrid_ItemDataBound"
DataKeyField="id"
EditItemStyle-BackColor="#eeeeee"
OnItemCommand="MyDataGrid_Add"
ShowFooter="True"
>
<headerstyle Font-Size="8"
Font-Name="Arial"
Font-Bold="true"
BackColor="#CCCCCC"
VerticalAlign="top" />
<footerstyle Font-Size="8"
Font-Name="Arial"
BackColor="#FFFFFF"
VerticalAlign="top" />
<itemstyle Font-Size="8"
Font-Name="Arial"
VerticalAlign="top"
BackColor="#FFFFFF" />
<EditItemStyle Font-Size="8"
Font-Name="Arial"
VerticalAlign="top"
BackColor="#FFFFFF" />
<Columns>
<asp:TemplateColumn visible="false">
<FooterTemplate>
</FooterTemplate>
<ItemTemplate>
<%# Container.DataItem("id") %>
</ItemTemplate>
<EditItemTemplate>
<%# Container.DataItem("id") %>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Name">
<FooterTemplate>
<asp:TextBox ID="add_name"
Font-Size="8"
Font-Name="Arial"
Columns="40"
Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<%# Container.DataItem("name") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="name" Text='<%# Container.DataItem("name") %>'
Font-Size="8"
Font-Name="Arial"
Columns="40"
Runat="Server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Phone">
<FooterTemplate>
<asp:TextBox ID="add_phone"
Font-Size="8"
Font-Name="Arial"
Columns="40"
Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<%# Container.DataItem("phone") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="phone" Text='<%# Container.DataItem("phone") %>'
Font-Size="8"
Font-Name="Arial"
Columns="40"
Runat="Server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn EditText="Edit Info" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" />
<asp:TemplateColumn HeaderText="Delete">
<FooterTemplate>
<asp:Button CommandName="Insert" Text="Add" Runat="server" width="55"/>
</FooterTemplate>
<ItemTemplate>
<asp:Button CommandName="Delete" id="Delete" Text="Delete" Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Delete All">
<FooterTemplate>
</FooterTemplate>
<ItemTemplate>
<asp:CheckBox id="CheckBox1" value='<%# Container.DataItem("id") %>' runat="server" />
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
<table Border="0" cellspacing="0" cellpadding="5" width="600">
<tr>
<td align="center">
<asp:Button ID="Submit" text="Delete Checked"
OnClick="post_form"
Runat="server"
Font-Size="8"
Font-Name="Arial"
width="100"
/>
</td>
</tr>
</table>
</form>
Reply With Quote