Establish a connection to SQL Server with Visual Studio 2015 Windows Form App

Question

I am trying to create a Window's Form Application connected to a SQL Server database. I am able to connect to the database using Alteryx, Tableau, Python and using the connection wizard in Visual Studio. However, when I try and create a connection within the application it fails to connect. I have attempted to do this in multiple ways

1. VB
2. C# using SqlClient
3. C# using config file (with details from tested connection)
4. C# using ODBC driver

/*************** ODBC Driver ***************/

using System;
using System.Windows.Forms;
using System.Data.Odbc;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string connetionString = null;
            OdbcConnection cnn;

            connetionString = "Driver={SQL Server Native Client 11.0};
            Server=********;Database=********;Uid=********;Pwd=********;"; 

            cnn = new OdbcConnection(connetionString);

            try
            {

                cnn.Open();
                MessageBox.Show("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Can not open connection ! ");
            }

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}

However, when I run this code I get the following error

Activated Event Time Duration Thread Exception: Exception thrown: 'System.Data.Odbc.OdbcException' in System.Data.dll ("ERROR [08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: A non-recoverable error occurred during a database lookup.

ERROR [HYT00] [Microsoft][SQL Server Native Client 11.0]Login timeout expired ERROR [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online."). Exception thrown: 'System.Data.Odbc.OdbcException' in System.Data.dll ("ERROR [08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: A non-recoverable error occurred during a database lookup.

ERROR [HYT00] [Microsoft][SQL Server Native Client 11.0]Login timeout expired ERROR [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.") 16.78s [22724]

For comparison, when I run a similar script in Python I have no trouble connecting to the data

/******** Python using Jupiter Notebook ********/

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                      'Server=********;'
                      'Database=********;'
                      'Uid=********;'
                      'Pwd=********;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM [********].[dbo].[********]')

for row in cursor:
    print(row)

Why can't I connect via a Windows Form Application if I can connect all the other ways. I am assuming that I all the TCP/IP settings should be fine? Plus I have already established a connection via Visual Studio.

Answer 1

I changed the ODBCConnection to SQLConnection. This worked for me, but I am by no means an expert. I do work better in VB.NET so this code was converted to C# using Telerik Code Converter. I have also posted the VB.NET code at the bottom in case something messed up in the conversion.

C# Code

using System;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            TestConnection();
        }

        private void TestConnection()
        {
            string connectionString = null;
            SqlConnection cnn;
            SqlConnectionStringBuilder cnBuild = new SqlConnectionStringBuilder();
            cnBuild.DataSource = "Server";
            cnBuild.InitialCatalog = "databaseName";
            cnBuild.UserID = "userid";
            cnBuild.Password = "password";
            // connectionString = "Driver={SQL Server Native Client 11.0};Server=********;Database=********;Uid=********;Pwd=********;"
            cnn = new SqlConnection(cnBuild.ConnectionString);

            try
            {
                cnn.Open();
                MessageBox.Show("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception __unusedException1__)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }
    }
}

VB.Net Code

Imports System
Imports System.Windows.Forms
Imports System.Data.SqlClient

Namespace WindowsFormsApplication2
    Partial Public Class Form1
        Inherits Form

        Public Sub New()


            TestConnection()
        End Sub

        Private Sub TestConnection()

            Dim connectionString As String = Nothing
            Dim cnn As SqlConnection
            Dim cnBuild As New SqlConnectionStringBuilder
            cnBuild.DataSource = "Server"
            cnBuild.InitialCatalog = "databaseName"
            cnBuild.UserID = "userid"
            cnBuild.Password = "password"
            cnn = New SqlConnection(cnBuild.ConnectionString)

            Try
                cnn.Open()
                MessageBox.Show("Connection Open ! ")
                cnn.Close()
            Catch __unusedException1__ As Exception
                MessageBox.Show("Can not open connection ! ")
            End Try
        End Sub

        Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
        End Sub
    End Class
End Namespace