CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.

Knowledge Base

Breadrumbs:
>>Development (Web and Desktop) >> Web Development >> ASP (Active Server Pages)
Microsoft OLE DB Provider for SQL Server (0x80040E14) Incorrect syntax near the keyword 'off' or Incorrect syntax near the keyword 'on'

Share With Friends ^{(Updated 6-8-2010)}

Article ID:
74
Date Created
Monday, December 20, 2010
Last Updated
Monday, December 20, 2010
This Article Has been Viewed
2330 times
Short Desc
When migrating from Access Database to SQL Server, you will have to change your ASP code to work with the new database settings.
Details
When you migrate from Access Database to SQL Server, there are several things that need to be changed.
Access uses the Yes/No Data Type, and in SQL Server you can use the BIT Data Type.
Once you convert over to BIT in SQL Server, you will get an error like this.

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'off'.
/NewInserts.asp, line 96

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'on'.
/NewInserts.asp, line 96
Recreate Issue
To recreate the error. Set Data Type to BIT and use the following SQL Statement.

<%
sql.commandtext="update mytable set mycol=off where myid=1"
%>

<%
sql.commandtext="update mytable set mycol=on where myid=1"
%>

In Access with the Yes/No Data Type, this code is correctly written, but not in SQL Server.
Resolve Issue
In SQL Server we are using the BIT value and we will need to change the off to 0
0 = off
1 - on

<%
sql.commandtext="update mytable set mycol=0 where myid=1"
%>

<%
sql.commandtext="update mytable set mycol=1 where myid=1"
%>

--------
Related Articles
Microsoft OLE DB Provider for SQL Server (0x80040E14) Invalid column name ''«

All Topics