RichTextBox To/From SQL Server

[Schmidt]

Driver={SQL Server Native Client 10.0};Server=(local);Database=FnsNew;Trusted_Connection=yes;
And I always got wrong result.
After you asked about connection string I decided to switch to production database just to test the code I've got from Schmidt. The connection string is:
DRIVER=SQL Server;SERVER=SRSNJC;UID=coordin;DATABASE=FnsNew;pwd=coordec

What's wrong with my connection to local server?

First of all, what you're currently using (ConnectionString-wise) is an unnecessary indirection (in both your Cnn-Strings).

ADO (a COM-lib) is meant to talk (directly) with other COM-libs (OleDB-Providers) -
but what you're using is ODBC-Drivers instead (which is IMO one unnecessary indirection too many).

Connection-Strings which contain "Driver=" are usually ODBC (with an often omitted, and leading
Connection-String-snippet -> Provider=MSDASQL.1; that gets automatically enhanced under the covers).

So, both your (ODBC-demanding) Cnn-Strings will ensure a Queue like the following:
[ADO] <-> [Generic OleDB-Provider for ODBC] <-> [specific ODBC-Driver]

If you want to avoid that, ensure that your Cnn-String does not contain the "Driver=" specifier,
but starts with a concrete "Provider=" term (leaving the generic ODBC-Mapper out of the loop):
[ADO] <-> [specific OleDB-Provider]

In my example I've used (a kind of old, "traditional" notation):
"Provider=SQLOleDB.1;...

But specific OleDB-Providers for MS-SQLServers "Native-Clients" (10 and 11) also exist -
and I've just tested my example successfully with e.g.:
"Provider=SQLNCLI10;

Olaf

[techgnome]

Wow!!!
Within many days I was fighting with this problem on my local server with the connection string
Driver={SQL Server Native Client 10.0};Server=(local);Database=FnsNew;Trusted_Connection=yes;
And I always got wrong result.
After you asked about connection string I decided to switch to production database just to test the code I've got from Schmidt. The connection string is:
DRIVER=SQL Server;SERVER=SRSNJC;UID=coordin;DATABASE=FnsNew;pwd=coordec

And the Schmidt's code WORKED!!!!

What's wrong with my connection to local server? I use it for years with no problem for any operation. And with storing Rtf data I spent many days and only today you put me to the right direction.
Thank you very much!

First of all, what you're currently using (ConnectionString-wise) is an unnecessary indirection (in both your Cnn-Strings).

ADO (a COM-lib) is meant to talk (directly) with other COM-libs (OleDB-Providers) -
but what you're using is ODBC-Drivers instead (which is IMO one unnecessary indirection too many).

Connection-Strings which contain "Driver=" are usually ODBC (with an often omitted, and leading
Connection-String-snippet -> Provider=MSDASQL.1; that gets automatically enhanced under the covers).

So, both your (ODBC-demanding) Cnn-Strings will ensure a Queue like the following:
[ADO] <-> [Generic OleDB-Provider for ODBC] <-> [specific ODBC-Driver]

If you want to avoid that, ensure that your Cnn-String does not contain the "Driver=" specifier,
but starts with a concrete "Provider=" term (leaving the generic ODBC-Mapper out of the loop):
[ADO] <-> [specific OleDB-Provider]

In my example I've used (a kind of old, "traditional" notation):
"Provider=SQLOleDB.1;...

But specific OleDB-Providers for MS-SQLServers "Native-Clients" (10 and 11) also exist -
and I've just tested my example successfully with e.g.:
"Provider=SQLNCLI10;

Olaf

Distilled version of what Olaf said - the problem was the drivers/provider you were using... I began to suspect that might be the case when I saw him use Provider=SQLOleDB.1 in his connection string... slowly the bulb started to come on, and I wondered what you were using for your provider... turns out, the wrong one. Or more precisely, you're using one that's too generic. You need to use one of the providers specific for SQL Server, as noted by Olaf's post.

-tg

[chapran]

I have not enough knowledge in SQL connection area. I tried many string formats found on internet to connect to local server on my development machine and all these not containing DRIVER word did not work:

'   Cnn.Open "PROVIDER = MSDASQL;driver={SQL Server};database=SRSNew ;server=localhost;uid=;pwd=;" ' Out of Memory when selection is done and the string is assigned to RichTextBox
'   Cnn.Open "Provider=SQLOleDB.1;Data Source=localhost;Initial Catalog=SRSNew;Integrated Security=True" '- Multi-Step Operation on Connection
'   Cnn.Open "Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=SRSNew;Integrated Security=True" '- Multi-Step Operation on Connection
'   Cnn.Open "Server=(local);Database=SRSNew;Trusted_Connection=Yes;" '-Data source not found
'   Cnn.Open "Initial Catalog=SRSNew;Data Source=(local);Integrated Security=SSPI;" '- Multi-Step Operation

I commented all attempt and gave the beginning of error messages after statements.
What is the correct connection string for local server if I use Windows authentication?
I have to use several workstations for development (office, road, home) and would not want to specify the machine in the connection string. Maybe it is just not possible to make Schmidt's code work with local server with windows authentication?

[techgnome]

When all else fails, go to ConnectionStrings
Since you are connecting to SQL Server, you'll want one of the SQL Server variety - https://www.connectionstrings.com/sql-server/
From there, I'd go with either Microsoft OLE DB Provider for SQL Server, SQL Server Native Client 11.0 OLE DB Provider, or SQL Server Native Client 10.0 OLE DB Provider
And since you want Windows Authentication, look for the Trusted Connection version...

-tg

[Schmidt]

What is the correct connection string for local server if I use Windows authentication?

I always use SQLServer-Auth with my Connections (but did allow both Logon-methods in the SQL-Server-Management-Studio),
so what worked for me just now with regards to WinAuth-Logons on my local machine is the following:

"Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=NorthWnd;Integrated Security=SSPI;"

Note, that Integrated Security=SSPI seems to be a necessity for OleDB-Providers
(instead of a simple Integrated Security=True - which seems to be sufficient with ADO.NET)

Olaf