Incorrect syntax near the keyword 'Default'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found another issue with this same heading, but mine is different so please
read on...

I have the following table:
CREATE TABLE [UsersSF] (
[UserId] [tinyint] NOT NULL ,
[SFId] [tinyint] NOT NULL ,
[SFTypeId] [tinyint] NOT NULL ,
[Description] [varchar] (30) NOT NULL ,
[Sort] [text] NOT NULL ,
[Filter] [text] NOT NULL ,
[Default] [bit] NOT NULL ,
CONSTRAINT [PK_UsersSF] PRIMARY KEY NONCLUSTERED
(
[UserId],
[SFId],
[SFTypeId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_UsersSF(SFTypeId)_SFTypes(SFTypeId)] FOREIGN KEY
(
[SFTypeId]
) REFERENCES [SFTypes] (
[SFTypeId]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am trying to update the table using the following code:
SqlCommandBuilder pscbBuilder;
SqlDataAdapter psdaDefault;
DataTable pdttDefault;
DataRow pdtrDefault;

pstrSql = "SELECT UserId, SFId, SFTypeId, [Description], Sort, Filter,
[Default] " +
"FROM dbo.UsersSF " +
"WHERE UserId = " + UserId + " " +
"AND SFTypeId = " + (short)SortFilter.SFTypes.SFIncidents + " " +
"AND [Default] = 1";
psdaDefault = new SqlDataAdapter( pstrSql, Connection );
pscbBuilder = new SqlCommandBuilder( psdaDefault );
psdaDefault.Fill( pdtsLookups, "UserDefault" );
pdttDefault = pdtsLookups.Tables["UserDefault"];
pdtrDefault = pdttDefault.Rows[0];
pdtrDefault["Sort"] = "Some text";
pdtrDefault["Filter"] = "Some text";
psdaDefault.UpdateCommand = pscbBuilder.GetUpdateCommand();
psdaDefault.Update( pdttDefault ); <---Fails here.
pdttDefault.AcceptChanges();


When I execute the statement, I get the following error:
SqlException: Incorrect syntax near the keyword 'Default'.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataTable dataTable) +139
EDS.WarrantyServices.ResourceConnection.IncidentsSF.Page_Load(Object
objSender, EventArgs evaEvtArgs) in
c:\inetpub\wwwroot\resconn\incidentssf.aspx.cs:143
System.Web.UI.Control.OnLoad(EventArgs e) +67
EDS.WarrantyServices.BasePage.OnLoad(EventArgs etaEventArgs)
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +739

Now my guess is that it can't handle having a column name called 'Default'
because if I remove the 'Default' column from the select everything works. I
have tried the following to get the update to work:
- Giving 'Default' an alias
- Modifying the CommandText after it is generated by the SqlCommandBuilder
to put brackets [] around all Default column references.

Anyone have any ideas on how I can get around this other than changing the
name of the column?

Thanks in advance.
 
Ah "Default" is a reserved word. (Of course so is "sort"). While you can
(sometimes) get away with naming objects using reserved keywords it usually
ends up with everyone hating you.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

rgrandidier said:
I found another issue with this same heading, but mine is different so
please
read on...

I have the following table:
CREATE TABLE [UsersSF] (
[UserId] [tinyint] NOT NULL ,
[SFId] [tinyint] NOT NULL ,
[SFTypeId] [tinyint] NOT NULL ,
[Description] [varchar] (30) NOT NULL ,
[Sort] [text] NOT NULL ,
[Filter] [text] NOT NULL ,
[Default] [bit] NOT NULL ,
CONSTRAINT [PK_UsersSF] PRIMARY KEY NONCLUSTERED
(
[UserId],
[SFId],
[SFTypeId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_UsersSF(SFTypeId)_SFTypes(SFTypeId)] FOREIGN KEY
(
[SFTypeId]
) REFERENCES [SFTypes] (
[SFTypeId]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am trying to update the table using the following code:
SqlCommandBuilder pscbBuilder;
SqlDataAdapter psdaDefault;
DataTable pdttDefault;
DataRow pdtrDefault;

pstrSql = "SELECT UserId, SFId, SFTypeId, [Description], Sort, Filter,
[Default] " +
"FROM dbo.UsersSF " +
"WHERE UserId = " + UserId + " " +
"AND SFTypeId = " + (short)SortFilter.SFTypes.SFIncidents + " " +
"AND [Default] = 1";
psdaDefault = new SqlDataAdapter( pstrSql, Connection );
pscbBuilder = new SqlCommandBuilder( psdaDefault );
psdaDefault.Fill( pdtsLookups, "UserDefault" );
pdttDefault = pdtsLookups.Tables["UserDefault"];
pdtrDefault = pdttDefault.Rows[0];
pdtrDefault["Sort"] = "Some text";
pdtrDefault["Filter"] = "Some text";
psdaDefault.UpdateCommand = pscbBuilder.GetUpdateCommand();
psdaDefault.Update( pdttDefault ); <---Fails here.
pdttDefault.AcceptChanges();


When I execute the statement, I get the following error:
SqlException: Incorrect syntax near the keyword 'Default'.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataTable dataTable) +139
EDS.WarrantyServices.ResourceConnection.IncidentsSF.Page_Load(Object
objSender, EventArgs evaEvtArgs) in
c:\inetpub\wwwroot\resconn\incidentssf.aspx.cs:143
System.Web.UI.Control.OnLoad(EventArgs e) +67
EDS.WarrantyServices.BasePage.OnLoad(EventArgs etaEventArgs)
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +739

Now my guess is that it can't handle having a column name called 'Default'
because if I remove the 'Default' column from the select everything works.
I
have tried the following to get the update to work:
- Giving 'Default' an alias
- Modifying the CommandText after it is generated by the SqlCommandBuilder
to put brackets [] around all Default column references.

Anyone have any ideas on how I can get around this other than changing the
name of the column?

Thanks in advance.
Click to expand...
 
Robert,

Try setting the QuotePrefix and QuoteSuffix properties on the
SqlCommandBuilder to "[" and "]" respectively.

CommandBuilder's don't use quoting logic for table and column names by
default. You have a column name in your table that's a reserved word -
Default. If you used the CommandText for the UpdateCommand in Query
Analyzer, you'd see the same error. By setting the QuotePrefix and
QuoteSuffix properties, you tell the SqlCommandBuilder to quote all table
and column names, which changes logic like:

DELETE UsersSF WHERE UserId = @UserId AND Default = @Default

to

DELETE [UsersSF] WHERE [UserId] = @UserId AND [Default] = @Default

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Glad to hear it, Robert. I'd recommend heeding Bill's advice. Using
keywords, spaces, etc. in your table and column names often leads to
problems like these.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top