Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL
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.">
 <Columns>
  <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" />
 </Columns>
</asp:GridView>
<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)">
 <DeleteParameters>
  <asp:Parameter Name="lineid" Type="Int16" />
 </DeleteParameters>
 <InsertParameters>
  <asp:Parameter Name="lineid" Type="Int16" />
  <asp:Parameter Name="appname" Type="String" />
  <asp:Parameter Name="cost" Type="Decimal" />
  <asp:Parameter Name="Quantity" Type="Int32" />
 </InsertParameters>
 <SelectParameters>
  <asp:QueryStringParameter Name="orderno" QueryStringField="id" Type="Int16" />
 </SelectParameters>
 <UpdateParameters>
  <asp:Parameter Name="appname" Type="String" />
  <asp:Parameter Name="cost" Type="Decimal" />
  <asp:Parameter Name="Quantity" Type="Int32" />
  <asp:Parameter Name="lineid" Type="Int16" />
 </UpdateParameters>
</asp:SqlDataSource>
 
Thank you,
Thomas
Posted 26-Jun-13 6:24am
Pottage207
Edited 26-Jun-13 6:27am
v2
Rate this: bad
good
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)
To
UPDATE [orderlines] SET [appname] = ?, [cost] = ?, [Quantity] = ? WHERE ([lineid] = ?)
  Permalink  
v2
Comments
Pottage at 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 at 29-Jun-13 13:13pm
   
Cool :-), I know the feeling.
Rate this: bad
good
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 210
1 Richard MacCutchan 100
2 kbrandwijk 90
3 ProgramFOX 80
4 Sandeep Singh Shekhawat 70
0 Sergey Alexandrovich Kryukov 9,050
1 OriginalGriff 8,151
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 26 Jun 2013
Copyright © CodeProject, 1999-2014
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