Query causes UPDATE syntax error, when 'where-attribute' is a date-CodePudding

Whenever i try to update a table in the SQL-Database via excel-vba using a Date-formated primary key as the 'where-attribute', a syntax error appears while executing the SQLQuery; in my example: wrong syntax near ".1900". However, when i create a table in sql using the 1900er number-format to express dates(as excel saves dates: 1 = 01.01.1900; 2 = 02.01.1900 etc.), format the date-column according to that in my excel-sheet, which shall be used to update the table in SQL, and run the exact same code, no error appears and the SQL-table gets updated. Here is an extract of my used code.

strWhere = "[Date] = " & strDate

SQLQuery = "update " & TableNameSql & " " & _
"set " & _
"[Column1Data] = '" & strColumn1Data & "' " & _
"Where " & strWhere

the debug.print of the SQLQuery for both options look as following:

update TableNameSql set [Column1Data] = '-0,1149' Where [Date] = 02.01.1900 'Error

update TableNameSql set [Column1Data] = '-0,1149' Where [Date] = 2 'no Error

I believe it may have to do with the dots in the date(strWhere). It could also be, that the Problem accurs because of different language settings in excel and SQL-Server. However, creating tables and exporting data from SQL to Excel is no Problem.

I've found different similar questions asked, however, none of the answers suited my specific problem.

Using the 1900er-Date-Format is also not useful in practice, so i would like to get this error fixed while using regular dates.

CodePudding user response：

Consider using the ISO date format as YYYY-MM-DD which you can format using VBA's Format() assuming strDate is a VBA Date type. Also, be sure to enclose in single quotes.

strWhere = "[Date] = '" & Format(strDate, "YYYY-MM-DD") & "'"

Even better consider parameterization and avoid any quotation or concatention such as ADO command which allows you to align data types:

' PREPARED STATEMENT WITH QMARKS
SQLQuery = "UPDATE " & TableNameSql & " " & _ 
           "SET [Column1Data] = ? " & _
           "WHERE [Date] = ?"

' ...  LOAD ADO CONNECTION ...

Set cmd = New ADODB.Command
With cmd
   .Activeconnection= conn  
   .CommandText = SQLQuery
   .commandType = adCmdText  

   ' BIND PARAMS
   .Parameters.Append .CreateParameter("StrParam", adVarChar, adParamInput, 255, strColumn1Data)
   .Parameters.Append .CreateParameter("DateParam", adDate, adParamInput, , strDate)

   ' RUN ACTION QUERY
   .Execute()  
End With