Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – SQL Connection Error 0 — if I start my Program from Visual Studio. It works from the build executable if started separately

I am building an Windows Forms App an can not connect to my SQL Server from within Visual Studio. If I build the app and start it separately from the folder, connection is instantly established and communication with the Server works. Then I can read/write/delete sucessfully.
If I start/run the Aplication from within Visual Studio I cannot talk with the SQL Server.
In the Debugger Output I get an Exception:

‘System.Data.SqlClient.SqlException’ in System.Data.SqlClient.dll

After the connection timeout I get an error.

…(provider: TCP, error: 0 – on Database call an not resolvable error
occured) [my translation from german]

The Server listens on a non standard Port (30500), in the meantime I switched to 1433 and back and nothing is working, from within the IDE.

I Use:

Microsoft Visual Studio Community 2022 (x64) with nonAdmin Domain
Account. Version 17.8.5 VisualStudio.17.Release/17.8.5+34511.84
Microsoft .NET Framework Version 4.8.04084 Installierte Version:
Community

Visual C++ 2022 00482-90000-00000-AA825
Microsoft Visual C++
2022
ASP.NET and Web Tools 17.8.358.6298
ASP.NET and Web
Tools
Azure App Service-Tools v3.0.0 17.8.358.6298
Azure
App Service-Tools v3.0.0
C#-Tools
4.8.0-7.23572.1+7b75981cf3bd520b86ec4ed00ec156c8bc48e4eb
Common Azure Tools 1.10
Microsoft JVM Debugger 1.0
NuGet-Paket-Manager 6.8.0
SQL Server Data Tools
17.8.120.1
Microsoft SQL Server Data Tools
SQLite & SQL Server Compact Toolbox 4.8
TypeScript-Tools
17.0.20920.2001
TypeScript-Tools für Microsoft Visual Studio
Visual Basic-Tools
4.8.0-7.23572.1+7b75981cf3bd520b86ec4ed00ec156c8bc48e4eb
Microsoft Visual F# Tools
Visual Studio IntelliCode 2.2
KI-gestützte Entwicklung für Visual Studio.

MyCode is essentialy just creating the connection string, placing it into the connection object and (trying to) open connection.

Public Const strDBSource As String = "Data Source=Computername,30501SQLEXPRESS;Initial Catalog=xxx_DB;Persist Security Info=True;User ID=xxx;Password=xxx;Encrypt=False;TrustServerCertificate=True"

''alternative

Private connectionString_FuEDB As String = New SqlConnectionStringBuilder With {.DataSource = "TTS-NB-LABOR,30501", '"192.168.13.62,30501SQLEXPRESS",
                                                                          .InitialCatalog = "FXXxxx_DB",
                                                                          .UserID = "xxx",
                                                                          .Password = "xxx",
                                                                          .TrustServerCertificate = True}.ConnectionString

[...]

 sqlCon = New SqlClient.SqlConnection
 sqlCon.ConnectionString = strDBSource
 'sqlCon.ConnectionString = connectionString_FuEDB

[...]

Try
   If sqlCon.State <> ConnectionState.Open Then
      Debug.WriteLine("State: not (jet) Open, trying to open..")
      sqlCon.Open()
[...]
End Try`

As the connection works with the (release) build, I assume there isn’t something essential wrong from the code point of view.

I can connect from SQL Mgmt Studio, I can connect via an .udl test connection. I cann connect via the VS integrated SQL Server Explorer. I cann not connect from my App.

I tried various connection-strings at first. They all failed. After some time I tried to connect to an other SQL server where I have the connectionString and also an working method, to try to establishing an connection. This also failed. Then I build the damn thing and started it from explorer and connection succeeded!

I tried NamedPipes, but same error, also I did not invest to much time into it. It may had connectionString issues as well.

I made sure all ports are open on the Serverside. As connection is working from the build, I think it works.

Somewhere on the way I made an new application just for connection-testing. Doesen’t work from the IDE (in Debug mode). From this application I tested a lot of variations of connectionStrings, most of them worked. Some where just plain wrong. Non of them work from within the IDE.

I came accross an MS Article which stated that port 135 TCP should be open for Visual Studio and SQL ManagementStudio, the are open. In fact for testing I opend all Port for thode two aplications.

Any Ideas what else I can try?

Update
I saw I can start the Programm without Debugging. Then it is working.
I edited the debug mode. Enabled and disabled Hot Reload and SQL-Server-Debugging. Same result, not working in Debug mode.

2nd Update
As proposed by @HardCode I post the whole connection Section from my code:

Imports System.ComponentModel
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Imports Microsoft.IdentityModel.Tokens

Public Class Hauptfenster

[..some other variable declarations.]


    Public Const strDBSource As String = "Data Source =Name-OfDevice-LABOR,30xxx;Database=xxx_DB;User ID=SA;Password=sa;"
   'Public Const strDBSource As String = "Data Source=Name-OfDevice-LABOR,30xxx;Initial Catalog=xxx_DB;Persist Security Info=True;User ID=SA;Password=sa;Encrypt=False;TrustServerCertificate=True"
   Private connectionString_xxxDB As String = New SqlConnectionStringBuilder With {.DataSource = "Name-OfDevice-LABOR,30xxx1", '"192.168.local.ip,30xxx",
                                                                             .InitialCatalog = "xxx_DB",
                                                                             .UserID = "SA",
                                                                             .Password = "sa",
                                                                             .TrustServerCertificate = True}.ConnectionString
   Dim sqlCon As SqlConnection
   Dim sqlQuerry As SqlCommand
   Private Sub openConnection()

      If IsNothing(sqlCon) Then
         sqlCon = New SqlClient.SqlConnection
         sqlCon.ConnectionString = strDBSource
         'sqlCon.ConnectionString = connectionString_xxxDB
      End If

      Ausgabe_Log(String.Format("ConnectionString: {0}", sqlCon.ConnectionString))
      Ausgabe_Log(String.Format("State_: {0}", sqlCon.State))

      Try
         If sqlCon.State <> ConnectionState.Open Then
            Debug.WriteLine("State: not (jet) Open, trying to open")
            sqlCon.Open()
            Ausgabe_Log(String.Format("Resultat nach Verbindugnsversuch: {0}", sqlCon.State))
         End If
      Catch sqlexception As SqlClient.SqlException
         MsgBox(sqlexception.Message)
         Ausgabe_Log(String.Format("SQL-Fehler: {0}", sqlexception.Message))
         Debug.WriteLine(sqlexception.Message)
      Catch ex As Exception
         MsgBox(ex.Message)
         Ausgabe_Log(String.Format("Allg. Fehler: {0}", ex.Message))
         Debug.WriteLine(ex.Message)
      End Try

The Exception is happening at :

 sqlCon.Open()

I tried to trace the source of the Error and am qurrently stuck somewhere in "DbConnectionPools.cs" in TryGetConnection(…).
There is an condition check dbConnectionInternal == null. It enters this path. dbConnectionInternal = UserCreateRequest(owningObject, userOptions); faisll.

I am getting an Win32Exception on a Win 10 Pro – x64 System. This is wondering me a litte bit. The SQL Server is running on a x32-System so this is maybe related? I just caught my eye and I just want to mention it.

Update 3 (2024-02-12)
I Implemented the whole communication with EF-Core. I Still can not get any SQL Connection in Debug-Mode.

I recreated the whole Project in vb.net7 from a clean Projectfile. Just copied the, the few files with written code. And installed all packages for .net7 again. Same Result.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Today I relocated the Project to a local Drive. Away from "User-Networkdrive" which is the default Userfolder, as we get it assigned per Domain-Settings.

    I started to get perfomance Problems and was wondering why. Recognized that the Data are not localy stored. After moving Project to local drive I thought why not try Debuging again. It worked.

    I still have ocasionaly some timing issues which result in Exceptions. But the essential Problem of not getting an Connection in Debug-Mode is resolved.


  2. 0

    This is not an answer. This is too many recommendations for comments, and it will be deleted once communicated to the OP.

    You have a lot of "fluff" and bad practices around your use of database connections. You are trying to open a connection and doing nothing with it. You have If statements that will always return true due to the nature of how the SqlConnection object works. For example, you are declaring an SqlConnection object and then immediately are checking if it is open. It will never be open until you open it. That’s unnecessary "fluff" that just distracts from actual issues, like your connection string issue.

    Also, Hungarian Notation is long dead :-).

    Here is how you should structure your code. Notice I’m doing away with an OpenConnection Sub, because you never open a connection to the database until you are about to run a query. I’m also declaring the SqlClient objects with Using, so they are properly and automatically disposed. Use Using with anything that implements IDisposable.

    Public Class Hauptfenster

Public Const _connectionString As String = "Data Source =Name-OfDevice-LABOR,30xxx;Database=xxx_DB;User ID=SA;Password=sa;"

Private Sub ReadMyData()

    ' Add in your logging and error handling around this code structure.
    ' No need to constantly check the connection state. You'll get an exception thrown
    ' if you try to use a connection that isn't open.

    Using conn As New SqlConnection(_connectionString)

        Using cmd As New SqlCommand("SELECT * FROM Foo", conn)

            conn.Open()

            Dim da As New SqlDataAdapter(cmd)
            Dim dt As New DataTable
            da.Fill(dt)

            ' Use DataTable dt to populate the data on your form. Like a DataGridView, for example.
            DataGridView1.DataSource = dt

        End Using

    End Using
End Sub

End Class

    Now, there is a lot less unnecessary code for debugging.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search