locked
Syntax error in UPDATE statement for TemplateField Gridview RRS feed

  • Question

  • User1084825142 posted

    I was wondering if there is any way to view an sql statement that is created by a templateField Gridview when updating? I had my Gridview working properly then I changed my update statement to update less fields and I changed a couple more things on the page and now I don't know what broke it. The error I'm getting is

    "System.Data.OleDb.OleDbException: Syntax error in UPDATE statement."

    When I searched for this error it said to debug it but all that happens when I do that is visual studio tells me there were errors because it couldn't find my header, which it does find when it runs regularly so this isn't the problem.

    Wednesday, September 1, 2010 12:45 PM

Answers

  • User77042963 posted

    You have a typo in your query:

    ... [roomName] = @roomName, WHERE conferenceID = @conferenceID

     

    Remove the extra comma in front of WHERE. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 1, 2010 1:26 PM

All replies

  • User77042963 posted

    You need to show your code in question here. Thanks. 

    Wednesday, September 1, 2010 12:55 PM
  • User1084825142 posted

    Well it's 200 lines between the AccessDataSource and the Gridview so I thought I'd just see if there was a way to view the executed sql statement instead but here goes I guess... I tried to pull out anything that might be sensitive information but if anyone sees something I missed please let me know so I can edit it.


                <asp:AccessDataSource DataFile="..."
                    ID="dataSource" runat="server" SelectCommand="SELECT TOP 1 * FROM conferences WHERE conferenceID = @conferenceID"
                    UpdateCommand="UPDATE [conferences] SET [start] = @start, [finish] = @finish, [rate] = @rate, [guestPass] = @guestPass, [roomName] = @roomName, WHERE conferenceID = @conferenceID">
                    <UpdateParameters>
                        <asp:Parameter Type="Datetime" Name="start"></asp:Parameter>
                        <asp:Parameter Type="Datetime" Name="finish"></asp:Parameter>
                        <asp:Parameter Type="Int32" Name="rate"></asp:Parameter>
                        <asp:Parameter Type="String" Name="guestPass"></asp:Parameter>
                        <asp:Parameter Type="String" Name="roomName"></asp:Parameter>
                        <asp:Parameter Type="Int32" Name="conferenceID"></asp:Parameter>
                    </UpdateParameters>
                    <SelectParameters>
                        <asp:QueryStringParameter QueryStringField="conferenceID" Name="conferenceID" Type="Int32" />
                    </SelectParameters>
                </asp:AccessDataSource>
                <asp:GridView ID="gridview1" AutoGenerateColumns="False" runat="server" DataSourceID="datasource"
                    DataKeyNames="conferenceID" BorderWidth="1px" BackColor="White" CellPadding="0"
                    BorderStyle="Solid" BorderColor="#6B93B9" HorizontalAlign="Center" PageSize="20"
                    GridLines="Vertical">
                    <FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
                    <PagerStyle ForeColor="White" HorizontalAlign="Center" BackColor="#6B93B9"></PagerStyle>
                    <HeaderStyle ForeColor="White" Font-Bold="True" BackColor="#6B93B9" Height="36px">
                    </HeaderStyle>
                    <Columns>
                        <asp:CommandField ControlStyle-CssClass="center" ControlStyle-Width="75px" ShowEditButton="True">
                            <ControlStyle CssClass="center" Width="75px"></ControlStyle>
                        </asp:CommandField>
                        <asp:TemplateField HeaderText="User Information">
                            <ItemTemplate>
                                <table style="border-spacing: 0px;">
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Room Name:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <%# Eval("roomName")%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Guest Pass:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <%# Eval("guestPass")%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            User ID:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <%# Eval("userID")%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Start Date:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <%# CStr(Eval("start")).Substring(0, CStr(Eval("start")).IndexOf(" "))%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Start Time:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <%# CStr(Eval("start")).substring(CStr(Eval("start")).IndexOf(" ") + 1) %>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Finish Date/Time:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <%# Eval("finish")%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Rate:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            $<%# Eval("rate")%>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Status:
                                        </td>
                                        <td id="status1" style="background-color: #EAEAFF; padding: 5px;">
                                            <%# Eval("status") %>
                                        </td>
                                    </tr>
                                </table>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <table style="border-spacing: 0px;">
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Room Name:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <asp:TextBox ID="editRoomName" runat="server" Text='<%# Bind("roomName") %>'></asp:TextBox>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Guest Pass:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <asp:TextBox ID="editPass" runat="server" Text='<%# Bind("guestPass") %>'></asp:TextBox>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            User ID:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <%# Eval("userID") %>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Start Date:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <asp:Label ID="editDate1" runat="server" Text='<%# CStr(Eval("start")).substring(0,CStr(Eval("start")).IndexOf(" ")) %>'></asp:Label>
                                            <asp:TextBox ID="editStart" CssClass="hideMe" runat="server" Text='<%# Bind("start") %>'></asp:TextBox>
                                            <a href="javascript:showCal('<%# DirectCast(Container, GridViewRow).FindControl("editDate1").ClientID %>','<%# DirectCast(Container, GridViewRow).FindControl("editStart").ClientID %>')"><img class="icon" alt="edit start date" src="images/calendar.gif" /></a>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Start Time:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <asp:Label ID="editTime1" runat="server" Text='<%# CStr(Eval("start")).substring(CStr(Eval("start")).IndexOf(" ")+1) %>'></asp:Label>
                                            <a href="javascript:showClock('<%# DirectCast(Container, GridViewRow).FindControl("editTime1").ClientID %>','<%# DirectCast(Container, GridViewRow).FindControl("editStart").ClientID %>')"><img class="icon" alt="edit start time" src="images/clock.gif" /></a>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Finish Date/Time:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <asp:Label ID="editFinish1" runat="server" Text='<%# Bind("finish") %>'></asp:Label>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Create Date:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <%# Eval("createDate") %>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #EAEAFF; padding: 5px;">
                                            Rate:
                                        </td>
                                        <td style="background-color: #EAEAFF; padding: 5px;">
                                            <asp:TextBox ID="editRate" runat="server" Text='<%# Bind("rate") %>'></asp:TextBox>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="border-right: 1px solid #6B93B9; background-color: #FFFFFF; padding: 5px;">
                                            Status:
                                        </td>
                                        <td style="background-color: #FFFFFF; padding: 5px;">
                                            <asp:TextBox ID="editStatus" ReadOnly="true" runat="server" Text='<%# Eval("status") %>'></asp:TextBox>
                                        </td>
                                    </tr>
                                </table>
                            </EditItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True" BackColor="#009999"></SelectedRowStyle>
                    <RowStyle ForeColor="#003399" BackColor="White"></RowStyle>
                </asp:GridView>

    Wednesday, September 1, 2010 1:04 PM
  • User77042963 posted

    You have a typo in your query:

    ... [roomName] = @roomName, WHERE conferenceID = @conferenceID

     

    Remove the extra comma in front of WHERE. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 1, 2010 1:26 PM
  • User1084825142 posted

    I guess after 4 hours of stairing at code anything can slip through the cracks! Thanks, working beautifully.


    Wednesday, September 1, 2010 2:05 PM