Incorrect syntax near 'nvarchar'.

K

Kevin Bilbee

The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
 
J

Jeff Dillon

Run SQL Profiler to see the actual SQL syntax being sent

Jeff
Kevin Bilbee said:
The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
[/QUOTE]
Click to expand...
 
K

Kevin Bilbee

How do I do that on MS sql express 2005? Profiler is not present, or is it
and I am just thick. I am not an MS SQL Expert. Please assist.



Kevin Bilbee




Jeff Dillon said:
Run SQL Profiler to see the actual SQL syntax being sent

Jeff
Kevin Bilbee said:
The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
L

Lloyd Sheen

Kevin Bilbee said:
How do I do that on MS sql express 2005? Profiler is not present, or is it
and I am just thick. I am not an MS SQL Expert. Please assist.



Kevin Bilbee




Jeff Dillon said:
Run SQL Profiler to see the actual SQL syntax being sent

Jeff
Kevin Bilbee said:
The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain
BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE,
@LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
[/QUOTE]
[/QUOTE]
[/QUOTE]

I don't see any DeleteParameters.  If you look at the SqlDataSource it has 
UpdateParameters and InsertParameters.  Both the Delete and Insert commands 
take @Code as a parameter.  I would think the Insert is correct so it must 
be attempting a delete when the error occurs???

LS
Click to expand...
Click to expand...
Click to expand...
 
K

Kevin Bilbee

Lloyd Sheen said:
Kevin Bilbee said:
How do I do that on MS sql express 2005? Profiler is not present, or is
it and I am just thick. I am not an MS SQL Expert. Please assist.



Kevin Bilbee




Jeff Dillon said:
Run SQL Profiler to see the actual SQL syntax being sent

Jeff
The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain
BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE,
@LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
[/QUOTE]
[/QUOTE]

I don't see any DeleteParameters.  If you look at the SqlDataSource it has 
UpdateParameters and InsertParameters.  Both the Delete and Insert 
commands take @Code as a parameter.  I would think the Insert is correct 
so it must be attempting a delete when the error occurs???

LS[/QUOTE]

I wish you were correct. It deletes just fine. I am trying to update!!

Just in case this was a bug in not supplying the delete parameter, for an 
update. I added it and the same error was generated.

Kevin
Click to expand...
Click to expand...
Click to expand...
 
K

Kevin Bilbee

SQL Management Studio Express has an activity monitor. I was able to get the
failed SQL statement form there. I for the life of me can not find anything
wrong with it.

(@BOTTLE$SIZE nvarchar(4000),@LABELED nchar(1),@BOTTLES$PER$CASE
int,@LITERS$PER$CASE decimal(29,0),@[BOTTLE$SIZE]
nvarchar(5),@[BOTTLES$PER$CASE] nvarchar(2),@[LITERS$PER$CASE]
nvarchar(4),@CODE nvarchar(1))UPDATE BSP SET CODE = @CODE, BOTTLE$SIZE =
@BOTTLE$SIZE, LABELED = @LABELED, BOTTLES$PER$CASE = @BOTTLES$PER$CASE,
LITERS$PER$CASE = @LITERS$PER$CASE WHERE CODE = @CODE


Please someonw help with this problem?


Kevin Bilbee

Kevin Bilbee said:
The error
Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@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

<%@ Page Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP
Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<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 = @CODE"
InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED,
BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED,
@BOTTLES$PER$CASE, @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] = @BOTTLE$SIZE, [LABELED] =
@LABELED, [BOTTLES$PER$CASE] = @BOTTLES$PER$CASE, [LITERS$PER$CASE] =
@LITERS$PER$CASE WHERE
Code: 
 = @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.<br />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
[/QUOTE]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incorrect syntax near 'nvarchar'. 2
enter key causing postback and then errors 5
Text box and Gridview on same page 2
GridView with ObjectDataSource. Can't get NewValues & OldValues 0
trouble with ChangePassword control 1
How to show an empty GridView when DataSet is datasource? 0
editing gridview when paging 0
Validation Control in Materpage\Conentpage\ContentPlaceHolder 1

Members online

Total: 54 (members: 3, guests: 51)
Robots: 390

Forum statistics

Threads
473,982
Messages
2,570,186
Members
46,744
Latest member
CortneyMcK

Latest Threads

Top