Incorrect syntax near "nvarchar".
Incorrect syntax near "nvarchar".
am 09.01.2008 18:16:51 von 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" %>
Runat="Server">
| BSP Codes |
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">
CancelText="Cancel"
UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
HeaderStyle-CssClass="rptTblTitle" />
Text='<%# Bind("[BOTTLE$SIZE]") %>'>
ErrorMessage="Bottle Size is a required field." Text="*"
ControlToValidate="txtBottleSize">
runat="server"
ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*"
ControlToValidate="txtBottleSize"
ValidationExpression="[0-9.]+(ML|L)">
Text='<%# Bind("LABELED") %>'>
ErrorMessage="Labeled is a required field" Text="*"
ControlToValidate="txtLabeled">
ErrorMessage="Bottles per case must be a whole number." Text="*"
ControlToValidate="txtBottlesPerCase">
Text='<%# Bind("[LITERS$PER$CASE]") %>'>
ErrorMessage="Liters per case must be a number."
ControlToValidate="txtLitersPerCase" Text="*">
CommandName="Delete" runat="server">Delete
|
runat="server" Text="" style="color:Red;font-weight:bold;"/> |
Code |
Columns="2">
ErrorMessage="Please Supply a BSP Code." Text="*"
ControlToValidate="txtAddCode"
SetFocusOnError="True">
|
Bottle Size |
Columns="10">
ErrorMessage="Bottle Size is a required field." Text="*"
ControlToValidate="txtAddSize">
runat="server"
ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*"
ControlToValidate="txtAddSize"
ValidationExpression="[0-9.]+(ML|L)">
|
Labeled |
Columns="2"> |
Bottles Per Case |
Columns="4"> |
Liters Per Case |
Columns="8"> |
|
Text="Add BSP" onclick="btnAddNew_Click" /> |
|
|
ShowMessageBox="True" ShowSummary="False" />
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">
type="String" />
/>
Name="BOTTLES$PER$CASE" type="Int32" />
type="Decimal" />
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
Re: Incorrect syntax near "nvarchar".
am 10.01.2008 00:59:05 von Jeff Dillon
Run SQL Profiler to see the actual SQL syntax being sent
Jeff
"Kevin Bilbee" wrote in message
news:%23HQOwNuUIHA.4360@TK2MSFTNGP06.phx.gbl...
> 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" %>
>
> Runat="Server">
>
>
> | BSP Codes |
>
>
>
>
>
> 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">
>
>
> CancelText="Cancel"
> UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
>
>
>
> HeaderStyle-CssClass="rptTblTitle" />
>
>
>
> Text='<%# Bind("[BOTTLE$SIZE]") %>'>
>
> ErrorMessage="Bottle Size is a required field." Text="*"
> ControlToValidate="txtBottleSize">
>
> runat="server"
> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
> Text="*"
> ControlToValidate="txtBottleSize"
> ValidationExpression="[0-9.]+(ML|L)">
>
>
>
>
>
>
>
>
>
> Text='<%# Bind("LABELED") %>'>
>
> ErrorMessage="Labeled is a required field" Text="*"
> ControlToValidate="txtLabeled">
>
>
>
>
>
>
>
>
>
>
> ErrorMessage="Bottles per case must be a whole number." Text="*"
> ControlToValidate="txtBottlesPerCase">
>
>
>
>
>
>
>
>
>
> runat="server"
> Text='<%# Bind("[LITERS$PER$CASE]") %>'>
>
> ErrorMessage="Liters per case must be a number."
> ControlToValidate="txtLitersPerCase"
> Text="*">
>
>
>
>
>
>
>
>
>
> CommandName="Delete" runat="server">Delete
>
>
>
>
>
> |
> runat="server" Text="" style="color:Red;font-weight:bold;"/> |
>
> Code |
>
> Columns="2">
>
> ErrorMessage="Please Supply a BSP Code." Text="*"
> ControlToValidate="txtAddCode"
> SetFocusOnError="True">
> |
>
>
> Bottle Size |
>
> Columns="10">
>
> ErrorMessage="Bottle Size is a required field." Text="*"
> ControlToValidate="txtAddSize">
>
> runat="server"
> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
> Text="*"
> ControlToValidate="txtAddSize"
> ValidationExpression="[0-9.]+(ML|L)">
> |
>
>
> Labeled |
>
> Columns="2"> |
>
>
> Bottles Per Case |
>
> Columns="4"> |
>
>
> Liters Per Case |
>
> Columns="8"> |
>
>
> runat="server"
> Text="Add BSP" onclick="btnAddNew_Click" /> |
>
> |
>
>
> |
>
>
> ShowMessageBox="True" ShowSummary="False" />
>
> 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">
>
>
>
>
>
>
>
>
>
> type="String" />
>
> />
>
> Name="BOTTLES$PER$CASE" type="Int32" />
>
> Name="LITERS$PER$CASE"
> type="Decimal" />
>
>
>
>
>
> 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
>
>
>
>
>
Re: Incorrect syntax near "nvarchar".
am 10.01.2008 02:50:02 von 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" wrote in message
news:OtUTeuxUIHA.4740@TK2MSFTNGP02.phx.gbl...
> Run SQL Profiler to see the actual SQL syntax being sent
>
> Jeff
> "Kevin Bilbee" wrote in message
> news:%23HQOwNuUIHA.4360@TK2MSFTNGP06.phx.gbl...
>> 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" %>
>>
>> Runat="Server">
>>
>>
>> | BSP Codes |
>>
>>
>>
>>
>>
>> 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">
>>
>>
>> CancelText="Cancel"
>> UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
>>
>>
>>
>> HeaderStyle-CssClass="rptTblTitle" />
>>
>>
>>
>> runat="server"
>> Text='<%# Bind("[BOTTLE$SIZE]") %>'>
>>
>> ErrorMessage="Bottle Size is a required field." Text="*"
>> ControlToValidate="txtBottleSize">
>>
>> runat="server"
>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>> Text="*"
>> ControlToValidate="txtBottleSize"
>> ValidationExpression="[0-9.]+(ML|L)">
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Text='<%# Bind("LABELED") %>'>
>>
>> ErrorMessage="Labeled is a required field" Text="*"
>> ControlToValidate="txtLabeled">
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ErrorMessage="Bottles per case must be a whole number." Text="*"
>> ControlToValidate="txtBottlesPerCase">
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> runat="server"
>> Text='<%# Bind("[LITERS$PER$CASE]") %>'>
>>
>> ErrorMessage="Liters per case must be a number."
>> ControlToValidate="txtLitersPerCase"
>> Text="*">
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> CommandName="Delete" runat="server">Delete
>>
>>
>>
>>
>>
>> |
>> runat="server" Text="" style="color:Red;font-weight:bold;"/> |
>>
>> Code |
>>
>> Columns="2">
>>
>> ErrorMessage="Please Supply a BSP Code." Text="*"
>> ControlToValidate="txtAddCode"
>> SetFocusOnError="True">
>> |
>>
>>
>> Bottle Size |
>>
>> Columns="10">
>>
>> ErrorMessage="Bottle Size is a required field." Text="*"
>> ControlToValidate="txtAddSize">
>>
>> runat="server"
>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>> Text="*"
>> ControlToValidate="txtAddSize"
>> ValidationExpression="[0-9.]+(ML|L)">
>> |
>>
>>
>> Labeled |
>>
>> Columns="2"> |
>>
>>
>> Bottles Per Case |
>>
>> Columns="4"> |
>>
>>
>> Liters Per Case |
>>
>> Columns="8"> |
>>
>>
>> runat="server"
>> Text="Add BSP" onclick="btnAddNew_Click" /> |
>>
>> |
>>
>>
>> |
>>
>>
>> ShowMessageBox="True" ShowSummary="False" />
>>
>> 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">
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> type="String" />
>>
>> type="Char"
>> />
>>
>> Name="BOTTLES$PER$CASE" type="Int32" />
>>
>> Name="LITERS$PER$CASE"
>> type="Decimal" />
>>
>>
>>
>>
>>
>> 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
>>
>>
>>
>>
>>
>
>
Re: Incorrect syntax near "nvarchar".
am 10.01.2008 03:53:02 von Courtney
"Kevin Bilbee" wrote in message
news:uQF8WsyUIHA.4440@TK2MSFTNGP06.phx.gbl...
> 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" wrote in message
> news:OtUTeuxUIHA.4740@TK2MSFTNGP02.phx.gbl...
>> Run SQL Profiler to see the actual SQL syntax being sent
>>
>> Jeff
>> "Kevin Bilbee" wrote in message
>> news:%23HQOwNuUIHA.4360@TK2MSFTNGP06.phx.gbl...
>>> 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" %>
>>>
>>> Runat="Server">
>>>
>>>
>>> | BSP Codes |
>>>
>>>
>>>
>>>
>>>
>>> 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">
>>>
>>>
>>> CancelText="Cancel"
>>> UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
>>>
>>>
>>>
>>> HeaderStyle-CssClass="rptTblTitle" />
>>>
>>>
>>>
>>> runat="server"
>>> Text='<%# Bind("[BOTTLE$SIZE]") %>'>
>>>
>>> ErrorMessage="Bottle Size is a required field." Text="*"
>>> ControlToValidate="txtBottleSize">
>>>
>>> runat="server"
>>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>>> Text="*"
>>> ControlToValidate="txtBottleSize"
>>> ValidationExpression="[0-9.]+(ML|L)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Text='<%# Bind("LABELED") %>'>
>>>
>>> ErrorMessage="Labeled is a required field" Text="*"
>>> ControlToValidate="txtLabeled">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ErrorMessage="Bottles per case must be a whole number." Text="*"
>>> ControlToValidate="txtBottlesPerCase">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> runat="server"
>>> Text='<%# Bind("[LITERS$PER$CASE]") %>'>
>>>
>>> ErrorMessage="Liters per case must be a number."
>>> ControlToValidate="txtLitersPerCase"
>>> Text="*">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> CommandName="Delete" runat="server">Delete
>>>
>>>
>>>
>>>
>>>
>>> |
>>> runat="server" Text="" style="color:Red;font-weight:bold;"/> |
>>>
>>> Code |
>>>
>>> Columns="2">
>>>
>>> ErrorMessage="Please Supply a BSP Code." Text="*"
>>> ControlToValidate="txtAddCode"
>>> SetFocusOnError="True">
>>> |
>>>
>>>
>>> Bottle Size |
>>>
>>> Columns="10">
>>>
>>> ErrorMessage="Bottle Size is a required field." Text="*"
>>> ControlToValidate="txtAddSize">
>>>
>>> runat="server"
>>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>>> Text="*"
>>> ControlToValidate="txtAddSize"
>>> ValidationExpression="[0-9.]+(ML|L)">
>>> |
>>>
>>>
>>> Labeled |
>>>
>>> Columns="2"> |
>>>
>>>
>>> Bottles Per Case |
>>>
>>> Columns="4"> |
>>>
>>>
>>> Liters Per Case |
>>>
>>> Columns="8"> |
>>>
>>>
>>> runat="server"
>>> Text="Add BSP" onclick="btnAddNew_Click" /> |
>>>
>>> |
>>>
>>>
>>> |
>>>
>>>
>>> ShowMessageBox="True" ShowSummary="False" />
>>>
>>> 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">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> type="String" />
>>>
>>> type="Char"
>>> />
>>>
>>> Name="BOTTLES$PER$CASE" type="Int32" />
>>>
>>> Name="LITERS$PER$CASE"
>>> type="Decimal" />
>>>
>>>
>>>
>>>
>>>
>>> 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
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
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
Re: Incorrect syntax near "nvarchar".
am 10.01.2008 06:53:19 von Kevin Bilbee
"Lloyd Sheen" wrote in message
news:%23cwf9PzUIHA.4440@TK2MSFTNGP06.phx.gbl...
>
> "Kevin Bilbee" wrote in message
> news:uQF8WsyUIHA.4440@TK2MSFTNGP06.phx.gbl...
>> 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" wrote in message
>> news:OtUTeuxUIHA.4740@TK2MSFTNGP02.phx.gbl...
>>> Run SQL Profiler to see the actual SQL syntax being sent
>>>
>>> Jeff
>>> "Kevin Bilbee" wrote in message
>>> news:%23HQOwNuUIHA.4360@TK2MSFTNGP06.phx.gbl...
>>>> 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" %>
>>>>
>>>> Runat="Server">
>>>>
>>>>
>>>> align="center">
>>>> | BSP Codes |
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> 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">
>>>>
>>>>
>>>> CancelText="Cancel"
>>>> UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
>>>>
>>>>
>>>>
>>>> HeaderStyle-CssClass="rptTblTitle" />
>>>>
>>>>
>>>>
>>>> runat="server"
>>>> Text='<%# Bind("[BOTTLE$SIZE]") %>'>
>>>>
>>>> ErrorMessage="Bottle Size is a required field." Text="*"
>>>> ControlToValidate="txtBottleSize">
>>>>
>>>> runat="server"
>>>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>>>> Text="*"
>>>> ControlToValidate="txtBottleSize"
>>>> ValidationExpression="[0-9.]+(ML|L)">
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Text='<%# Bind("LABELED") %>'>
>>>>
>>>> ErrorMessage="Labeled is a required field" Text="*"
>>>> ControlToValidate="txtLabeled">
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Text='<%#
>>>> Bind("[BOTTLES$PER$CASE]") %>'>
>>>>
>>>> ErrorMessage="Bottles per case must be a whole number." Text="*"
>>>> ControlToValidate="txtBottlesPerCase">
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> runat="server"
>>>> Text='<%# Bind("[LITERS$PER$CASE]") %>'>
>>>>
>>>> ErrorMessage="Liters per case must be a number."
>>>> ControlToValidate="txtLitersPerCase"
>>>> Text="*">
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> CommandName="Delete" runat="server">Delete
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> |
>>>> runat="server" Text="" style="color:Red;font-weight:bold;"/> |
>>>>
>>>> Code |
>>>>
>>>> Columns="2">
>>>>
>>>> ErrorMessage="Please Supply a BSP Code." Text="*"
>>>> ControlToValidate="txtAddCode"
>>>> SetFocusOnError="True">
>>>> |
>>>>
>>>>
>>>> Bottle Size |
>>>>
>>>> Columns="10">
>>>>
>>>> ErrorMessage="Bottle Size is a required field." Text="*"
>>>> ControlToValidate="txtAddSize">
>>>>
>>>> runat="server"
>>>> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
>>>> Text="*"
>>>> ControlToValidate="txtAddSize"
>>>> ValidationExpression="[0-9.]+(ML|L)">
>>>> |
>>>>
>>>>
>>>> Labeled |
>>>>
>>>> Columns="2"> |
>>>>
>>>>
>>>> Bottles Per Case |
>>>>
>>>> Columns="4"> |
>>>>
>>>>
>>>> Liters Per Case |
>>>>
>>>> Columns="8"> |
>>>>
>>>>
>>>> runat="server"
>>>> Text="Add BSP" onclick="btnAddNew_Click" /> |
>>>>
>>>> |
>>>>
>>>>
>>>> |
>>>>
>>>>
>>>> ShowMessageBox="True" ShowSummary="False" />
>>>>
>>>> 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">
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> type="String" />
>>>>
>>>> type="Char"
>>>> />
>>>>
>>>> Name="BOTTLES$PER$CASE" type="Int32" />
>>>>
>>>> Name="LITERS$PER$CASE"
>>>> type="Decimal" />
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 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
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
Re: Incorrect syntax near "nvarchar".
am 27.01.2008 23:33:23 von 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" wrote in message
news:%23HQOwNuUIHA.4360@TK2MSFTNGP06.phx.gbl...
> 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" %>
>
> Runat="Server">
>
>
> | BSP Codes |
>
>
>
>
>
> 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">
>
>
> CancelText="Cancel"
> UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
>
>
>
> HeaderStyle-CssClass="rptTblTitle" />
>
>
>
> Text='<%# Bind("[BOTTLE$SIZE]") %>'>
>
> ErrorMessage="Bottle Size is a required field." Text="*"
> ControlToValidate="txtBottleSize">
>
> runat="server"
> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
> Text="*"
> ControlToValidate="txtBottleSize"
> ValidationExpression="[0-9.]+(ML|L)">
>
>
>
>
>
>
>
>
>
> Text='<%# Bind("LABELED") %>'>
>
> ErrorMessage="Labeled is a required field" Text="*"
> ControlToValidate="txtLabeled">
>
>
>
>
>
>
>
>
>
>
> ErrorMessage="Bottles per case must be a whole number." Text="*"
> ControlToValidate="txtBottlesPerCase">
>
>
>
>
>
>
>
>
>
> runat="server"
> Text='<%# Bind("[LITERS$PER$CASE]") %>'>
>
> ErrorMessage="Liters per case must be a number."
> ControlToValidate="txtLitersPerCase"
> Text="*">
>
>
>
>
>
>
>
>
>
> CommandName="Delete" runat="server">Delete
>
>
>
>
>
> |
> runat="server" Text="" style="color:Red;font-weight:bold;"/> |
>
> Code |
>
> Columns="2">
>
> ErrorMessage="Please Supply a BSP Code." Text="*"
> ControlToValidate="txtAddCode"
> SetFocusOnError="True">
> |
>
>
> Bottle Size |
>
> Columns="10">
>
> ErrorMessage="Bottle Size is a required field." Text="*"
> ControlToValidate="txtAddSize">
>
> runat="server"
> ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'"
> Text="*"
> ControlToValidate="txtAddSize"
> ValidationExpression="[0-9.]+(ML|L)">
> |
>
>
> Labeled |
>
> Columns="2"> |
>
>
> Bottles Per Case |
>
> Columns="4"> |
>
>
> Liters Per Case |
>
> Columns="8"> |
>
>
> runat="server"
> Text="Add BSP" onclick="btnAddNew_Click" /> |
>
> |
>
>
> |
>
>
> ShowMessageBox="True" ShowSummary="False" />
>
> 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">
>
>
>
>
>
>
>
>
>
> type="String" />
>
> />
>
> Name="BOTTLES$PER$CASE" type="Int32" />
>
> Name="LITERS$PER$CASE"
> type="Decimal" />
>
>
>
>
>
> 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
>
>
>
>
>