none
SQL80001: Incorrect syntax near 'Order' RRS feed

  • Question

  • Why am i having Incorrect Syntax near Order in C# SQL Query but when i do the same method in another query, there is no problem in the query.

    Below is Insert and Values on SQL Query

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[ORDER_INSERT] 
    @Username AS NVARCHAR(256),
    @ProductName AS NVARCHAR(256),
    @ProductQuantity AS INT,
    @ProductPrice AS MONEY,
    @TPRICE  AS NVARCHAR(256),
    @STATUS AS NVARCHAR(256),
    @ProductImage as nvarchar(500)

    AS
    INSERT INTO Order ([Username], [ProductName], [ProductQuantity], [ProductPrice], [TPrice], [Status], [ProductImage]) 
    VALUES (@Username, @ProductQuantity, @ProductQuantity, @ProductPrice, @TPRICE, @STATUS, @ProductImage)

    RETURN

    Below is the table

    CREATE TABLE [dbo].[Order] (
        [OrderID]         INT            IDENTITY (1, 1) NOT NULL,
        [Username]        NVARCHAR (50)  NULL,
        [ProductName]     NVARCHAR (50)  NULL,
        [ProductQuantity] INT            NULL,
        [ProductPrice]    MONEY     NULL,
        [TPrice]          INT     NULL,
        [Status]          INT            NULL,
        [EntryDate]       DATETIME       NULL,
        [ProductImage]    NVARCHAR (500) NULL,
        CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
    );

    Wednesday, May 1, 2019 4:29 AM

Answers

All replies

  • Try this:

       INSERT INTO [Order] …

     

    Wednesday, May 1, 2019 4:41 AM
  • Add the owner/schema of your table:

    INSERT INTO [dbo].[Order] ...

    wizend

    Wednesday, May 1, 2019 6:33 AM
  • Hint: If you paste the query into SSMS, you will see that the word "Order" in "INSERT INTO Order ..." is colored blue instead of black. This tells you that it is a reserved keyword (it is used in the "order by..." part of a query).

    When you have a table name or a field name that is a reserved word, you can "escape it" by enclosing it in square brackets: [Order]. This removes the special meaning and it is treated as an identifier. This is the reason why you got the suggestion for "INSERT INTO [Order] …" in a previous answer.

    The recommendation of prepending the schema ("INSERT INTO [dbo].[Order] ...") serves to avoid the problem that would be caused if the user executing the query has a default schema and another table named "Order" existed in the user's default schema. This would cause the query to run against the other Order table and not the one under the dbo schema.

    Wednesday, May 1, 2019 7:40 AM
    Moderator
  • Hello,

    If you are just starting out with the database I would recommend renaming the table from Order to Orders and never have to be concerned about having to use brackets anytime you access this table.

    When creating table check the proposed name against the reserve word list.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 1, 2019 9:36 AM
    Moderator