locked
C# Syntax Error - System.Data.SqlClient.SqlException: Incorrect syntax near ','. Incorrect syntax near the keyword 'AS'. RRS feed

  • Question

  • I am fairly new to software development and am encountering an error in my code which I believe is syntax related, but I have been unable to determine what exactly the issue is.

    However, I believe the issue is with the SQL query as it sits in the C# code.

    When run the exact same SQL query from within SQL Management Studio, the query works flawlessly and returns the expected data, so I believe my problem is when I applied the working code to the C# file as shown below.

           public void GetTheData(string caseid, string callback) 
            {
                string dktcentury = caseid.Remove(1);
                string dktyear = caseid.Substring(1, 2);
                string dktsequence = caseid.Substring(3);
    
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                //string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DemoDBConnection"].ConnectionString;
                SqlConnection cs = new SqlConnection(ConfigurationManager.ConnectionStrings["CIVILCMSQA"].ConnectionString);
                string strSQL = "WITH ResultTable AS " +
                                "(SELECT " +
                                ",RHO.[Plaintiff] " +
                                ",RHO.[Defendant] " +
                                ",CASE MAX(J.[JudgeCode]) " +
                                "WHEN '' THEN NULL " +
                                "WHEN NULL THEN NULL " +
                                "ELSE MAX(J.[First] " +
                                "J.[Middle] " +
                                "J.[Last]) " +
                                "END AS [Judge Name] " +
                                "FROM " +
                                "(SELECT  [DktCentury] " +
                                ",[DktYear] " +
                                ",[DktSequence] " +
                                ",MAX([Plaintiff]) as [Plaintiff] " +
                                ",MAX([Defendant]) as [Defendant] " +
                                "FROM ((SELECT [DktCentury] " +
                                ",[DktYear] " +
                                ",[DktSequence] " +
                                ",[CaptionName] as [Plaintiff] " +
                                ",NULL as [Defendant] " +
                                "FROM [civil_cms_qa].[dbo].[PartyCaseXref] " +
                                "WHERE abs(ID) = (select min(abs(ID)) from (SELECT [ID] " +
                                ",[CaptionName]  as [Plaintiff] " +
                                ",NULL as [Defendant] " +
                                ",[CaptionSeq] " +
                                "FROM [civil_cms_qa].[dbo].[PartyCaseXref] " +
                                "WHERE DktCentury = '@dktcentury' and DktYear = '@dktyear' " +
                                "and DktSequence = '@dktsequence' and PartyType = 'Plaintiff') AS FOO)) " +
                                "UNION all " +
                                "(SELECT [DktCentury] " +
                                ",[DktYear] " +
                                ",[DktSequence] " +
                                ",NULL as [Plaintiff] " +
                                ",[CaptionName]  as [Defendant] " +
                                "FROM [civil_cms_qa].[dbo].[PartyCaseXref] " +
                                "WHERE abs(ID) = (select min(abs(ID)) from (SELECT [ID] " +
                                ",NULL as [Plaintiff] " +
                                ",[CaptionName] as [Defendant] " +
                                ",[CaptionSeq] " +
                                "FROM [civil_cms_qa].[dbo].[PartyCaseXref] " +
                                "WHERE DktCentury = '@dktcentury' and DktYear = '@dktyear' " +
                                "and DktSequence = '@dktsequence' and PartyType = 'Defendant') AS FOI))) as POI " +
                                "GROUP BY  [DktCentury],[DktYear],[DktSequence]) AS RHO " +
                                "INNER JOIN [civil_cms_qa].[dbo].[Master] M ON M.[DktCentury] = RHO.[DktCentury] " +
                                "and M.[DktYear] = RHO.[DktYear] and M.[DktSequence] = RHO.[DktSequence] " +
                                "LEFT JOIN [civil_cms_qa].[dbo].[Judge] J ON J.[JudgeCode] = M.[JudgeCode] " +
                                "GROUP BY RHO.[Plaintiff],RHO.[Defendant]) " +
                                "SELECT * FROM ResultTable";
                      
                SqlCommand SQLCmd = new SqlCommand(strSQL, cs);
                adapter.SelectCommand = SQLCmd;
                SQLCmd.Parameters.AddWithValue("@dktcentury", dktcentury);
                SQLCmd.Parameters.AddWithValue("@dktyear", dktyear);
                SQLCmd.Parameters.AddWithValue("@dktsequence", dktsequence);
    
                DataSet custDS = new DataSet();
                adapter.Fill(custDS, "ResultTable");
    
                if (custDS.Tables[0].Rows.Count == 0)
                {
                    string ResponseError = "Error";
                    StringBuilder sb = new StringBuilder();
                    JavaScriptSerializer js = new JavaScriptSerializer();
                    sb.Append(callback + "(");
                    sb.Append(js.Serialize(ResponseError));
                    sb.Append(");");
                    Context.Response.Clear();
                    Context.Response.ContentType = "application/json";
                    Context.Response.Write(sb.ToString());
                    Context.Response.End();
                }
                else
                {
                    string Plaintiff = Convert.ToString(custDS.Tables[0].Rows[0][0].ToString());
                    string Defendant = Convert.ToString(custDS.Tables[0].Rows[0][1].ToString());
                    string Judge = Convert.ToString(custDS.Tables[0].Rows[0][2].ToString());
                    string Result = Plaintiff.ToString() + ": " + Defendant.ToString() + ": " + Judge.ToString();
                    StringBuilder sb = new StringBuilder();
                    JavaScriptSerializer js = new JavaScriptSerializer();
                    sb.Append(callback + "(");
                    sb.Append(js.Serialize(Result));
                    sb.Append(");");
                    Context.Response.Clear();
                    Context.Response.ContentType = "application/json";
                    Context.Response.Write(sb.ToString());
                    Context.Response.End();
                }
    
            }

    According to the Visual Studio debugger, the error is triggered when line 145 executes, which is

    adapter.Fill(custDS, "ResultTable");

    The error message that appears on my results page is shown below.

    System.Data.SqlClient.SqlException: Incorrect syntax near ','.
    Incorrect syntax near the keyword 'AS'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at CRSuiteGetCourtCaseWebSvc.RetrieveCivilCaseInfo.GetCivilCaseInfo(String caseid, String callback) in GetTheData.asmx.cs:line 145

    I am hoping that someone who has been doing this for a long time, can take a quick look at this, and sees an obvious problem which they can point out to me, so that I can fix it.

    Any help, would be greatly appreciated.

    Thanks in advance!


    • Edited by ACECORP Friday, December 2, 2016 3:30 PM
    Friday, December 2, 2016 3:10 PM

Answers

  • There's a bug right here:

                string strSQL = "WITH ResultTable AS " +
                                "(SELECT " +
                                ",RHO.[Plaintiff] " +
    

    Remove the comma, or add something between the SELECT and the comma.

    Another syntax error here:

                                "ELSE MAX(J.[First] " +
                                "J.[Middle] " +
                                "J.[Last]) " +
    

    Perhaps you meant to concatenate the strings (this assumes none of them is NULL):

                                "ELSE MAX(J.[First] " +
                                "+ ' ' + J.[Middle] " +
                                "+ ' ' + J.[Last]) " +

    Also, remove the single quotation marks around parameter placeholders:

                                "WHERE DktCentury = '@dktcentury' and DktYear = '@dktyear' " +
                                "and DktSequence = '@dktsequence' and PartyType = 'Plaintiff') AS FOO)) " +
    

    That should be written as:

                                "WHERE DktCentury = @dktcentury and DktYear = @dktyear " +
                                "and DktSequence = @dktsequence and PartyType = 'Plaintiff') AS FOO)) " +
    I suggest using the @"" verbatim string syntax so that you can put newlines in the string and not have to concatenate it from parts with the plus operator. That would make the source code easier to read.

    • Edited by ranta Friday, December 2, 2016 4:56 PM argument of MAX
    • Marked as answer by ACECORP Friday, December 2, 2016 6:43 PM
    Friday, December 2, 2016 4:39 PM
  • SqlCommand SQLCmd = new SqlCommand(strSQL, cs);

    What you can do is if you know how to use the VS debugger and QuickWatch, you can set a breakpoint, use QuickWatch on SQLCmd object, go into the object and get the TSQL that the command is going to execute and copy/past the TSQL into the SSMS TSQL Query Execution pane and execute the TSQL to find the error. You have to do this right before SQLCmd on the query is executed by the SQLCmd. 

    • Marked as answer by ACECORP Friday, December 2, 2016 5:39 PM
    Friday, December 2, 2016 4:40 PM

All replies

  • I was able to find one of the two errors.

    Incorrect syntax near the keyword 'AS'.

    That particular error is being triggered by the following line of code.

    "GROUP BY  [DktCentury],[DktYear],[DktSequence]) AS RHO " +

    The problem is that

    AS RHO

    is needed to join that result set and further manipulate the data to get exactly what is needed using the last lines of code shown below

    "GROUP BY  [DktCentury],[DktYear],[DktSequence]) AS RHO " +
                                "INNER JOIN [civil_cms_qa].[dbo].[Master] M ON M.[DktCentury] = RHO.[DktCentury] " +
                                "and M.[DktYear] = RHO.[DktYear] and M.[DktSequence] = RHO.[DktSequence] " +
                                "LEFT JOIN [civil_cms_qa].[dbo].[Judge] J ON J.[JudgeCode] = M.[JudgeCode] " +
                                "GROUP BY RHO.[Plaintiff],RHO.[Defendant]) " +
                                "SELECT * FROM ResultTable";

    Any suggestions on how to get around this would be appreciated.

    If I can locate the source of the other error

    System.Data.SqlClient.SqlException: Incorrect syntax near ','.

    I will post that too, so as to make addressing it easier.

    Friday, December 2, 2016 3:36 PM
  • There's a bug right here:

                string strSQL = "WITH ResultTable AS " +
                                "(SELECT " +
                                ",RHO.[Plaintiff] " +
    

    Remove the comma, or add something between the SELECT and the comma.

    Another syntax error here:

                                "ELSE MAX(J.[First] " +
                                "J.[Middle] " +
                                "J.[Last]) " +
    

    Perhaps you meant to concatenate the strings (this assumes none of them is NULL):

                                "ELSE MAX(J.[First] " +
                                "+ ' ' + J.[Middle] " +
                                "+ ' ' + J.[Last]) " +

    Also, remove the single quotation marks around parameter placeholders:

                                "WHERE DktCentury = '@dktcentury' and DktYear = '@dktyear' " +
                                "and DktSequence = '@dktsequence' and PartyType = 'Plaintiff') AS FOO)) " +
    

    That should be written as:

                                "WHERE DktCentury = @dktcentury and DktYear = @dktyear " +
                                "and DktSequence = @dktsequence and PartyType = 'Plaintiff') AS FOO)) " +
    I suggest using the @"" verbatim string syntax so that you can put newlines in the string and not have to concatenate it from parts with the plus operator. That would make the source code easier to read.

    • Edited by ranta Friday, December 2, 2016 4:56 PM argument of MAX
    • Marked as answer by ACECORP Friday, December 2, 2016 6:43 PM
    Friday, December 2, 2016 4:39 PM
  • SqlCommand SQLCmd = new SqlCommand(strSQL, cs);

    What you can do is if you know how to use the VS debugger and QuickWatch, you can set a breakpoint, use QuickWatch on SQLCmd object, go into the object and get the TSQL that the command is going to execute and copy/past the TSQL into the SSMS TSQL Query Execution pane and execute the TSQL to find the error. You have to do this right before SQLCmd on the query is executed by the SQLCmd. 

    • Marked as answer by ACECORP Friday, December 2, 2016 5:39 PM
    Friday, December 2, 2016 4:40 PM
  • Thanks! Your suggestion allowed me to find and solve my problem! The code error was really with the CASE ELSE

    This is the corrected code that ended up fixing it

     "ELSE MAX(J.[First] + ' ' + J.[Middle] + ' ' + J.[Last]) " +



    • Edited by ACECORP Friday, December 2, 2016 5:38 PM
    Friday, December 2, 2016 5:23 PM
  • Ranta, I don't know how I missed your post, but I found and corrected two of those things shortly after the initial post, before I ultimately I ran the quick watch and checked the query in the SSMS TSQL Execution Pane to find the issue with the CASE ELSE END.

    But reading it over now, yes those three things were what I ultimately corrected.


    • Edited by ACECORP Friday, December 2, 2016 6:44 PM
    Friday, December 2, 2016 6:42 PM