GridView update with SqlDataSource

Question

I have the below but the update from gridview doesn't seem to work and throws this error:

Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@id".

I have added the datakeynames to the gridview and the update parameters to the SqlDataSource but still can't see what's wrong...

Doing a delete works fine.

I have tried reduced the update parameters one by one but still not found the cause

<asp:SqlDataSource ID="sqldsEvents" runat="server" 
     ConnectionString='<%$ ConnectionStrings:dbIPRadioConnectionString %>'
     SelectCommand="SELECT id, eventid, [start time], duration, active, [event desc] FROM tblEvents"
     UpdateCommand="UPDATE tblEvents SET [start time] = @starttime WHERE (id = @id)"
     InsertCommand="INSERT INTO tblEvents(eventid, [start time], duration, active, [event desc]) VALUES (@eventid, @starttime, @duration, @active, @eventdesc)"
     DeleteCommand="DELETE FROM tblEvents WHERE (id = @id)">
     <DeleteParameters>
         <asp:Parameter Name="id"></asp:Parameter>
     </DeleteParameters>
     <InsertParameters>
         <asp:Parameter Name="eventid"></asp:Parameter>
         <asp:Parameter Name="starttime"></asp:Parameter>
         <asp:Parameter Name="duration"></asp:Parameter>
         <asp:Parameter Name="active"></asp:Parameter>
         <asp:Parameter Name="eventdesc"></asp:Parameter>
     </InsertParameters>
     <UpdateParameters>
         <asp:Parameter Name="starttime"></asp:Parameter>
         <asp:Parameter Name="id"></asp:Parameter>
     </UpdateParameters>
 </asp:SqlDataSource>

<div class="col-sm-12 text-center">
    <asp:GridView ID="gvEvents" runat="server" DataKeyNames="id" 
         CssClass="table table-bordered" DataSourceID="sqldsEvents" 
         BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" 
         CellPadding="4" AutoGenerateColumns="False" 
         EmptyDataText="No Events Configured">
       <Columns>
           <asp:TemplateField ShowHeader="False">
               <EditItemTemplate>
                   <asp:LinkButton runat="server" Text="Update" CommandName="Update" CausesValidation="True" ID="LinkButton1"></asp:LinkButton>&nbsp;<asp:LinkButton runat="server" Text="Cancel" CommandName="Cancel" CausesValidation="False" ID="LinkButton2"></asp:LinkButton>
               </EditItemTemplate>
               <ItemTemplate>
                   <asp:LinkButton runat="server" Text="Edit" CommandName="Edit" CausesValidation="False" ID="LinkButton1"></asp:LinkButton>&nbsp;<asp:LinkButton runat="server" Text="Select" CommandName="Select" CausesValidation="False" ID="LinkButton2"></asp:LinkButton>&nbsp;<asp:LinkButton runat="server" Text="Delete" CommandName="Delete" CausesValidation="False" ID="LinkButton3"></asp:LinkButton>
               </ItemTemplate>
           </asp:TemplateField>

           <asp:BoundField DataField="id" HeaderText="id" ReadOnly="True" SortExpression="id"></asp:BoundField>
           <asp:BoundField DataField="eventid" HeaderText="eventid" SortExpression="eventid"></asp:BoundField>
           <asp:BoundField DataField="start time" HeaderText="start time" SortExpression="start time"></asp:BoundField>
           <asp:BoundField DataField="duration" HeaderText="duration" SortExpression="duration"></asp:BoundField>
           <asp:BoundField DataField="event desc" HeaderText="event desc" SortExpression="event desc"></asp:BoundField>
           <asp:TemplateField HeaderText="active" SortExpression="active">
               <EditItemTemplate>
                   <asp:DropDownList ID="DropDownList1" runat="server">
                       <asp:ListItem Text="0" Value="0"></asp:ListItem>
                       <asp:ListItem Text="1" Value="1"></asp:ListItem>
                   </asp:DropDownList>
               </EditItemTemplate>
               <ItemTemplate>
                   <asp:Label runat="server" Text='<%# Bind("active") %>' ID="Label1"></asp:Label>
               </ItemTemplate>
           </asp:TemplateField>
       </Columns>
</asp:GridView>

I don't think what you have shown could produce that error. Can you run a SQL Profiler trace and observe what command is actually hitting the database? — Crowcoder
– Crowcoder, Commented Jan 21, 2018 at 23:04

Tetsuya Yamamoto · Accepted Answer · 2018-01-22 01:56:52Z

The problem occurs because DataField attribute inside GridView's BoundField does not accept column names that using whitespaces.

Better to use underscores for DataField and SortExpression field/column names, also using underscore-based alias names for every columns containing whitespaces in SqlDataSource:

GridView control

<asp:GridView ID="gvEvents" ... >
     <%-- other fields --%>
     <asp:BoundField DataField="start_time" HeaderText="start time" SortExpression="start_time"></asp:BoundField>
     <asp:BoundField DataField="event_desc" HeaderText="event desc" SortExpression="event_desc"></asp:BoundField>
     <%-- other fields --%>
</asp:GridView>

SqlDataSource control

<asp:SqlDataSource ID="sqldsEvents" runat="server" ConnectionString='<%$ 
    ConnectionStrings:dbIPRadioConnectionString %>'
    SelectCommand="SELECT id, eventid, [start time] AS start_time, duration, active, [event desc] AS event_desc FROM tblEvents"
    <%-- other items --%>
</asp:SqlDataSource>

Furthermore, if you have access to modify column names in SSMS, changing all column names with whitespaces to underscores like this update command should work without hassle:

UpdateCommand="UPDATE tblEvents SET start_time = @starttime WHERE (id = @id)"

Note:

Column/field names in every table should follow database identifier convention to ensure CRUD commands are executed successfully.

Similar issue: incorrect syntax near nvarchar in gridview

Collectives™ on Stack Overflow

GridView update with SqlDataSource

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related