-1

If I put a bracket in parameter @Time-in and @Time-out in the cmd.Parameters.Add, I got an error

Incorrect Syntax near datetime.

But If there is no bracket, I got an error

Incorrect Syntax near '-'

cmd.CommandText = "UPDATE [q].[dbo].[d] set 
    Name=@Name," & "[Reserve_date]=
    [@Reserve_date],Room=@Room,[Time-in]=[@Time-in],[Time-
    out]=[@Time-out] where ID=@ID;"

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(SID)
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = sName
cmd.Parameters.Add("@Reserve_date", SqlDbType.DateTime).Value = sDate
cmd.Parameters.Add("@Room", SqlDbType.VarChar).Value = sRoom
cmd.Parameters.Add("@Time-in", SqlDbType.DateTime).Value = sStart
cmd.Parameters.Add("@Time-out", SqlDbType.DateTime).Value = sEnd
MatSnow
  • 7,357
  • 3
  • 19
  • 31
dvgrh1234
  • 1
  • 3
  • 1
    Your parameters **must not be enclosed** with brackets. `[@Reserve_date]`, `[@Time-in]`, `[@Time-out]` – John Woo Feb 20 '18 at 07:00
  • 1
    What the mean by `" & "` @ second line of code ? – Yogesh Sharma Feb 20 '18 at 07:00
  • "&" is used because my vs , is in word wrap – dvgrh1234 Feb 20 '18 at 07:02
  • but if it is not enclosed with bracket , i got an error Incorrect Syntax near '-' – dvgrh1234 Feb 20 '18 at 07:03
  • another thing, look at the SqlDbType types for `@Reserve_date` and `@Reserve_date`, isn't it DateTime also? – John Woo Feb 20 '18 at 07:06
  • it is in Datetime. – dvgrh1234 Feb 20 '18 at 07:14
  • https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers – Ivan Starostin Feb 20 '18 at 08:04
  • 1
    Stop bad practices. Avoid the use of special characters in tsql identifiers. Do not use 3 or 4 part object names in the tsql statements of your application without **very good reasons** and only when you understand the implications. Your connection string should be set correctly to provide the appropriate context for the server instance and database you use. Adding the database name to your table reference will only cause problems when you change the connection string (and suddenly find that your app no longer works correctly). – SMor Feb 20 '18 at 13:20

2 Answers2

0

The hyphen - is not a valid character for sql-server parameter names.
Remove it or change it to an underline _ for example.

Excerpt from TechNet

Microsoft SQL Server does not recognize variable names and stored procedure parameters that are delimited. These types of identifiers must comply with the rules for regular identifiers.

More information about identifiers can be found here:

MatSnow
  • 7,357
  • 3
  • 19
  • 31
0

You cannot name variables like that:

The first character must be one of the following:

A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

The underscore (_), at sign (@), or number sign (#). ...

Subsequent characters can include the following:

Letters as defined in the Unicode Standard 3.2.

Decimal numbers from either Basic Latin or other national scripts.

The at sign, dollar sign ($), number sign, or underscore.

see details at: msdn: db identifiers rules

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39