I want to use a client side delete confirmation box with an editable
datagrid. I've tried various available examples and they work fine.
When I try to incorporate into my own datagrid I receive a
compilation error "specified cast is not valid" in my Sub
DataGrid_ItemDataBound at line, Dim deleteButton as LinkButton =
e.Item.Cells(0).Controls(0). Any ideas on where the problem might be?
My code and datagrid html follow:
<%@ Page Language="vb" Explicit="true" Trace="false" Debug="true "
smartnavigation="true" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Dim ConnectionString As String =
ConfigurationSettings.AppSettings ("strConnect")
Dim SelectCommand As String = "StorProc_ValidateNewUser"
Dim SelectCommand2 as string = "Select * from tblPerson"
Dim myConnection As New SqlConnection(ConnectionString)
Dim ddlDataset as Dataset = New DataSet()
Dim ddlDataset2 as Dataset = New DataSet()
Dim ddlDataset3 as Dataset = New DataSet()
Dim isEditing As Boolean = False
Sub Page_Load(Sender As Object, E As EventArgs)
TransferBox.text = Session("PersonKey")
AuthorityBox.text = Session("AuthorityKey")
If Not Page.IsPostBack Then
BindGrid()
Else
End If
End Sub
' --------------------------------------------------------------
-
' DataGrid Commands: Page, Sort, Edit,
Update, Cancel, Delete
'
Sub DataGrid_ItemCommand(Sender As Object, E As
DataGridCommandEventArgs)
' this event fires prior to all of the other commands
' use it to provide a more graceful transition out of edit mode
CheckIsEditing(e.CommandName)
End Sub
'---------------------------------------
Sub CheckIsEditing(commandName As String)
If DataGrid1.EditItemIndex <> -1 Then
' we are currently editing a row
If commandName <> "Cancel" And commandName <> "Update" Then
' user's edit changes (If any) will not be committed
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)
' turn on editing for the selected row
If Not isEditing Then
DataGrid1.EditItemIndex = e.Item.ItemIndex
BindGrid()
End If
End Sub
'--------------------------------------------------
Function DoFillState() as Dataset ' fill the Authority
dropdownbox
Const strSQL as string = "Select AuthorityKey,
AuthorityLevel, AuthorityLevelDescription from tblLkupAuthority"
Dim MyDataAdapter as SQLDataAdapter = New SqlDataAdapter
(strSQL, myConnection)
MyDataAdapter.Fill(ddlDataSet2, "Authority")
Return ddlDataSet2
End Function
'--------------------------------------------------
--------------
Function GetSelIndex2(CatID as String) as Integer
Dim iLoop as Integer
'Loop through each row in the DataSet
Dim dt as DataTable = ddlDataSet2.Tables("Authority")
For iLoop = 0 to dt.Rows.Count - 1
If Int32.Parse(CatID) = Int32.Parse(dt.Rows(iLoop)
("AuthorityKey")) then
Return iLoop
End If
Next iLoop
End Function
'-------------------------------------------------
Sub DataGrid_Update(Sender As Object, E As
DataGridCommandEventArgs)
' update the database with the new values
''Determine what choices were selected in dropdowns
Dim strAuthorityID as string
Dim strApprovedBy as string
strApprovedBy= transferbox.text
strAuthorityID = CType(e.Item.FindControl
("LstAuthority"), DropDownList).SelectedItem.Value
Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection
UpdateCommand.CommandText = "UPDATE tblPerson SET
ApprovedDate=@ApprovedDate, ApprovedBy=@ApprovedBy,
AuthorityKey=@AuthorityKey WHERE PersonKey =" & DataGrid1.DataKeys
(CInt(E.Item.ItemIndex))
UpdateCommand.Parameters.Add(New SqlParameter
("@AuthorityKey", SqlDbType.real, 4))
UpdateCommand.Parameters("@AuthorityKey").Value =
Int32.Parse(strAuthorityID)
UpdateCommand.Parameters.Add(New SqlParameter
("@ApprovedBy", SqlDbType.real, 4))
UpdateCommand.Parameters("@ApprovedBy").Value =
Int32.Parse(strApprovedBy)
UpdateCommand.Parameters.Add(New SqlParameter
("@ApprovedDate", SqlDbType.datetime, 8))
UpdateCommand.Parameters("@ApprovedDate").Value = Today
()
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()
Catch ex as Exception
Message.Text = ex.ToString()
Finally
myConnection.Close()
End Try
' rebind the grid to display from the modified datatable
DataGrid1.EditItemIndex = -1
BindGrid()
End Sub
'----------------------------------------------
Sub DataGrid_Cancel(Sender As Object, E As
DataGridCommandEventArgs)
' cancel editing
DataGrid1.EditItemIndex = -1
BindGrid()
End Sub
'----------------------------------------
Sub DataGrid_Delete(Sender As Object, E As
DataGridCommandEventArgs)
' delete the selected row
If Not isEditing Then
' the key value for this row is in the DataKeys collection
Dim keyValue As String = CStr(DataGrid1.DataKeys
(e.Item.ItemIndex))
Dim myConnection As New SqlConnection(ConnectionString)
Dim DeleteCommand As New SqlCommand("DELETE from tblPerson
where PersonKey='" & keyValue & "'", myConnection)
'execute the command. This does the actual deletion
myConnection.Open()
DeleteCommand.ExecuteNonQuery()
myConnection.Close()
' rebind the grid to show the modified display from the datatable
DataGrid1.CurrentPageIndex = 0
DataGrid1.EditItemIndex = -1
BindGrid()
End If
End Sub
'--------------------------------------
Sub DataGrid_Page(Sender As Object, E As
DataGridPageChangedEventArgs)
' display a new page of data
If Not isEditing Then
DataGrid1.EditItemIndex = -1
DataGrid1.CurrentPageIndex = e.NewPageIndex
BindGrid()
End If
End Sub
'----------------------------------------------------
-----
Sub BindGrid() 'this does all of the databinding back to the
datagrid
AuthorityBox.text = 8
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand,
myConnection)
MyCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter
("@AuthorityKey",SQLDbType.real, 4))
MyCommand.SelectCommand.Parameters
("@AuthorityKey").value = AuthorityBox.text
Dim ds As New DataSet()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
'---------------------------
Sub DataGrid_ItemDataBound(sender as Object, e as
DataGridItemEventArgs)
'
If e.Item.ItemType <> ListItemType.Header AND
e.Item.ItemType <> ListItemType.Footer then
Dim deleteButton as LinkButton = e.Item.Cells
(0).Controls(0)
deleteButton.Attributes("onclick")
= "javascript:return confirm('Are you sure you want to delete the
Applicant ?')"
End If
End Sub
--------------------------
<asp:datagrid id="DataGrid1"
runat="server"
width ="950"
font-size="8"
CellSpacing="1"
GridLines="None"
CellPadding="2"
BackColor="White"
ForeColor="Black"
AllowPaging="true"
OnItemDataBound="DataGrid_ItemDataBound"
OnDeleteCommand="DataGrid_Delete"
OnCancelCommand="DataGrid_Cancel"
OnUpdateCommand="DataGrid_Update"
OnEditCommand="DataGrid_Edit"
OnItemCommand="DataGrid_ItemCommand"
DataKeyField="PersonKey"
AutoGenerateColumns="false"
HorizontalAlign="Center"
PageSize="6"
OnPageIndexChanged="DataGrid_Page">
<FooterStyle backcolor="#C6C3C6"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White"
horizontalalign="center" backcolor="#4A3C8C"></HeaderStyle>
<PagerStyle font-size="Smaller" font-names="Arial"
horizontalalign="Right" backcolor="#C6C3C6" mode="NextPrev"
PrevPageText="Prev" NextPageText="Next" ></PagerStyle>
<ItemStyle backcolor="#DEDFDE" ></ItemStyle>
<Columns>
<asp:HyperlinkColumn HeaderText="Click for Details"
ItemStyle-width="10%"
DataNavigateUrlField="PersonKey"
DataNavigateUrlFormatString="/webassoc/DisplayProfile.aspx?PersonKey=
{0}" Text="View Details"/>
<asp:EditCommandColumn ButtonType="PushButton"
UpdateText="Save" CancelText="Cancel" EditText="Edit">
<ItemStyle font-size="8" width="5%"></ItemStyle>
</asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete Company"
CommandName="Delete"/>
<asp:TemplateColumn HeaderText="Name" >
<ItemTemplate>
<%# DataBinder.Eval
(Container.DataItem, "FullName") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Company">
<ItemTemplate>
<%# DataBinder.Eval
(Container.DataItem, "CompanyName") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Title">
<ItemTemplate>
<%# DataBinder.Eval
(Container.DataItem, "Title") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Role">
<ItemTemplate>
<%# DataBinder.Eval
(Container.DataItem, "Role") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Authority Level">
<ItemTemplate>
<%# Databinder.Eval
(Container.DataItem, "AuthorityLevel") %>
</ItemTemplate>
<EditItemTemplate>
<asp:dropdownlist runat="server"
id="LstAuthority" font-size="8" Font-Names="Arial" width="200"
datavaluefield= "AuthorityKey"
datatextfield="AuthorityLevel"
datasource="<%# DoFillState()%>"
SelectedIndex='<%# GetSelIndex2
(Container.DataItem("AuthorityKey")) %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>