locked
Cannot get A webservice to talk to SQLExpress RRS feed

  • Question

  • Hi all,

    First, sorry about the length of this post...I just thought I'd put everything out there and hope for a solution.  I do want to mention that I can connect using the SQL Server management Studio express...just not in the actual web environment

    I have searched and cannot seem to find an article on how to get an asp webservice, written in C#, to talk to my sqlserver express database.

    I have enabled TCP/IP and Pipes and also allowed for remote connections...

    I have literally cut and pasted the connection string from an asp server control (an sqldatasource)  For the record, I cannot connect even from a simple web page using code...the only way I've been able to connect is through the server control...

    Let's see..I've uninstalled and reinstalled SQLServer...used a named instance and the default instance....

    I have tried everything I can think of , including turning my XP Firewall off..and still no luck...I get the error listed at the bottom of this post either way.

    Can anyone offer advice or point me to an article that would CLEARLY state how to configue SQL Server, so as to allow my asp.net apps to actually hit it?  I'm seriously thinking of dumping SQLServer express and using mySQL...I don't want too, but the frustration is mounting....

    Here is the code in my webservice as well...if I'm doing something wrong, please point it out....the minute I hit the open method for the connection, I get the following error

    I"ve also noticed that if I use a "\" in the connection string, I get an error, but if I use a "/" everything compiles...why would that be?  It's the same string that the server control mentioned above uses to succesfully hit the database?

    Thank you all in advance.

    using System;
    using System.Web;
    using System.Web.Services;
    using System.Web.Services.Protocols;
    using System.Data;
    using System.Data.SqlClient;

    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    public class Service : System.Web.Services.WebService
    {
        public Service () {

            //Uncomment the following line if using designed components
            //InitializeComponent();
        }

        [WebMethod]
        public string HelloWorld() {
            return "Hello World";
        }

        [WebMethod]
        public DataSet getUser_ID()
        {
            //Create a new Dataset with the name wsDataReturn
            DataSet wsDataset = new DataSet();

                try
               {
                    SqlConnection wsConn = new SqlConnection("Data Source=tgsopteron/sqlexpress;Integrated Security=SSPI;Database=earth;" +
                   "Timeout=10;" +
                   "Application Name=workingDBConnect;" +
                   "AttachDBFilename=moon");
                    wsConn.Open();
                    wsConn.Close();
                   }

                catch (SqlException Exception)
                {
                    wsDataset.Namespace = "Failed ";
                }
       
    return wsDataset;
        }
    }

    Here is the error I get with the firewall turned on or off...also, I've made or at least I think I've made an exception to the firewall for sqlserver...

    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)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Service.getUser_ID() in c:\Documents and Settings\TGS\My Documents\Visual Studio 2005\workingDBConnect\App_Code\Service.cs:line 45
    Friday, December 30, 2005 9:36 PM

Answers