SQL edit - Must declare the scalar variable "@appname" please help

Question

3.00/5 (1 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.NET

<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

Pottage

Updated 26-Jun-13 6:27am

v2

Add a Solution

2 solutions

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.

Posted 26-Jun-13 7:26am

RedDk

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kim Togo · Accepted Answer · 2013-06-26T06:41:00

Solution 1

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

Try change:

SQL

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

To

SQL

UPDATE [orderlines] SET [appname] = ?, [cost] = ?, [Quantity] = ? WHERE ([lineid] = ?)

Posted 26-Jun-13 6:41am

Kim Togo

Updated 26-Jun-13 7:03am

v2

Comments

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.