[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

When trying to delete from a gridview asp.net I get Line 1: Incorrect syntax near 'nvarchar'.

Hi,
I'm pretty much a newb to the vb asp.net and I'm currently pulling my hair out trying to solve the following problem.

I have a grid view that displays data from a SQL view.  I'm trying to get the gridview to delete a record in table the view is based on.

When I press the delete button I get "Line 1: Incorrect syntax near 'nvarchar'."

Can anyone offer any suggestions on how to resolve this?
<asp:GridView ID="gvwJobList" runat="server" AllowPaging="True" 
                AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Job Number" 
                DataSourceID="SQLSmart" CellPadding="4">
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                                <RowStyle  CssClass="TableRow" />
                <Columns>
                    <asp:CommandField ButtonType="Button" ShowSelectButton="True" 
                        ControlStyle-CssClass="ButtonStyleMaint" >
                    <ControlStyle CssClass="ButtonStyleMaint"></ControlStyle>
                    </asp:CommandField>
                    <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ControlStyle-CssClass="ButtonStyleMaint" />
                    <asp:BoundField DataField="Job Number" HeaderText="Job Number" ReadOnly="True" 
                        SortExpression="Job Number" />
                    <asp:BoundField DataField="Activity" HeaderText="Activity" 
                        SortExpression="Activity" />
                    <asp:BoundField DataField="Job Map" HeaderText="Job Map" 
                        SortExpression="Job Map" />
                </Columns>
                <PagerStyle CssClass="Divider2" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle CssClass="TableRowSelected" Font-Bold="True" />
                <HeaderStyle CssClass="Divider2" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
                <EditRowStyle CssClass="TableRowEdit" />
                <AlternatingRowStyle CssClass="TableRowAlternate" />
 
            </asp:GridView>
            <asp:SqlDataSource ID="SQLSmart" runat="server" 
                ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>" 
                
                SelectCommand="SELECT * FROM [vw_TSK_JobList] ORDER BY [Job Number], [Activity]" 
                DeleteCommand="Delete From TSK_JOB Where JobID=@JobID">
                <DeleteParameters>
                    <asp:ControlParameter ControlID="gvwJobList" Name="JobID" 
                        PropertyName="SelectedDataKey" Type="String" Size="50" />
                </DeleteParameters>
 
            </asp:SqlDataSource>

Open in new window

0
redoxsoft
Asked:
redoxsoft
  • 2
  • 2
2 Solutions
 
GiftsonDJohnCommented:
Hi,

The SelectedDataKey will have value only if the Row is selected. Else it will be blank. Probably this is the cause of the error.
0
 
redoxsoftAuthor Commented:
Hi thanks for the quick response.
As I don't want to have to select before deleting (ie pressing a select button first) how would I change the code so the delete button knows what record it's associated with?
0
 
GiftsonDJohnCommented:
You can use like this.
<asp:GridView ID="gvwJobList" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" DataKeyNames="Job Number" DataSourceID="SQLSmart"
            CellPadding="4">
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <RowStyle CssClass="TableRow" />
            <Columns>
                <asp:CommandField ButtonType="Button" ShowSelectButton="True" ControlStyle-CssClass="ButtonStyleMaint">
                    <ControlStyle CssClass="ButtonStyleMaint"></ControlStyle>
                </asp:CommandField>
                <asp:TemplateField ShowHeader="False">
                    <ItemTemplate>
                        <asp:Button ID="Button1" runat="server" CausesValidation="False" CommandName="Delete" OnCommand="gvwJobList_Delete"
                            Text="Delete" CommandArgument='<%#DataBinder.Eval(Container.DataItem,"Job Number") %>' />
                        <asp:SqlDataSource ID="SQLSmart" runat="server" ConnectionString="" SelectCommand="SELECT * FROM [vw_TSK_JobList] ORDER BY [Job Number], [Activity]"
                            DeleteCommand="Delete From TSK_JOB Where JobID=@JobID">
                            <DeleteParameters>
                                <asp:Parameter Name="JobID" Type="String" Size="50" />
                            </DeleteParameters>
                        </asp:SqlDataSource>
                    </ItemTemplate>
                    <ControlStyle CssClass="ButtonStyleMaint" />
                </asp:TemplateField>
                <asp:BoundField DataField="Job Number" HeaderText="Job Number" ReadOnly="True" SortExpression="Job Number" />
                <asp:BoundField DataField="Activity" HeaderText="Activity" SortExpression="Activity" />
                <asp:BoundField DataField="Job Map" HeaderText="Job Map" SortExpression="Job Map" />
            </Columns>
            <PagerStyle CssClass="Divider2" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle CssClass="TableRowSelected" Font-Bold="True" />
            <HeaderStyle CssClass="Divider2" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
            <EditRowStyle CssClass="TableRowEdit" />
            <AlternatingRowStyle CssClass="TableRowAlternate" />
        </asp:GridView>
 
 
 protected void gvwJobList_Delete(object sender, CommandEventArgs e)
    {
        if (e.CommandName.Equals("Delete"))
        {
            DataControlFieldCell cell = (DataControlFieldCell)((Button)sender).Parent;
 
            // Insert the new order            
 
            SqlDataSource s = (SqlDataSource)cell.FindControl("SQLSmart");
            s.DeleteParameters[0].DefaultValue = e.CommandArgument.ToString();
            s.Delete();
        }
    }

Open in new window

0
 
redoxsoftAuthor Commented:
It turns out that it's because the DataKeyNames is different to the parameter required for the delete statement.  By adding JobID to the view, and changing the grids DataKeyNames = "JobID", the delete statement works.
I also changed the delete parameter to it's default settings where no control is specified to provide the data.
<asp:GridView ID="gvwJobList" runat="server" AllowPaging="True" 
                AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="JobID" 
                DataSourceID="SQLSmart" CellPadding="4">
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                                <RowStyle  CssClass="TableRow" />
                <Columns>
                    <asp:CommandField ButtonType="Button" ShowSelectButton="True" 
                        ControlStyle-CssClass="ButtonStyleMaint" >
                    <ControlStyle CssClass="ButtonStyleMaint"></ControlStyle>
                    </asp:CommandField>
                    <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ControlStyle-CssClass="ButtonStyleMaint" />
                    <asp:BoundField DataField="Job Number" HeaderText="Job Number" ReadOnly="True" 
                        SortExpression="Job Number" />
                    <asp:BoundField DataField="Activity" HeaderText="Activity" 
                        SortExpression="Activity" />
                    <asp:BoundField DataField="Job Map" HeaderText="Job Map" 
                        SortExpression="Job Map" />
                </Columns>
                <PagerStyle CssClass="Divider2" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle CssClass="TableRowSelected" Font-Bold="True" />
                <HeaderStyle CssClass="Divider2" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
                <EditRowStyle CssClass="TableRowEdit" />
                <AlternatingRowStyle CssClass="TableRowAlternate" />
 
            </asp:GridView>
            <asp:SqlDataSource ID="SQLSmart" runat="server" 
                ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>" 
                
                SelectCommand="SELECT * FROM [vw_TSK_JobList] ORDER BY [Job Number], [Activity]" 
                DeleteCommand="Delete From TSK_JOB Where JobID=@JobID">
                <DeleteParameters>
                    <asp:Parameter Name="JobID" />
                </DeleteParameters>
 
            </asp:SqlDataSource>

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now