Results 1 to 6 of 6

Thread: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    Hi! Hope you are well.

    I want to use ADO recordsets against a SQL Server database.

    I am trying to connect to a Sql Server database using the connnection string

    "Provider=SQLOLEDB;Data Source=HSSQL001;Initial Catalog=2020_Consumer_13;Trusted_Connection=yes;"

    My code is:
    Code:
    Private Sub LoadDataForEdit()
            Dim a26fields(25) As String
            Dim conn As New ADODB.Connection
            Dim strRequestedId As String = "" ' = txtSurveyId.Text
    
    
            If txtSurveyId.Text <> "" Then
                strRequestedId = txtSurveyId.Text
    
                
                conn.ConnectionString = "Provider=SQLOLEDB;Data Source=HSSQL001;Initial Catalog=2020_Consumer_13;Trusted_Connection=yes;"         
    
                conn.Open()
    
               
                Call ChildClearForm()
                Dim ssql As String
                Dim objRecordset As New ADODB.Recordset
    
                ssql = "Select * FROM [2020Survey] WHERE Id = '" & strRequestedId & "'"
    
                objRecordset.Open(ssql, sconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
    
                If objRecordset.RecordCount = 0 Then
                    MessageBox.Show("Survey Id: " & strRequestedId & " does not exist in the database!", "CONSUMER SURVEY", MessageBoxButtons.OK)
    
                Else
    
                    With objRecordset
                        Process Recordset
    		End With
    	    End
           
            End If
    End Sub
    I get an error: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

    If someone could please assist me.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,505

    Re: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    I Googled the error and there are several things worth trying in this article along with a brief description of what goes on under the covers.

    https://www.sqlnethub.com/blog/dbnet...error-resolve/
    Please remember next time...elections matter!

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,077

    Re: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    Is there a reason for using SQLOLEDB and ADO and not ADO.Net with a proper provider?

    -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??? *

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,775

    Re: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    I see that you have been using VB.NET for several years so I also have to question why you aren't using it "properly". If you're upgrading a VB6 application that already uses ADO then that's one thing. If you're writing new VB.NET code from scratch though, there's no excuse for not using ADO.NET. You may have a reason, e.g. you know ADO better than ADO.NET, but it's most likely not a good reason. If this is work and you're supervisor is telling you to use ADO then I guess you have no choice but it's still a very bad choice.

  5. #5
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    5,723

    Re: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    All of this would be a lot easier and cleaner if you just used ADO.Net instead of ADO as jmc suggested.

    Also, I noticed that the Integrated Security variable is missing from your connection string. I am of course assuming that the instance of SQL Server that you're trying to connect to is using Windows authentication.
    Last edited by Niya; Oct 26th, 2020 at 12:55 PM.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena


    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. -jmcilhinney

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,775

    Re: Issues with connecting to SQL Server from VB.Net using SQLOLEDB connection string

    Quote Originally Posted by Niya View Post
    Also, I noticed that the Integrated Security variable is missing from your connection string.
    Trusted_Connection is a synonym for Integrated Security. In theory, they should do the same thing.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width