0

Just trying to do a simple local database connect to Microsoft SQL Server in Visual Studio. Trying to figure out why it's not connecting. I made a user specifically for this and have the correct username and password. I omitted the User Id and Password from this purposely. Seems to be throwing an error at my connection string, what am I doing wrong?

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

My code:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Assetmvc
{
    public partial class SearchPage : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the db
            string connStr = "Server=localhost;Database=AssetTracking;User Id=;Password=; ";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            // Create a command
            SqlCommand cmd = new SqlCommand("SELECT [EmployeeId],[FirstName],[LastName],[HiredDate],[FiredDate],[CurrentItems],[SupervisorId],[SupervisorName] From [dbo].Employees");
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;

            string temp = "";

            // Read from database
            SqlDataReader reader = cmd.ExecuteReader();

            while(reader.Read())
            {
                temp += reader["EmployeeId"].ToString();
                temp += reader["FirstName"].ToString();
                temp += reader["LastName"].ToString();
                temp += reader["HiredDate"].ToString();
                temp += reader["FiredDate"].ToString();
                temp += reader["CurrentItems"].ToString();
                temp += reader["SupervisorId"].ToString();
                temp += reader["SupervisorName"].ToString();
                temp += 

                temp += "<br/>";
            }

            conn.Close();

            lbl_test.Text = temp; 
        }
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LEROY JENKINS
  • 49
  • 8
  • 4
    What's the error? – silkfire Jan 15 '19 at 18:15
  • System.Data.SqlClient.SqlException: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)' Win32Exception – LEROY JENKINS Jan 15 '19 at 18:18
  • are you working with an SQLExpress version? – Mario Mitterbauer Jan 15 '19 at 18:20
  • Edit: actually yeah it looks like I'm using the express version – LEROY JENKINS Jan 15 '19 at 18:21
  • how do you connect to your database? With Windows Authentication? – Mario Mitterbauer Jan 15 '19 at 18:23
  • I was using that method then I made a new login specifically for this and put it under SQL Server Authentication – LEROY JENKINS Jan 15 '19 at 18:27
  • already had a look at https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-could-not-open-a-connec ? – Mario Mitterbauer Jan 15 '19 at 18:27
  • Didn't see that when I was searching for answers but I'll give it a try and let you know how it goes. Thanks – LEROY JENKINS Jan 15 '19 at 18:29
  • Does your SQL Server have an instance name? – critical_error Jan 15 '19 at 19:36
  • Are you using the default port? Have you confirmed remote connections have been allowed? In SSMS Right-Click your server » Properties » Connections » confirm "Allow remote connections to this server" is checked off. – critical_error Jan 15 '19 at 19:41
  • Mario: I tried following that guide and when I tried to restart sql server it all options were greyed out and wouldn't let me even when in admin. Critical error: I believe I'm using the default port but im not 100%. I unchecked "allow remote connections" and still nothing. – LEROY JENKINS Jan 15 '19 at 20:14
  • By the way thanks for all the help. This brick wall has halted my work all day and it's super frustrating. – LEROY JENKINS Jan 15 '19 at 20:15
  • What's frustrating Leroy is you running in before everyone is ready :) – S3S Jan 15 '19 at 20:31
  • I'm about to run my fist right through this computer in a minute lol – LEROY JENKINS Jan 15 '19 at 20:36

2 Answers2

1

If you're using SQL Server Express and used all the defaults when installing, then you have a SQL Server named instance with a name of SQLEXPRESS - and therefore, you need to use this connection string:

string connStr = "Server=localhost\\SQLEXPRESS;Database=AssetTracking;User Id=;Password=; ";

Note that you need to use localhost\SQLEXPRESS to connect to the named instance with an instance name of SQLEXRPESS.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I tried that already and the backslash in "localhost\SQLexpress" throws an error – LEROY JENKINS Jan 15 '19 at 20:29
  • We are getting somewhere. After troubleshooting every single thing in this guide https://success.scribesoft.com/s/article/Named-Pipes-Provider-Error-40 the only error I have now is Login Failed. Thank you so much! – LEROY JENKINS Jan 15 '19 at 21:32
-1

After troubleshooting all day this guide has helped a lot.

https://success.scribesoft.com/s/article/Named-Pipes-Provider-Error-40

Although after doing that I ended up just using the connection string provided in the properties box after clicking my server in Sql server explorer.

I want to thank everyone who didn't downvote and actually helped out I really appreciate it. I'm marking Marc_S as the answer but wanted to add this for any one else who might read this.

LEROY JENKINS
  • 49
  • 8