I always used this connection string in my codes to connect to sql server:
"Driver={SQL Server};Server=SERVER_ADDRESS;Database=DBNAME;User Id=MYUSER;Password=******;"
In a new website when I deployed the project on the real server I noticed that I have to use a new connection string due to sql server version:
"Driver={ODBC Driver 17 for SQL Server};Server=SERVER_ADDRESS;Database=DBNAME;User Id=MYUSER;Password=*****;"
The connection works fine and the website is working without error but all values in recordsets are empty! I have 5 menus as there are 5 records in database but all menus are displaying empty text! In fact all texts are empty however there are text records in database (when I check with SSMS). Do I need to add something new to my codes or to the connection string?
This is how I get data from database:
Set objcon = Server.CreateObject("ADODB.Connection")
objcon.connectionString="Driver={ODBC Driver 17 for SQL Server};Server=SERVER_ADDRESS;Database=DBNAME;User Id=MYUSER;Password=*****;"
objcon.Open
Set rs= Server.CreateObject("ADODB.RecordSet")
rs.CursorType = 2
rs.open "select * from menu",objcon
while not rs.eof
Response.Write rs("title") 'I get 5 empty texts here as I have 5 record in database
rs.movenext
wend
rs.close
It is best to use OLE DB with ADO classic. ADO is natively a COM-based API and uses the MSDASQL (Microsoft OLE DB Provider for ODBC drivers) on top of the ODBC call-level interface, adding an addition API layer.
Windows ships with SQLOLEDB (Microsoft OLE DB Provider for SQL Server) for backwards compatibility. This driver is intended to be used only for legacy application APIs like ADO classic. ADO classic is forever stuck in the SQL 2000 world and lacks data type mappings for newer SQL Server data types introduced in the last 20 years. With It is a good practice to also specify the DataTypeCompatibility=80 connection string keyword to avoid surprises.
You could also use the MSOLEDBSQL driver (Microsoft OLE DB Driver for SQL Server), which is a separate download as are all modern SQL Server drivers. However, there are behavior differences between SQLOLEDB and MSOLEDBSQL that may require additional application testing.