title: Must Declare Scalar Variable when it is already defined, and Sytax error near 'nvarchar' when updating
body: <p>Solution at the bottom.</p>
  
  <p>I receive the following error when trying to update records: </p>
  
  <blockquote>
    <p>Must declare Scalar Variable @PaymentTermID</p>
  </blockquote>
  
  <p>Problem is, <code>@PaymentTermID</code> should be defined already in the Update parameters.  The 2 functions for <code>onrowdatabound</code> and <code>onrowupdate</code> are tweaked versions of the ones found here for the DropDownList: <a href="http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridviewrow.dataitem.aspx" rel="nofollow">GridViewRow.DataItem Property</a>.  Delete works fine.</p>
  
  <p>No new lines are added, just names changed around.  Any help would be appreciated, I am not exactly used to working with asp objects.</p>
  
  <pre><code>&lt;asp:GridView ID="GridView1" onrowdatabound="GridView1_RowDataBound" onrowupdating="GridView1_RowUpdating" runat="server" AutoGenerateColumns="False"   DataKeyNames="PaymentTermID" DataSourceID="SqlDataSource1" CellPadding="1"  CellSpacing="2"&gt;
      &lt;Columns&gt;
          &lt;asp:CommandField ShowEditButton="True" /&gt;
          &lt;asp:TemplateField HeaderText="Key Contract Date" SortExpression="Key Contract Date"&gt;
              &lt;EditItemTemplate&gt;
                  &lt;asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Key_Contract_Date" DataValueField="Key_Contract_Date" /&gt;
                  &lt;asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="&lt;%$ ConnectionStrings:Production_DatabaseConnectionString %&gt;" 
                              SelectCommand="SELECT DISTINCT [Key Contract Date] AS Key_Contract_Date FROM [tblPRJ_PaymentTerms]" /&gt;
              &lt;/EditItemTemplate&gt;
              &lt;ItemTemplate&gt;
                  &lt;asp:TextBox ID="TextBox1" runat="server" Text='&lt;%# Bind("[Key Contract Date]") %&gt;' /&gt;
              &lt;/ItemTemplate&gt;
          &lt;/asp:TemplateField&gt;
          &lt;asp:BoundField DataField="Due Date" HeaderText="Due Date" SortExpression="Due Date" /&gt;
          &lt;asp:BoundField DataField="Percent Due" HeaderText="Percent Due" SortExpression="Percent Due" /&gt;
          &lt;asp:BoundField DataField="Custom Description" HeaderText="Custom Description" SortExpression="Custom Description" /&gt;
      &lt;/Columns&gt;
      &lt;HeaderStyle Font-Bold="True" ForeColor="#666666" /&gt;
  &lt;/asp:GridView&gt;
  &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="&lt;%$ ConnectionStrings:Production_DatabaseConnectionString %&gt;" 
          SelectCommand="SELECT * FROM [tblPRJ_PaymentTerms] WHERE ([Job Number] = @Job_Number)" 
          DeleteCommand="DELETE FROM [tblPRJ_PaymentTerms] WHERE [PaymentTermID] = @PaymentTermID" 
          InsertCommand="INSERT INTO [tblPRJ_PaymentTerms] ([Job Number], [Key Contract Date], [Due Date], [Percent Due], [Custom Description]) VALUES (@Job_Number, @Key_Contract_Date, @Due_Date, @Percent_Due, @Custom_Description)" 
          UpdateCommand="UPDATE [tblPRJ_PaymentTerms] SET [Job Number] = @Job_Number, [Key Contract Date] = @Key_Contract_Date, [Due Date] = @Due_Date, [Percent Due] = @Percent_Due, [Custom Description] = @Custom_Description WHERE [PaymentTermID] = @PaymentTermID"&gt; 
      &lt;DeleteParameters&gt;
          &lt;asp:Parameter Name="PaymentTermID" Type="Int32" /&gt;
      &lt;/DeleteParameters&gt;
      &lt;InsertParameters&gt;
          &lt;asp:Parameter Name="Job_Number" Type="String" /&gt;
          &lt;asp:Parameter Name="Key_Contract_Date" Type="String" /&gt;
          &lt;asp:Parameter Name="Due_Date" Type="DateTime" /&gt;
          &lt;asp:Parameter Name="Percent_Due" Type="Decimal" /&gt;
          &lt;asp:Parameter Name="Custom_Description" Type="String" /&gt;
      &lt;/InsertParameters&gt;
      &lt;SelectParameters&gt;
          &lt;asp:ControlParameter ControlID="txtCurrentJobNumber" Name="Job_Number" PropertyName="Text" Type="String" /&gt;
      &lt;/SelectParameters&gt;
      &lt;UpdateParameters&gt;
          &lt;asp:Parameter Name="Job_Number" Type="String" /&gt;
          &lt;asp:Parameter Name="Key_Contract_Date" Type="String" /&gt;
          &lt;asp:Parameter Name="Due_Date" Type="DateTime" /&gt;
          &lt;asp:Parameter Name="Percent_Due" Type="Decimal" /&gt;
          &lt;asp:Parameter Name="Custom_Description" Type="String" /&gt;
          &lt;asp:Parameter Name="PaymentTermID" Type="Int32" /&gt;
      &lt;/UpdateParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>SOLUTION:  There were multiple problems happening here.  First and foremost, GuthMD was correct in his assessment of parameters needing a reference in terms of either a boundfield, templatefield, or other source (such as a control in the case of a control parameter).  Simply creating an <code>asp:boundfield</code> for the PaymentTermID and setting the <code>Visible</code> property to false fixed the problem I posted about.</p>
  
  <p>The other problem was that the database was setup poorly, and had spaces in column names.  The OLEDB driver doesn't like that, and causes errors when you try to write back to the database and you have spaces in column names (even if it's encased in brackets []).  After fixing the names in SQL, then revisiting our code and rewriting most of the SQL code for it, things started behaving as expected.</p>
  
  <p>Thanks again for your help.</p>