none
incorrect syntax RRS feed

  • Question

  • string strQuery = "Select p.project_Id, p.Estimator_Name, p.Project_Name, p.Estimation_Date,";
                strQuery += "t.type_Id, t.project_Id, t.Type, t.Description, t.Quantity, mr.project_Id, mr.type_Id, mr.Metal_Reinforcement, mr.Quantity, mr.Price_Bar, tw.project_Id, tw.type_Id, tw.Tie_Wire_Kilogram,";
                strQuery += "tw.Price_Kilogram, c.project_Id, c.type_Id, c.Cement_Bag, c.Price_Bag, g.project_Id, g.type_Id, g.Gravel_CubicMeter, g.Price_Gravel, s.project_Id, s.type_Id, s.Sand_CubicMeter, s.Price_Sand,";
                strQuery += "hb.project_Id, hb.type_Id, hb.CHB_Size, hb.Quantity, hb.Price_Chb from projects p, type t, metal_reinforcement mr, tie_wire tw, cement c,";
                strQuery += "gravel g, sand s, hollow_blocks hb where p.project_Id = " + Convert.ToInt64(PROJECT_ID) + "";
                strQuery += "and t.project_Id = p.project_Id and mr.type_Id = t.type_Id and mr.project_Id = p.project_Id and tw.type_Id = t.type_Id and tw.project_Id = p.project_Id";
                strQuery += "and c.type_Id = t.type_Id and c.project_Id = p.project_Id and g.type_Id = t.type_Id and g.project_Id = p.project_Id";
                strQuery += "and s.type_Id = t.type_Id and s.project_Id = p.project_Id and hb.type_Id = t.type_Id and hb.project_Id = p.project_Id";
    Tuesday, July 30, 2013 1:28 AM

Answers

  • Hi keijie

    It's more like a question about SQL.

    Have you debug your project, what the string "strQuery" exactly is when debugging your project?

    The best way I think is to past the SQL statement in the query window and run it in the database. Check if there is anything wrong with your SQL statement.

    Or you may post your SQL statement in the T-SQL forum to get more help from more experts in SQL.

    Good luck.


    Caillen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 31, 2013 2:15 AM
    Moderator
  • Hi,

    you left out some required whitespace in the conditions.
    To find such a problem you can use a Console.WriteLine(strQuery); to get the concatenated query text. To make the investigation simpler some line breaks can help:

                string PROJECT_ID = "12345";
    
                string strQuery = "Select p.project_Id, p.Estimator_Name, p.Project_Name, p.Estimation_Date,"
                  + "t.type_Id, t.project_Id, t.Type, t.Description, t.Quantity, mr.project_Id, mr.type_Id, mr.Metal_Reinforcement, mr.Quantity, mr.Price_Bar, tw.project_Id, tw.type_Id, tw.Tie_Wire_Kilogram,"
                  + "tw.Price_Kilogram, c.project_Id, c.type_Id, c.Cement_Bag, c.Price_Bag, g.project_Id, g.type_Id, g.Gravel_CubicMeter, g.Price_Gravel, s.project_Id, s.type_Id, s.Sand_CubicMeter, s.Price_Sand,"
                  + "hb.project_Id, hb.type_Id, hb.CHB_Size, hb.Quantity, hb.Price_Chb\r\n"
                  + "from projects p, type t, metal_reinforcement mr, tie_wire tw, cement c,gravel g, sand s, hollow_blocks hb\r\n"
                  + "where p.project_Id = " + Convert.ToInt64(PROJECT_ID) + "\r\n" 
                  + "and t.project_Id = p.project_Id and mr.type_Id = t.type_Id and mr.project_Id = p.project_Id and tw.type_Id = t.type_Id and tw.project_Id = p.project_Id\r\n"
                  + "and c.type_Id = t.type_Id and c.project_Id = p.project_Id and g.type_Id = t.type_Id and g.project_Id = p.project_Id\r\n"
                  + "and s.type_Id = t.type_Id and s.project_Id = p.project_Id and hb.type_Id = t.type_Id and hb.project_Id = p.project_Id;";
    
                Console.WriteLine(strQuery);

    As C# supports literal concatenation the strQuery += isn't necessary.

    I would even add more line breaks and use the ANSI INNER JOIN Syntax as it avoids other problems as accidental cross joins.

    Regards, Elmar



    • Edited by Elmar Boye Wednesday, July 31, 2013 9:25 AM literal
    • Marked as answer by CaillenModerator Tuesday, August 6, 2013 2:07 AM
    Wednesday, July 31, 2013 9:24 AM

All replies

  • string strQuery = "Select p.project_Id, p.Estimator_Name, p.Project_Name, p.Estimation_Date,";
                strQuery += "t.type_Id, t.project_Id, t.Type, t.Description, t.Quantity, mr.project_Id, mr.type_Id, mr.Metal_Reinforcement, mr.Quantity, mr.Price_Bar, tw.project_Id, tw.type_Id, tw.Tie_Wire_Kilogram,";
                strQuery += "tw.Price_Kilogram, c.project_Id, c.type_Id, c.Cement_Bag, c.Price_Bag, g.project_Id, g.type_Id, g.Gravel_CubicMeter, g.Price_Gravel, s.project_Id, s.type_Id, s.Sand_CubicMeter, s.Price_Sand,";
                strQuery += "hb.project_Id, hb.type_Id, hb.CHB_Size, hb.Quantity, hb.Price_Chb from projects p, type t, metal_reinforcement mr, tie_wire tw, cement c,";
                strQuery += "gravel g, sand s, hollow_blocks hb where p.project_Id = " + Convert.ToInt64(PROJECT_ID) + "";
                strQuery += "and t.project_Id = p.project_Id and mr.type_Id = t.type_Id and mr.project_Id = p.project_Id and tw.type_Id = t.type_Id and tw.project_Id = p.project_Id";
                strQuery += "and c.type_Id = t.type_Id and c.project_Id = p.project_Id and g.type_Id = t.type_Id and g.project_Id = p.project_Id";
                strQuery += "and s.type_Id = t.type_Id and s.project_Id = p.project_Id and hb.type_Id = t.type_Id and hb.project_Id = p.project_Id";
    the error say's incorrect syntax near 'c'
    Tuesday, July 30, 2013 1:31 AM
  • Hi keijie

    It's more like a question about SQL.

    Have you debug your project, what the string "strQuery" exactly is when debugging your project?

    The best way I think is to past the SQL statement in the query window and run it in the database. Check if there is anything wrong with your SQL statement.

    Or you may post your SQL statement in the T-SQL forum to get more help from more experts in SQL.

    Good luck.


    Caillen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 31, 2013 2:15 AM
    Moderator
  • Hi,

    you left out some required whitespace in the conditions.
    To find such a problem you can use a Console.WriteLine(strQuery); to get the concatenated query text. To make the investigation simpler some line breaks can help:

                string PROJECT_ID = "12345";
    
                string strQuery = "Select p.project_Id, p.Estimator_Name, p.Project_Name, p.Estimation_Date,"
                  + "t.type_Id, t.project_Id, t.Type, t.Description, t.Quantity, mr.project_Id, mr.type_Id, mr.Metal_Reinforcement, mr.Quantity, mr.Price_Bar, tw.project_Id, tw.type_Id, tw.Tie_Wire_Kilogram,"
                  + "tw.Price_Kilogram, c.project_Id, c.type_Id, c.Cement_Bag, c.Price_Bag, g.project_Id, g.type_Id, g.Gravel_CubicMeter, g.Price_Gravel, s.project_Id, s.type_Id, s.Sand_CubicMeter, s.Price_Sand,"
                  + "hb.project_Id, hb.type_Id, hb.CHB_Size, hb.Quantity, hb.Price_Chb\r\n"
                  + "from projects p, type t, metal_reinforcement mr, tie_wire tw, cement c,gravel g, sand s, hollow_blocks hb\r\n"
                  + "where p.project_Id = " + Convert.ToInt64(PROJECT_ID) + "\r\n" 
                  + "and t.project_Id = p.project_Id and mr.type_Id = t.type_Id and mr.project_Id = p.project_Id and tw.type_Id = t.type_Id and tw.project_Id = p.project_Id\r\n"
                  + "and c.type_Id = t.type_Id and c.project_Id = p.project_Id and g.type_Id = t.type_Id and g.project_Id = p.project_Id\r\n"
                  + "and s.type_Id = t.type_Id and s.project_Id = p.project_Id and hb.type_Id = t.type_Id and hb.project_Id = p.project_Id;";
    
                Console.WriteLine(strQuery);

    As C# supports literal concatenation the strQuery += isn't necessary.

    I would even add more line breaks and use the ANSI INNER JOIN Syntax as it avoids other problems as accidental cross joins.

    Regards, Elmar



    • Edited by Elmar Boye Wednesday, July 31, 2013 9:25 AM literal
    • Marked as answer by CaillenModerator Tuesday, August 6, 2013 2:07 AM
    Wednesday, July 31, 2013 9:24 AM