• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

SQL Connection Error

Do you know what can be causing this error?---

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Source Error:


Line 21:         Dim cn As SqlConnection = New SqlConnection
Line 22:         cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ToString
Line 23:         cn.Open()
Line 24:         Dim command As SqlCommand = New SqlCommand(sql, cn)
Line 25:         Dim rs As SqlDataReader = command.ExecuteReader
0
John Account
Asked:
John Account
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
crisco96Commented:
Change the line where you set the connection string to:

 cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ConnectionString
0
 
John AccountAuthor Commented:
What do I change it to? Here's my CommonProcs.vb code:
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class CommonProcs
    Dim cn As SqlConnection = New SqlConnection
    Dim TSelectedUniqueID

    Property getSelectedUniqueID() As Long
        Get
            Return TSelectedUniqueID
        End Get
        Set(ByVal value As Long)
            TSelectedUniqueID = value
        End Set
    End Property
    Function ReadPageData(ByVal PageCode As String, Optional ByVal FieldToRead As String = "PageData") As String
        Dim sData As String = ""
        Dim sql As String = "SELECT * FROM mst_cmsPage WHERE PageCode=" & Encap(PageCode)
        Dim cn As SqlConnection = New SqlConnection
        cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ToString
        cn.Open()
        Dim command As SqlCommand = New SqlCommand(sql, cn)
        Dim rs As SqlDataReader = command.ExecuteReader
        If rs.Read Then
            'sData = rs("PageData") & ""
            sData = rs(FieldToRead) & ""
        End If
        rs.Close()
        cn.Close()
        Return sData
    End Function
    Function Encap(ByVal Expr) As String
        If Expr.ToString.Length = 0 Then
            Return ""
        Else
            Dim s As String
            s = Expr.ToString.Replace("'", "''")
            s = "'" & s & "'"
            Return s
        End If

    End Function
    Function mConn() As SqlConnection
        If cn.State = ConnectionState.Closed Then
            cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ToString
            cn.Open()
        End If
        Return cn
    End Function
    Function ExecCmd(ByVal SqlCmdText As String)
        Dim cmd As New SqlCommand
        cmd.CommandText = SqlCmdText
        Return cmd.ExecuteNonQuery()
    End Function
End Class
0
 
crisco96Commented:
You need to change:
            cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ToString
To
           cn.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("cmsdbConnectionString").ConnectionString
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
John AccountAuthor Commented:
There were two replacements of that code I made as you suggested, but then go this error at cn.Open()

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
0
 
crisco96Commented:
Ok then you need to make sure your connection string is valid (does it include a port if necessary) and can you actually access the database server the connection string is pointing to.
0
 
John AccountAuthor Commented:
This is the strangest thing, okay. Look, here's my connection string, in my web.config file:
 <connectionStrings>
        <add name="cmsdbConnectionString" connectionString="Data Source=server;Initial Catalog=cmsdb;Integrated Security=True;Pooling=False"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
And, of course, I can open the tables in my Server Explorer. Everything's there. So what can be going wrong here?! Sigh.
0
 
crisco96Commented:
Debug the application and set a break point where you set the connection string, after the connection string is set make sure the connection string is what you think it should be.
0
 
badbearontourCommented:
Just as a matter of interest you have enabled remote connections in the surface manager for this instance of SQL2005?

BB
0
 
badbearontourCommented:
use the suraface area configuration tool

Select your server and then selsct network.... in thereI select both named pipes and tcp/ip

BB
0
 
hatem72Commented:
Hi,

all Programs --> microsoft sql server --> sql server configuration manager --> enable pipes & tcp/ip for network and for client connections
0
 
John AccountAuthor Commented:
Okay, guys, the troubled area has been identified. It's right here:
Data Source=.\SQLEXPRESS;AttachDbFilename=C:\MySite\App_Data\cmsdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Where, on a host provider, it might not be C:\MySite\....etc. Perhaps it's F:\, etc. Is there a way to simply indicate localhost\ and the rest of the path?--something like that?
0
 
John AccountAuthor Commented:
Nevermind, guys. This took care of the problem: |DataDirectory|cmsdb.mdf
I used that instead of a full path.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now