Inserting Data - SQL Error 40 (VS2005 Express / SQL Express 2005)

Hello,
For the past few days, I've been stuck on this particular error and I hope somebody can help me. Whenever I try to connect to my database using code, I get the following 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) "

I did enable TCP/IP settings under the SQL Surface Area Configuration and restart the SQL server. The problem has not disappeared.

When I use the SQLDataSource tool along with the GridView tool from the Design View in VStudio, I am able to display the data from my database without any problems. Yet, when I attempt to connect using code to insert some data into my database, I am running into the above error.

My connection string looks like the following:

<connectionStrings><add name="testString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Customers.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" /></connectionStrings>

And my code for inserting values into the database looks like the following:

SqlConnection con = null;

SqlCommand cmd = null;

try {

con = new SqlConnection("Server=localhost;Database=Customers.mdf;Trusted_Connection=True;");

string sqlCmd = "INSERT INTO Table (CustomerID, Name, City, State, ZipCode) VALUES ('" + nameTxt.Text + "', '" + cityTxt.Text + "', '" + stateTxt.Text + "', '" + zipTxt.Text + "');";

cmd = new SqlCommand(sqlCmd, con);

con.Open();

} finally {

// foo

}

What is puzzling though, is that because I am able to view the data using built-in tools but not open a connection manually, I am guessing the mistake lies in the above bold/blue line of code. But, because I am not familiar with all of this, I am not sure what to do next.

I am running IIS 5.1 on Windows XP Professional.

Can somebody please help me out

Thanks,
Kirupa




Answer this question

Inserting Data - SQL Error 40 (VS2005 Express / SQL Express 2005)

  • mightymoe

    I did some more fiddling, and I changed my SqlConnection string to the following:

    con = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=True;"+ @"AttachDbFilename=C:\Inetpub\wwwroot\App_Data\Customers.mdf;Initial Catalog=Customers");

    Using the above lines of code gives me the following error:

     

    CREATE DATABASE permission denied in database 'master'.
    Could not attach file 'C:\Inetpub\wwwroot\App_Data\Customers.mdf' as database 'Customers'.

    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.

    I guess that is some progress, but why would I receive a permission denied error. This again goes back to my earlier problem of viewing data from the database without problems, but when I access the database from code, I keep having issues.

    If this is somehow related to my above question, how would I change the permissions Accessing the .aspx page via localhost produced the same error as accessing the same page view a .com domain name.

    =)



  • Inserting Data - SQL Error 40 (VS2005 Express / SQL Express 2005)