Incorrect syntax near....?

I keep getting this error...?

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'c'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Line 1: Incorrect syntax near 'c'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.BaseDataList.GetData() +53 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56 System.Web.UI.WebControls.BaseDataList.DataBind() +62 System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55 System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I think it talking about my SQL Query...?? If you could help me it would greatly appreciated! Thanks

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID"

I could be wrong but the error is the c.EmployeeID because the ID is IDENTITY which is a property not a column. In the mean time download SQL Prompt for free from Red Gate to use intelisense in Management Studio. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

|||You are right in saying that your query is causing the error. But I have to say that the query looks OK to me. Can you show us a little more of the code? Are you further modifying strSQLQuery in any manner?|||

Can you run this query in "Query analyser", it might give you more information

|||

It was a space... :(

|||

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID " <<<< right after eq.EquipTypeID and "!!

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
--
BUTTONS:
OK
--Are you using a database in compatibility mode? Right-click on database,
properties, Options, compatability level drop-down at top of dialog.
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility
> level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||What compatibility level is the database? I assume you have a @.query parm for the email sending, and
it seems that the database you access data from need to be in compat level 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||Master is set to 9.0
Do I have to have all databases set to 9.0?
"Will Alber" wrote:
> Are you using a database in compatibility mode? Right-click on database,
> properties, Options, compatability level drop-down at top of dialog.
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> > level
> > of the current database to a higher value to enable this feature. See help
> > for the stored procedure sp_dbcmptlevel.
> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
>
>|||You'll need the database that is issuing this error to be set to that
compatibility level - note however that this is not without it's (potential)
problems - some SQL Server 2000 features are broken in 2005 compat. level,
so tread carefully!
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> Master is set to 9.0
> Do I have to have all databases set to 9.0?
> "Will Alber" wrote:
>> Are you using a database in compatibility mode? Right-click on database,
>> properties, Options, compatability level drop-down at top of dialog.
>> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
>> message
>> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
>> > TITLE: Microsoft SQL Server Management Studio
>> > --
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch.
>> > (Microsoft.SqlServer.ConnectionInfo)
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
>> > level
>> > of the current database to a higher value to enable this feature. See
>> > help
>> > for the stored procedure sp_dbcmptlevel.
>> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
>> >
>> > --
>> > BUTTONS:
>> >
>> > OK
>> > --
>> >
>>|||I don't know what database it is. I'm simply trying to setup Database Mail
and test. Not really working with a particular database. I just assumed
that it was with the Master. I have about 6 databases on this server. -
Thanks.
"Will Alber" wrote:
> You'll need the database that is issuing this error to be set to that
> compatibility level - note however that this is not without it's (potential)
> problems - some SQL Server 2000 features are broken in 2005 compat. level,
> so tread carefully!
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> > Master is set to 9.0
> > Do I have to have all databases set to 9.0?
> >
> > "Will Alber" wrote:
> >
> >> Are you using a database in compatibility mode? Right-click on database,
> >> properties, Options, compatability level drop-down at top of dialog.
> >>
> >> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
> >> message
> >> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> >> > TITLE: Microsoft SQL Server Management Studio
> >> > --
> >> >
> >> > An exception occurred while executing a Transact-SQL statement or
> >> > batch.
> >> > (Microsoft.SqlServer.ConnectionInfo)
> >> >
> >> > --
> >> > ADDITIONAL INFORMATION:
> >> >
> >> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> >> > level
> >> > of the current database to a higher value to enable this feature. See
> >> > help
> >> > for the stored procedure sp_dbcmptlevel.
> >> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >> >
> >> > For help, click:
> >> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >> >
> >> > --
> >> > BUTTONS:
> >> >
> >> > OK
> >> > --
> >> >
> >>
> >>
> >>
>
>|||You might also try msdb, I believe this is where all the dbmail stuff lives.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>I don't know what database it is. I'm simply trying to setup Database Mail
> and test. Not really working with a particular database. I just assumed
> that it was with the Master. I have about 6 databases on this server. -
> Thanks.|||... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
works, lower one at a time and see which one caused the problems.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> You might also try msdb, I believe this is where all the dbmail stuff lives.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>>I don't know what database it is. I'm simply trying to setup Database Mail
>> and test. Not really working with a particular database. I just assumed
>> that it was with the Master. I have about 6 databases on this server. -
>> Thanks.
>|||Changing msdb to 9.0 worked.
Thanks.
Problem resolved.
"Tibor Karaszi" wrote:
> ... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
> works, lower one at a time and see which one caused the problems.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> > You might also try msdb, I believe this is where all the dbmail stuff lives.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sqlblog.com/
> > http://www.aspfaq.com/5006
> >
> >
> >
> > "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> > news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
> >>I don't know what database it is. I'm simply trying to setup Database Mail
> >> and test. Not really working with a particular database. I just assumed
> >> that it was with the Master. I have about 6 databases on this server. -
> >> Thanks.
> >
> >
>|||> Changing msdb to 9.0 worked.
> Thanks.
> Problem resolved.
Since I was able to reproduce this in Katmai I filed an issue on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284340
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476[/url]
BUTTONS:
OK
What compatibility level is the database? I assume you have a @.query parm for the email sending, and
it seems that the database you access data from need to be in compat level 90.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
|||Master is set to 9.0
Do I have to have all databases set to 9.0?
"Will Alber" wrote:

> Are you using a database in compatibility mode? Right-click on database,
> properties, Options, compatability level drop-down at top of dialog.
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
>
>
|||I don't know what database it is. I'm simply trying to setup Database Mail
and test. Not really working with a particular database. I just assumed
that it was with the Master. I have about 6 databases on this server. -
Thanks.
"Will Alber" wrote:

> You'll need the database that is issuing this error to be set to that
> compatibility level - note however that this is not without it's (potential)
> problems - some SQL Server 2000 features are broken in 2005 compat. level,
> so tread carefully!
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
>
>
|||You might also try msdb, I believe this is where all the dbmail stuff lives.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>I don't know what database it is. I'm simply trying to setup Database Mail
> and test. Not really working with a particular database. I just assumed
> that it was with the Master. I have about 6 databases on this server. -
> Thanks.
|||... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
works, lower one at a time and see which one caused the problems.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> You might also try msdb, I believe this is where all the dbmail stuff lives.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>
|||Changing msdb to 9.0 worked.
Thanks.
Problem resolved.
"Tibor Karaszi" wrote:

> ... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
> works, lower one at a time and see which one caused the problems.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
>
|||> Changing msdb to 9.0 worked.
> Thanks.
> Problem resolved.
Since I was able to reproduce this in Katmai I filed an issue on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284340
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
BUTTONS:
OK
--Are you using a database in compatibility mode? Right-click on database,
properties, Options, compatability level drop-down at top of dialog.
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility
> level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||What compatibility level is the database? I assume you have a @.query parm fo
r the email sending, and
it seems that the database you access data from need to be in compat level 9
0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility leve
l
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...25&LinkId=20476
> --
> BUTTONS:
> OK
> --
>

Incorrect syntax near ).

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error:

Line 42: objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
Line 43: ' Fill the dataset.
Line 44: objAdapter.Fill(objDataset)
Line 45: ' Create a new view.
Line 46: Dim oView As New DataView(objDataset.Tables(0))

 Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Dim AllorOne As String Dim company As String AllorOne = Request.QueryString("AllorOne") company = Request.QueryString("company")' Create a connection and open it. Dim objConn As New System.Data.SqlClient.SqlConnection("User ID=******;Password=******;Initial Catalog=customer_config;Data Source=rqa4-sql01;") objConn.Open() Dim strSQL As String Dim objDataset As New DataSet() Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter() ' Get all the sql If AllorOne ="All" Then strSQL ="SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) order by company_name" Else strSQL ="SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) where lower(company_name) like '%" & company &"%' order by company_name" End If objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)' Fill the dataset. objAdapter.Fill(objDataset) ' Create anew view. Dim oView As New DataView(objDataset.Tables(0)) ' Set up the data grid and bind the data. SimpleDataGrid.DataSource = oView SimpleDataGrid.DataBind() End Sub

Can anyone tell me what causes this type of error, what they think is wrong, or what IS wrong if someone wants to take the time to go through the code. Anything helps. Eh, I feel like one of those guys on a street corner with a sign when I say that.

Since the exception that was thrown is of type: System.Data.SqlClient.SqlException, then the error is in the sql statement itself and not in the code that is calling it.

can you run your sql statement in query analyzer?

|||

Visual Web Developer "helped" me out by adding "(" and ")" around my "from" statement in the SQL. Thanks for pointing out it had to be in the SQL.

|||

I am having the same problem except I dont know where the problem is after reading your posts. Could someone please explain a bit more on where I can find/fix the error in SQL statement??

INCORRECT SYNTAX NEAR "STRING" FOR ALTER SQL

HELP

I am trying to create a new column for every file in a folder

but i keep getting an sql exception - incorrect syntax near ' whatever the value of the file name is'

it works if i just type in the value directly

my code look like this

fsofolder = CreateObject("Scripting.FileSystemObject")
folder = fsofolder.GetFolder("the path to the Files\")
files = folder.Files
For Each objfile In files
sname = objfile.Name

cmd3.CommandText = "ALTER TABLE NEW ADD " & "' " & sname & " ' " & " nvarchar(MAX)"

DatabaseConnection.Open()

Try

cmd3.Connection = DatabaseConnection
cmd3.ExecuteNonQuery()
Catch ex As SqlException
MsgBox(ex.Message)
End Try

DatabaseConnection.Close()

The syntax should be Alter TabletablenameADD COLUMNcolumnname datatype

There is no place for apostophe delimiters in the syntax, and the word COLUMN is needed too.

|||

Thanks

I figured out what the problem was

cmd3.CommandText = "ALTER TABLEtablename ADD " & "'[" & sname & "]" & " nvarchar(MAX)"

It was not accepting eg Q45654656.txt as a column name

but accepting [Q45654656]

|||

database objects can't have a '.' in their names

|||

It did actually

I missed-type in the last post

the difference was the [] that enclosed the string

it accepted

sname = [textfile.txt]

but not

sname = textfile.txt

as the column name

|||

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

|||

Well, the a

fredi:

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

Well, why don't you type in that sql statement for yourself and tell us? :)

However, wanting to do what you asked does not make sense to me. I could understand if you said you wanted to update a column only if it was null, because presumably you don't want to lose the old value. By definition, if you want to insert a record, the record shouldn't already exist, so how could a non-existent record have a value in any column?

FYI, it is possible to say (instead of the VALUES (value1, etc.)), SELECT value1, value2, etc.

|||

well here is what i am trying to do and able to do so far

-look into a folder

-create a database table in sql server using the create sql command

-alter the table and create a column named for each file name in the folder

-read each of the text file data into each column

however if i run the code again it adds the textfile data into the same columns again

I just need a statement to say if the column already has data then don't do the all the above steps

I hope this explains my situation

These work:

cmd2.CommandText = "CREATE TABLE " & DatabaseTableName & "(" & ISTCOLUMN& " nvarchar(MAX))"
cmd3.CommandText = "ALTER TABLE " & DatabaseTableName & " ADD " & sname1 & " nvarchar(MAX)"

cmd4.CommandText = "INSERT INTO " & DatabaseTableName & "( " & sname1 & " )" & "VALUES ( '" & filefields(i) & "' )"

How will I check if sname column is Null and only insert the values of filefields into it?

thanks

|||

Am I correct in saying the following?

If the column exists in the table, then you must have populated it with a value?

Because if that is true, then all you have to do is query INFORMATION_SCHEMA.COLUMNS and find out if the column exists.

If that is not true, then you can query the table to see if the column exists.

If it does, query the table to see if it has a row at all, and if so, a value in the column you are interested in.

If yes, do nothing.

If no, update the record.

Now, I have to tell you that what you are doing almost certainly violates relational data modeling.

I would be EXTREMELY SUSPICIOUS of a database design that required me to add a column to a table for every file in a directory.

The odds of this being a good database design are very, very low. Lower than the chance of my being hit by lightning this year.

Standard relational theory would tell us to create a ROW, not a COLUMN, for every file in the directory.

I am not telling you that your database design is wrong. I am telling you that it is very likely wrong, and that you should re-think your approach to be very, very sure the approach you are taking is the right one.

How many files might there be in the directory? Did you know there are limits as to how many columns can be defined for a table? Will you have more than that limit? Did you know that there are limits as to the number of bytes that can be returned for a row in a query? How many filename columns with their values will it take to go over that limit?

See <http://technet.microsoft.com/en-us/library/ms143432.aspx> for details on sql server limits.

Please reconsider your design or - to educate us all - explain why the situation you are in requires such an unusual design.

|||

Thanks David,

If the column exists in the table, then you must have populated it with a value?

is not true. I first create an empty table with at least one column then I add more columns as they show up (i.e as the text files get created). That might not be as important now as the structure of the database itself.

To say that I am fairly new to Database design would be an understatement. Thanks for enlightening me. I am still in an early stage of the design phase and you just showed me how flawed the database would be if I end up going over limits. I would reconsider my approach.

|||

Glad to have helped! I've got 25 years of computing mistakes behind me, so it's easier for me to recognize them.. Some of them are old friends. :)

So, to wrap up this thread, the correct answer is "Don't do it."