dcsimg
Page 2 of 2 FirstFirst 12
Results 41 to 45 of 45

Thread: RichTextBox To/From SQL Server

  1. #41
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    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

  2. #42
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,954

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    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!
    Quote Originally Posted by Schmidt View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #43

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: RichTextBox To/From SQL Server

    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:
    Code:
    '   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?

  4. #44
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,954

    Re: RichTextBox To/From SQL Server

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #45
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: RichTextBox To/From SQL Server

    Quote Originally Posted by chapran View Post
    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:

    Code:
    "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

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width