locked
GridView with FormView doesn't work RRS feed

  • Question

  • User1894502017 posted

    I have a FormView hooked up to a GridView. When I try to Insert from the FormView I get this error: "System.Data.SqlClient.SqlException: Incorrect syntax near '?'"

    Can anyone see where my error is? I've been over this code and over it but I don't see anything.

     

    1    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="AdminGems.aspx.vb" Inherits="AdminGems" %>
    2    
    3    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    4    <html xmlns="http://www.w3.org/1999/xhtml">
    5    <head runat="server">
    6        <title>Admin Page</title>
    7    </head>
    8    <body>
    9        <form id="form1" runat="server">
    10           <div>
    11               <h2>
    12                   Administer Gem Data</h2>
    13               <p>
    14                   <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Default.aspx">Home</asp:HyperLink></p>
    15               <p>
    16                   &nbsp;</p>
    17               <p>
    18                   <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSourceFormView"
    19                       AllowPaging="True" CellPadding="4" DataKeyNames="GemID">
    20                       <EditItemTemplate>
    21                           <table border="0" width="350" id="table1" cellspacing="0" cellpadding="1">
    22                               <tr>
    23                                   <td colspan="2">
    24                                       <b>Edit Record</b></td>
    25                                   <td>
    26                                       &nbsp;</td>
    27                               </tr>
    28                               <tr>
    29                                   <td width="150" style="height: 25px">
    30                                       Gem Name:</td>
    31                                   <td style="height: 25px">
    32                                       <asp:TextBox ID="gemNameTextBox" runat="server" Text='<%# Bind("GemName") %>'>
    33                                       </asp:TextBox></td>
    34                                   <td style="height: 25px">
    35                                       <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="gemNameTextBox"
    36                                           ErrorMessage="Required field">*</asp:RequiredFieldValidator></td>
    37                               </tr>
    38                               <tr>
    39                                   <td width="150" valign="top">
    40                                       Description
    41                                   </td>
    42                                   <td>
    43                                       <asp:TextBox ID="DescriptionOfChangeTextBox" runat="server" Height="40px" Width="200"
    44                                           Wrap="True" Text='<%# Bind("Description") %>'>
    45                                       </asp:TextBox></td>
    46                                   <td>
    47                                       <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="DescriptionOfChangeTextBox"
    48                                           ErrorMessage="Required field">*</asp:RequiredFieldValidator></td>
    49                               </tr>
    50                           </table>
    51                           <br />
    52                           <b>
    53                               <asp:Button ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
    54                                   Text="Save Changes"></asp:Button>
    55                               <asp:Button ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
    56                                   Text="Cancel"></asp:Button>
    57                           </b>
    58                       </EditItemTemplate>
    59                       <InsertItemTemplate>
    60                           <table border="0" width="350" id="table1" cellspacing="0" cellpadding="4">
    61                               <tr>
    62                                   <td colspan="2">
    63                                       <b>Add New Record:</b></td>
    64                                   <td>
    65                                       &nbsp;</td>
    66                               </tr>
    67                               <tr>
    68                                   <td width="150">
    69                                       Gem Name:</td>
    70                                   <td>
    71                                       <asp:TextBox ID="gemNameInsertTextBox" runat="server" Text='<%# Bind("GemName") %>'>
    72                                       </asp:TextBox></td>
    73                                   <td>
    74                                       <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="gemNameInsertTextBox"
    75                                           ErrorMessage="Required field">*</asp:RequiredFieldValidator></td>
    76                               </tr>
    77                               <tr>
    78                                   <td width="150" valign="top">
    79                                       Description</td>
    80                                   <td>
    81                                       <asp:TextBox ID="DescrChangeInsertTextBox" runat="server" Height="40px" Width="200"
    82                                           Wrap="True" Text='<%# Bind("Description") %>'>
    83                                       </asp:TextBox></td>
    84                                   <td>
    85                                       <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="DescrChangeInsertTextBox"
    86                                           ErrorMessage="Required field">*</asp:RequiredFieldValidator></td>
    87                               </tr>
    88                           </table>
    89                           <br />
    90                           <b>
    91                               <asp:Button ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
    92                                   Text="Create This Record"></asp:Button>
    93                               <asp:Button ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
    94                                   Text="Cancel"></asp:Button>
    95                           </b>
    96                       </InsertItemTemplate>
    97                       <ItemTemplate>
    98                           <table border="0" width="350" id="table1" cellspacing="0" cellpadding="4">
    99                               <tr>
    100                                  <td colspan="2">
    101                                      <b>Detail of Change:</b></td>
    102                                  <td>
    103                                      &nbsp;</td>
    104                              </tr>
    105                              <tr>
    106                                  <td width="150">
    107                                      Gem Name:</td>
    108                                  <td>
    109                                      <asp:Label ID="gemNameLabel" runat="server" Text='<%# Bind("GemName") %>'></asp:Label></td>
    110                                  <td>
    111                                      &nbsp;</td>
    112                              </tr>
    113                              <tr>
    114                                  <td width="150" valign="top">
    115                                      Description:</td>
    116                                  <td>
    117                                      <asp:Label ID="DescriptionOfChangeLabel" runat="server" Height="40px" Text='<%# Bind("Description") %>'
    118                                          Width="400"></asp:Label></td>
    119                                  <td>
    120                                      &nbsp;</td>
    121                              </tr>
    122                          </table>
    123                          <br />
    124                          <asp:Button ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
    125                              Text="Edit This Record"></asp:Button>
    126                          <asp:Button ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
    127                              Text="Delete This Record" OnClientClick="return confirm('Are you sure you want to this record?');">
    128                          </asp:Button>
    129                          &nbsp;&nbsp; &nbsp;
    130                      </ItemTemplate>
    131                  </asp:FormView>
    132                  &nbsp;&nbsp;</p>
    133              <p>
    134                  <asp:Button ID="AddNewButton" runat="server" Font-Bold="True" Text="Add New" />&nbsp;</p>
    135              <p>
    136                  <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourceGridView"
    137                      AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"
    138                      DataKeyNames="GemID">
    139                      <Columns>
    140                          <asp:CommandField ShowSelectButton="True" ButtonType="Button" SelectText="Edit" EditText=" Edit " />
    141                          <asp:BoundField DataField="GemID" HeaderText="GemID" InsertVisible="False" ReadOnly="True"
    142                              SortExpression="GemID" />
    143                          <asp:BoundField DataField="GemName" HeaderText="GemName" SortExpression="GemName" />
    144                          <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
    145                      </Columns>
    146                  </asp:GridView>
    147                  &nbsp;</p>
    148              <asp:SqlDataSource ID="SqlDataSourceFormView" runat="server" ConnectionString="<%$ ConnectionStrings:GemsDbConnectionString %>"
    149                  SelectCommand="SELECT GemID, GemName, Description FROM tblGems" InsertCommand="INSERT INTO tblGems (GemName, Description) VALUES (?, ?)"
    150                  UpdateCommand="UPDATE tblGems SET GemName = ? WHERE GemID = ? AND GemName = ?"
    151                  DeleteCommand="DELETE FROM tblGems WHERE GemID = ? AND GemName = ?">
    152                  <SelectParameters>
    153                      <asp:ControlParameter ControlID="FormView1" Name="GemID" PropertyName="SelectedValue"
    154                          Type="Int32" />
    155                  </SelectParameters>
    156                  <DeleteParameters>
    157                      <asp:Parameter Name="original_GemID" Type="Int32" />
    158                      <asp:Parameter Name="original_GemName" Type="String" />
    159                      <asp:Parameter Name="original_Description" Type="String" />
    160                  </DeleteParameters>
    161                  <UpdateParameters>
    162                      <asp:Parameter Name="GemName" Type="String" />
    163                      <asp:Parameter Name="Description" Type="String" />
    164                      <asp:Parameter Name="original_GemID" Type="Int32" />
    165                      <asp:Parameter Name="original_GemName" Type="String" />
    166                      <asp:Parameter Name="original_Description" Type="String" />
    167                  </UpdateParameters>
    168                  <InsertParameters>
    169                      <asp:Parameter Name="GemName" Type="String" />
    170                      <asp:Parameter Name="Description" Type="String" />
    171                  </InsertParameters>
    172              </asp:SqlDataSource>
    173              <asp:SqlDataSource ID="SqlDataSourceGridView" runat="server" ConnectionString="<%$ ConnectionStrings:GemsDbConnectionString %>"
    174                  SelectCommand="SELECT GemID, GemName, Description FROM tblGems" InsertCommand="INSERT INTO tblGems (GemName, Description) VALUES (?, ?)"
    175                  UpdateCommand="UPDATE tblGems SET GemName = ? WHERE GemID = ? AND GemName = ?"
    176                  DeleteCommand="DELETE FROM tblGems WHERE GemID = ? AND GemName = ?">
    177                  <DeleteParameters>
    178                      <asp:Parameter Name="original_GemID" Type="Int32" />
    179                      <asp:Parameter Name="original_GemName" Type="String" />
    180                      <asp:Parameter Name="original_Description" Type="String" />
    181                  </DeleteParameters>
    182                  <UpdateParameters>
    183                      <asp:Parameter Name="GemName" Type="String" />
    184                      <asp:Parameter Name="Description" Type="String" />
    185                      <asp:Parameter Name="original_GemID" Type="Int32" />
    186                      <asp:Parameter Name="original_GemName" Type="String" />
    187                      <asp:Parameter Name="original_Description" Type="String" />
    188                  </UpdateParameters>
    189                  <InsertParameters>
    190                      <asp:Parameter Name="GemName" Type="String" />
    191                      <asp:Parameter Name="Description" Type="String" />
    192                  </InsertParameters>
    193              </asp:SqlDataSource>
    194          </div>
    195      </form>
    196  </body>
    197  </html>
    198  
     
    The code behind:
    1    
    2    Partial Class AdminGems
    3        Inherits System.Web.UI.Page
    4    
    5        Protected Sub GridView1_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PageIndexChanged
    6            FormView1.PageIndex = GridView1.SelectedIndex
    7        End Sub
    8    
    9        Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
    10           FormView1.PageIndex = GridView1.SelectedIndex
    11       End Sub
    12   
    13       Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Sorted
    14           FormView1.PageIndex = GridView1.SelectedIndex
    15       End Sub
    16   
    17       Protected Sub FormView1_ItemDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewDeletedEventArgs) Handles FormView1.ItemDeleted
    18           GridView1.DataBind()
    19       End Sub
    20   
    21       Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted
    22           GridView1.DataBind()
    23       End Sub
    24   
    25       Protected Sub FormView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdatedEventArgs) Handles FormView1.ItemUpdated
    26           GridView1.DataBind()
    27       End Sub
    28   
    29       Protected Sub AddNewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles AddNewButton.Click
    30           FormView1.ChangeMode(FormViewMode.Insert)
    31       End Sub
    32   
    33       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    34           '
    35       End Sub
    36   End Class
    37   
    
     
     
    Saturday, August 4, 2007 7:43 AM

Answers

  • User187056398 posted

    You have question marks for parameters, they should look like this: @ParameterName:

    UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @CustomerName, [Status] = @Status, [CustomerDate] = @CustomerDate WHERE [ID] = @ID">
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 4, 2007 10:41 AM

All replies

  • User187056398 posted

    You have question marks for parameters, they should look like this: @ParameterName:

    UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @CustomerName, [Status] = @Status, [CustomerDate] = @CustomerDate WHERE [ID] = @ID">
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 4, 2007 10:41 AM
  • User1894502017 posted

    Thanks, that did it.

    The ?s worked with some old Access code I had - I assumed it would work with SQL Server

    Saturday, August 4, 2007 12:02 PM