Sunday, March 11, 2012

Datagrid Delete

Hi I'm having a problem deleting rows from my datagrid. Basically I hit delete and a message box pops up and asks if Im sure I want to delete so I hit yes and then I get the following error -->Could not find stored procedure 'delete from SECTION_TBL where SECT_ID = @.SECT_ID'.

Is it my code thats wrong or is our test sql server that is the problem?

1<%@. Page Language="VB" EnableEventValidation="True" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>2<%@. import namespace="System" %>3<%@. import namespace="System.Data" %>4<%@. import namespace="System.Data.SqlClient" %>56<script language="VB" runat="server">78Dim sectionAs String9 Dim myconnectionAs SqlConnection10Dim mydaAs SqlDataAdapter11Dim dsAs DataSet1213Sub Page_Load(ByVal SourceAs Object,ByVal EAs EventArgs)14 BindData()15End Sub1617 Sub BindData()1819Dim strConnAs String ="server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS"20Dim sqlAs String ="Select * from SECTION_TBL"21 myconnection =New SqlConnection(strConn)22 myda =New SqlDataAdapter(sql, myconnection)23 ds =New DataSet24 myda.Fill(ds,"SECTION_TBL")25 sectList.DataSource = ds26 sectList.DataBind()2728End Sub2930 Private Sub sectList_ItemDataBound(ByVal senderAs Object,ByVal eAs DataGridItemEventArgs)Handles sectList.ItemDataBound3132Dim lAs LinkButton3334If e.Item.ItemType = ListItemType.ItemOr e.Item.ItemType = ListItemType.AlternatingItemThen35 l =CType(e.Item.Cells(0).FindControl("cmdDel"), LinkButton)36 l.Attributes.Add("onclick","return getconfirm();")37End If3839 End Sub4041 Sub sectList_DeleteCommand(ByVal sAs Object,ByVal eAs DataGridCommandEventArgs)4243Dim ConnectionStrAs String = ConfigurationManager.AppSettings("ConnStr")44Dim connAs SqlConnection45Dim cmdAs SqlCommand46Dim IdAs Integer4748 Id =CInt(e.Item.Cells(0).Text)49 conn =New SqlConnection("server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS")50 cmd =New SqlCommand("delete from SECTION_TBL where SECT_ID = @.SECT_ID", conn)51 cmd.CommandType = CommandType.StoredProcedure52 cmd.Parameters.Add("@.SECT_ID", SqlDbType.Int).Value = Id5354 cmd.Connection.Open()55 cmd.ExecuteNonQuery()56 cmd.Connection.Close()5758 DataBind()5960End Sub61626364</script>6566<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">6768<script language="javascript">6970function getconfirm()71{72if (confirm("Do you want to delete record?")==true)73return true;74else75return false;76}7778</script>7980<table cellpadding="2" cellspacing="2" width="760">81<tr>82<td>Sections</td>83</tr>84<tr>85<td>8687<asp:DataGrid OnDeleteCommand="sectList_DeleteCommand" ID="sectList" runat="server" DataKeyField="SECT_ID" AutoGenerateColumns="False">8889<Columns>9091<asp:BoundColumn DataField="SECT_ID" Visible="False" />9293<asp:HyperLinkColumn HeaderText="SECTION NAME" DataTextField="SECT_NAME" DataNavigateUrlField="SECT_ID" DataNavigateUrlFormatString="manageSection.aspx?SECT_ID={0}" />9495<asp:TemplateColumn>96<ItemTemplate>97<asp:LinkButton id="cmdDel" runat="server" Text="Delete" CommandName="Delete" CausesValidation="false" />98</ItemTemplate>99</asp:TemplateColumn>100101</Columns>102103</asp:DataGrid>104105</td>106</tr>107<tr>108<td></td>109</tr>110</table>111112</asp:Content>
 
Thanks in advance.

on line 51 you should use

CommandType.Text

|||

Brilliant - This works great

Thank you.