The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable " [at] CODE".
I have looked at many posts with this error. I have potes to ASP.net forums
with no luck. How can I debug and fis this error. Below is the ASPX page the
code behind and the sql data table create schema straight from the database.
Running in windows 2003/Sql 2005 Express
Please someone fine the error, direct me tho the knowledgebase article or
something. I have used the datagrid on hundreds of forms never with an issue
but I am stumped.
Kevin Bilbee
<% [at] Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP
Codes" %>
<% [at] MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<table class="mainTable" cellspacing="0" cellpadding="3" align="center">
<tr><td class="mainTableTitle">BSP Codes</td></tr>
<tr><td>
<table align="center">
<tr>
<td>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
BorderColor="Silver"
BorderStyle="Solid"
BorderWidth="1px"
HorizontalAlign="Center"
CellPadding="3"
DataKeyNames="CODE"
DataSourceID="SqlDataSource1"
OnRowDataBound="GridView1_RowDataBound"
OnRowEditing="GridView1_OnRowEditing"
OnRowCancelingEdit="GridView1_EndEdit"
OnRowUpdating="GridView1_OnRowUpdating"
OnRowUpdated="GridView1_EndEdit">
<Columns>
<asp:CommandField ShowEditButton="True" EditText="Edit"
CancelText="Cancel"
UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
<HeaderStyle CssClass="rptTblTitle"></HeaderStyle>
</asp:CommandField>
<asp:BoundField DataField="CODE" HeaderText="Code" ReadOnly="true"
HeaderStyle-CssClass="rptTblTitle" />
<asp:TemplateField HeaderText="Bottle Size" SortExpression="Btl Sz">
<EditItemTemplate>
<asp:TextBox ID="txtBottleSize" MaxLength="10" Columns="10" runat="server"
Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ErrorMessage="Bottle Size is a required field." Text="*"
ControlToValidate="txtBottleSize"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1"
runat="server"
ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*"
ControlToValidate="txtBottleSize"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[BOTTLE$SIZE]")
%>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Labeled" SortExpression="Labeled">
<EditItemTemplate>
<asp:TextBox ID="txtLabeled" MaxLength="1" Columns="2" runat="server"
Text='<%# Bind("LABELED") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ErrorMessage="Labeled is a required field" Text="*"
ControlToValidate="txtLabeled"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("LABELED")
%>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Bottles Per Case" SortExpression="Btls Per
Case">
<EditItemTemplate>
<asp:TextBox ID="txtBottlesPerCase" Columns="4" runat="server" Text='<%#
Bind("[BOTTLES$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
ErrorMessage="Bottles per case must be a whole number." Text="*"
ControlToValidate="txtBottlesPerCase"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]")
%>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Liters Per Case" SortExpression="Ltrs Per
Case">
<EditItemTemplate>
<asp:TextBox ID="txtLitersPerCase" MaxLength="8" Columns="8" runat="server"
Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
ErrorMessage="Liters per case must be a number."
ControlToValidate="txtLitersPerCase" Text="*"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("[LITERS$PER$CASE]")
%>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderStyle-CssClass="rptTblTitle">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" CommandArgument='<%# Eval("CODE") %>'
CommandName="Delete" runat="server">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<table id="tblAddBSP" runat="server" width="100%">
<tr><td colspan="2" align="center"><asp:Label ID="lblAddMessage"
runat="server" Text="" style="color:Red;font-weight:bold;"/></td></tr>
<tr>
<td style="font-weight:bold;">Code</td>
<td><asp:TextBox ID="txtAddCode" runat="server" MaxLength="1"
Columns="2"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfv_txtAddCode" runat="server"
ErrorMessage="Please Supply a BSP Code." Text="*"
ControlToValidate="txtAddCode"
SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Bottle Size</td>
<td><asp:TextBox ID="txtAddSize" runat="server" MaxLength="10"
Columns="10"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ErrorMessage="Bottle Size is a required field." Text="*"
ControlToValidate="txtAddSize"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1"
runat="server"
ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*"
ControlToValidate="txtAddSize"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Labeled</td>
<td><asp:TextBox ID="txtAddLabeled" runat="server" MaxLength="1"
Columns="2"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Bottles Per Case</td>
<td><asp:TextBox ID="txtAddBottlesPerCase" runat="server" MaxLength="4"
Columns="4"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Liters Per Case</td>
<td><asp:TextBox ID="txtAddLitersPerCase" runat="server" MaxLength="8"
Columns="8"></asp:TextBox></td>
</tr>
<tr><td colspan="2" align="right"><asp:Button ID="btnAddNew" runat="server"
Text="Add BSP" onclick="btnAddNew_Click" /></td></tr>
</table>
</td>
</tr>
</table>
</td></tr>
</table>
<asp:ValidationSummary ID="ValidationSummary1" runat="server"
ShowMessageBox="True" ShowSummary="False" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
ProviderName="<%$ ConnectionStrings:SqlConnectionString.ProviderName %>"
DeleteCommand="DELETE FROM BSP WHERE CODE = [at] CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES ( [at] CODE, [at] BOTTLE$SIZE, [at] LABELED,
[at] BOTTLES$PER$CASE, [at] LITERS$PER$CASE)"
SelectCommand="SELECT CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE,
LITERS$PER$CASE FROM BSP order by CODE"
UpdateCommand="UPDATE BSP SET [BOTTLE$SIZE] = [at] BOTTLE$SIZE, [LABELED] =
[at] LABELED, [BOTTLES$PER$CASE] = [at] BOTTLES$PER$CASE, [LITERS$PER$CASE] =
[at] LITERS$PER$CASE WHERE [CODE] = [at] CODE">
<UpdateParameters>
<asp:Parameter Name="BOTTLE$SIZE" type="String" />
<asp:Parameter Name="LABELED" type="Char" />
<asp:Parameter Name="BOTTLES$PER$CASE" type="Int32" />
<asp:Parameter Name="LITERS$PER$CASE" type="Decimal" />
<asp:Parameter Name="CODE" type="Char" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="txtAddSize" Name="BOTTLE$SIZE"
type="String" />
<asp:ControlParameter ControlID="txtAddLabeled" Name="LABELED" type="Char"
/>
<asp:ControlParameter ControlID="txtAddBottlesPerCase"
Name="BOTTLES$PER$CASE" type="Int32" />
<asp:ControlParameter ControlID="txtAddLitersPerCase" Name="LITERS$PER$CASE"
type="Decimal" />
<asp:ControlParameter ControlID="txtAddCode" Name="CODE" type="Char" />
</InsertParameters>
</asp:SqlDataSource>
</asp:Content>
Code Behind
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using reports;
using System.Data.SqlClient;
public partial class MaintainBSP : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Master.ActiveTab = Helpers.Tabs.Admin;
Security.CheckPageAccess(Security.AccessTypes.Administrator) ;
}
protected void GridView1_OnRowUpdating(Object sender,
GridViewUpdateEventArgs e)
{
}
protected void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs
e)
{
tblAddBSP.Visible = false;
}
protected void GridView1_EndEdit(Object sender, EventArgs e)
{
tblAddBSP.Visible = true;
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
LinkButton l = (LinkButton)e.Row.FindControl("LinkButton1");
l.Attributes.Add("onclick", String.Format("javascript:return confirm('Are
you sure you want to delete BSP Code \\'{0}\\'')",
DataBinder.Eval(e.Row.DataItem, "CODE")));
}
}
protected void btnAddNew_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.Insert();
lblAddMessage.Text = "Add BSP '" + txtAddCode.Text + "' successful.";
txtAddCode.Text = String.Empty;
txtAddSize.Text = String.Empty;
txtAddLabeled.Text = String.Empty;
txtAddBottlesPerCase.Text = String.Empty;
txtAddLitersPerCase.Text = String.Empty;
}
catch (SqlException ex)
{
if (ex.Number == 2627)
{
lblAddMessage.Text = "The code '" + txtAddCode.Text + "' is already in the
database.
Select another code for this BSP.";
txtAddCode.Text = String.Empty;
}
else
lblAddMessage.Text = ex.Number + " - " + ex.ErrorCode.ToString() + " - " +
ex.Message;
}
catch
{
lblAddMessage.Text = "There was an issue inserting the BSP Code '" +
txtAddCode.Text + "'. Please check the values and try again.";
}
}
}
SQL Table
1 USE [BS_DATASTORE]
2 GO
3 /****** Object: Table [dbo].[BSP] Script Date: 01/06/2008 10:39:46
******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 SET ANSI_PADDING ON
9 GO
10 CREATE TABLE [dbo].[BSP](
11 [CODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
12 [BOTTLE$SIZE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
13 [LABELED] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
14 [BOTTLES$PER$CASE] [int] NOT NULL,
15 [LITERS$PER$CASE] [decimal](5, 2) NULL,
16 CONSTRAINT [PK_BSP] PRIMARY KEY CLUSTERED
17 (
18 [CODE] ASC
19 )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
20 ) ON [PRIMARY]
21
22 GO
23 SET ANSI_PADDING OFF
