Click here to Skip to main content
13,297,547 members (49,224 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hello all,
If anyone could help me out with this annoying problem I would be extremely grateful. I have got a GridView in my ASP.NET page which takes data from a database table called "orderlines", with the goal of letting the user edit the data and save the changes to the database. The "Delete" link works fine, deleting the row of data from the table, but when I try to make a change and click update I always get the error message
"Must declare the scalar variable "@appname"."
Can anyone tell me why this is happening and suggest a fix please? I've read lots of other forums about this problem and haven't been able to fix it so far.
Here's my code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="lineid"
                        DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
  <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
  <asp:BoundField DataField="lineid" HeaderText="lineid" ReadOnly="True" 
          SortExpression="lineid" InsertVisible="False" />
  <asp:BoundField DataField="appname" HeaderText="appname" SortExpression="appname" />
  <asp:BoundField DataField="cost" HeaderText="cost" SortExpression="cost" 
          DataFormatString="{0:c2}" HtmlEncode="False" />
  <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
 DeleteCommand="DELETE FROM [orderlines] WHERE [lineid] = ?" InsertCommand="INSERT INTO [orderlines] ([lineid], [appname], [cost], [Quantity]) VALUES (?, ?, ?, ?)"
 ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" SelectCommand="SELECT [lineid], [appname], [cost], [Quantity] FROM [orderlines] WHERE ([orderno] = ?)"
 UpdateCommand="UPDATE [orderlines] SET [appname] = @appname, [cost] = @cost, [Quantity] = @Quantity WHERE ([lineid] = @lineid)">
  <asp:Parameter Name="lineid" Type="Int16" />
  <asp:Parameter Name="lineid" Type="Int16" />
  <asp:Parameter Name="appname" Type="String" />
  <asp:Parameter Name="cost" Type="Decimal" />
  <asp:Parameter Name="Quantity" Type="Int32" />
  <asp:QueryStringParameter Name="orderno" QueryStringField="id" Type="Int16" />
  <asp:Parameter Name="appname" Type="String" />
  <asp:Parameter Name="cost" Type="Decimal" />
  <asp:Parameter Name="Quantity" Type="Int32" />
  <asp:Parameter Name="lineid" Type="Int16" />

Thank you,
Posted 26-Jun-13 7:24am
Updated 26-Jun-13 7:27am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

UpdateCommand has named SQL parameter names, where InsertCommand, SelectCommand and DeleteCommand has unnamed.

Try change:
UPDATE [orderlines] SET [appname] = @appname, [cost] = @cost, [Quantity] = @Quantity WHERE ([lineid] = @lineid)

UPDATE [orderlines] SET [appname] = ?, [cost] = ?, [Quantity] = ? WHERE ([lineid] = ?)
Pottage 27-Jun-13 8:48am
Thanks a lot, this change made it work. I was banging my head against the desk in frustration, so this helps so much.
Kim Togo 29-Jun-13 13:13pm
Cool :-), I know the feeling.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

It might be possible to use the code as-is but with a slight addition. Try replaceing in the code:
UpdateCommand="UPDATE [orderlines] SET [appname] = @appname, [cost] = @cost, [Quantity] = @Quantity WHERE 

With this:
UpdateCommand="DECLARE @appname [nvarchar](54);UPDATE [orderlines] SET [appname] = @appname, [cost] = @cost, [Quantity] = @Quantity WHERE 

The error received wouldn't be if the TSQL-end of this mix was more of a call to a stored procedure. In which case, this DECLARE would certainly be easier spotted in that event.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171207.1 | Last Updated 26 Jun 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100