1

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
Ali Sheikhpour
  • 10,475
  • 5
  • 41
  • 82
  • Can you post the more about how you are accessing the data? If you do not have connection issues then it must be with the data access portion of your solution. – Ross Bush Feb 05 '20 at 13:59
  • I have added the code to extract data from recordset @RossBush – Ali Sheikhpour Feb 05 '20 at 14:05
  • Is "menu" a table or a view? – Gert-Jan Feb 05 '20 at 16:17
  • It is a table with existing values when I check using SSMS. The strange thing is that all news and article exists with their ID but all text fileds like title, body,keywords etc. are being displayed as empty divs! @Gert-Jan – Ali Sheikhpour Feb 05 '20 at 16:46
  • The ODBC driver can have different (default) settings than the native SQL Server driver, such as how it handles NULLs. But that doesn't explain the behavior you are seeing for your query. Do you get the same result if you use rs("title").Value instead of rs("title") ? – Gert-Jan Feb 05 '20 at 16:56
  • Is the result same if you use `select title from menu` instead? – Flakes Feb 05 '20 at 17:02
  • 1
    No difference. I have many fields such as Website title which is selected individually but it is also empty! @Flakes – Ali Sheikhpour Feb 05 '20 at 17:16
  • 1
    I tried rs("title").value but no change. @Gert-Jan – Ali Sheikhpour Feb 05 '20 at 17:18
  • 1
    With ADO classic, I suggest you use OLE DB instead of ODBC. You could try [the latest MSOLEDBSQL driver](https://www.microsoft.com/en-us/download/details.aspx?id=56730) or the backwards compatible SQLOLEDB provider that ships with Windows if MSOLEDBSQL doesn't work. – Dan Guzman Feb 05 '20 at 21:40
  • Thank you @DanGuzman Switching to OLEDB solved the problem. – Ali Sheikhpour Feb 06 '20 at 10:14
  • 1
    Are the fields by any chance NVARCHAR(MAX)? – Erik Oosterwaal Feb 06 '20 at 10:51
  • @AliSheikhpour, you might also want to add the connection string keyword `DataTypeCompatibility=80` as ADO classic is forever stuck in the SQL 2000 world and may get confused if you use newer data types introduced in the last 20 years. – Dan Guzman Feb 06 '20 at 12:00
  • @AliSheikhpour, I expanded on my comments with an answer. – Dan Guzman Feb 06 '20 at 12:25
  • Use the [SQLOLEDB](https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15) if you want to use newer features without unexpected behaviour in ADODB. – user692942 Feb 06 '20 at 13:34
  • 1
    Does this answer your question? [Switching from sqloledb to odbc driver 13 for SQL Server](https://stackoverflow.com/questions/48660899/switching-from-sqloledb-to-odbc-driver-13-for-sql-server) (one of yours @DanGuzman) – user692942 Feb 06 '20 at 13:45

1 Answers1

2

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71