Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls
Imports Microsoft.VisualBasic
Public Class links_cb
Inherits System.Web.UI.Page
Protected TypeDDL As DropDownList
Protected DataGrid1 As DataGrid
Protected Message As Label
Dim ConnectionString As String = "server=(local);database=helpdesk;trusted_connection=true"
Dim SelectCommand As String
Dim SelectFilter As String = "SELECT DISTINCT LinkType FROM tblWebLink"
Dim isEditing As Boolean = False
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
BindGrid()
BindTypeDDL()
End If
End Sub
Sub BindGrid()
Dim myConnection As New SqlConnection(ConnectionString)
If ViewState("isFilter") = "True" Then
SelectCommand = "SELECT * FROM tblWebLink WHERE LinkType LIKE '" & ViewState("strFilter") & "'"
Else
SelectCommand = "SELECT * FROM tblWebLink ORDER BY LinkType"
End If
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
Sub BindTypeDDL()
Dim objConnection As New SqlConnection(ConnectionString)
Dim objCommand As SqlCommand = New SqlCommand(SelectFilter, objConnection)
Dim objAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
Dim objDataSet As DataSet = New DataSet()
objAdapter.Fill(objDataSet)
TypeDDL.DataSource = objDataSet
TypeDDL.DataBind()
objConnection.Close()
TypeDDL.Items.Insert(0, new ListItem("Filter Type"))
End Sub
Sub FilterDG_Change(sender As Object, e As EventArgs)
ViewState("strFilter") = TypeDDL.SelectedItem.Text
If ViewState("strFilter") = "Filter Type" Then
exit sub
Else
ViewState("isFilter") = "True"
BindGrid()
End If
End Sub
Sub resetFilter_Click(sender As Object, e As EventArgs)
ViewState("isFilter") = "False"
TypeDDL.SelectedIndex = 0
BindGrid()
End Sub
Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)
CheckIsEditing(e.CommandName)
End Sub
Sub CheckIsEditing(commandName As String)
If DataGrid1.EditItemIndex <> -1 Then
If commandName <> "Cancel" And commandName <> "Update" Then
Message.Text = "Your changes have not been saved yet. Please press update to save your changes, or cancel to discard your changes, before selecting another item."
isEditing = True
End If
End If
End Sub
Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
If Not isEditing Then
DataGrid1.EditItemIndex = e.Item.ItemIndex
BindGrid()
End If
End Sub
Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
Dim Linkid As Integer = Ctype(e.Item.Cells(2).Text, integer)
Dim linkTypeTxt As String = Replace(CType(e.Item.Cells(3).Controls(0), TextBox).Text, "'", "''")
Dim linkURLTxt2 As String = (CType(e.Item.FindControl("LinkURLTxtBox"), TextBox)).Text
Dim LinkDescTxt2 As String = Replace((CType(e.Item.FindControl("LinkDescBox"), TextBox)).Text, "'", "''")
Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection
If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO tblWebLink(LinkURL, LinkDesc, LinkType) VALUES (@linkURL, @LinkDesc, @LinkType)"
Else
UpdateCommand.CommandText = "UPDATE tblWebLink SET LinkType = @linkType, LinkURL = @linkURL, LinkDesc = @LinkDesc WHERE LinkID = @id"
End If
UpdateCommand.Parameters.Add("@id", SqlDbType.int, 4).Value = Linkid
UpdateCommand.Parameters.Add("@linkType", SqlDbType.VarChar, 50).Value = linkTypeTxt
UpdateCommand.Parameters.Add("@linkURL", SqlDbType.VarChar,100).Value = linkURLTxt2
UpdateCommand.Parameters.Add("@LinkDesc", SqlDbType.NText).Value = LinkDescTxt2
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()
Catch ex as Exception
Message.Text = ex.ToString()
Finally
myConnection.Close()
End Try
' Resort the grid for new records
If AddingNew = True Then
DataGrid1.CurrentPageIndex = 0
AddingNew = false
End If
' rebind the grid
DataGrid1.EditItemIndex = -1
BindGrid()
BindTypeDDL()
End Sub
Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
DataGrid1.EditItemIndex = -1
BindGrid()
AddingNew = False
End Sub
Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
If Not isEditing Then
Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))
Dim myConnection As New SqlConnection(ConnectionString)
Dim DeleteCommand As New SqlCommand("DELETE FROM tblWebLink WHERE LinkID='" & keyValue & "'", myConnection)
Try
myConnection.Open()
DeleteCommand.ExecuteNonQuery()
Catch ex as Exception
message.Text = ex.ToString()
Finally
myConnection.Close()
End Try
DataGrid1.CurrentPageIndex = 0
DataGrid1.EditItemIndex = -1
BindGrid()
End If
End Sub
Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
If Not isEditing Then
DataGrid1.EditItemIndex = -1
DataGrid1.CurrentPageIndex = e.NewPageIndex
BindGrid()
End If
End Sub
Sub AddNew_Click(Sender As Object, E As EventArgs)
CheckIsEditing("")
If Not isEditing = True Then
AddingNew = True
Dim myConnection As New SqlConnection(ConnectionString)
If ViewState("isFilter") = "True" Then
SelectCommand = "SELECT * FROM tblWebLink WHERE LinkType LIKE '" & ViewState("strFilter") & "'"
Else
SelectCommand = "SELECT * FROM tblWebLink ORDER BY LinkType"
End If
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
' add a new blank row to the end of the data
Dim strType As String
If ViewState("strFilter") Is Nothing Then
strType = ""
Else
strType = ViewState("strFilter")
End If
Dim rowValues As Object() = {999, strType, "", ""}
ds.Tables(0).Rows.Add(rowValues)
' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count
If recordCount > 1 Then
recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
End If
' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End If
End Sub
Property AddingNew() As Boolean
Get
Dim o As Object = ViewState("AddingNew")
If o Is Nothing Then
Return False
End If
Return CBool(o)
End Get
Set(ByVal Value As Boolean)
ViewState("AddingNew") = Value
End Set
End Property
End Class
