Cannot connect to website SQL Server database in code behind file..

Question

Hello,

I'm having trouble connecting to my server's (GoDaddy) SQL Server database in my C# code. I'm trying to decide if it's 1) A C# syntax problem with my SQL string Connection or 2) A SQLServer access issue with security.

I used SQL Express on my local machine which obviously has a lot of Security already built-in like Logins and Server Roles. On my GoDaddy database I only created the tables that I needed to display on my web page. When my data was displaying and updating correctly using SQLDataSources I assumed that everything was setup as far as rights and users.

I opened up the GoDaddy database manager and saw following tables (which are empty): aspnet_Applications. aspnet_Membership, aspnet_Paths, aspnet_PersonalizationAllUsers, aspnet_PersonalizationPerUser, aspnet_Profile, aspnet_Roles, aspnet_SchemaVersions, aspnet_Users, aspnet_UsersInRoles, aspnet_WebEvent_Events.

In my aspx file, I am using a SQLDataSource to select, update, insert and delete data from my grid - and that works great on my local pc and on the server. I had to modify the connectionstring in my web.config file (below) to get it to work on the server.

In the code behind file, I want to fill labels with data using SqlConnection, SqlCommand and SqlDataReader - which works find locally but not on the server.
I can't seem to get the syntax correct in my strConn literal. I've tried it several different ways without success.

Can anyone help?

C# code:

// local
String strConn = "Server = .\\SqlExpress;Database = Test;Integrated Security = True;";
//server
String strConn = "Data Source=jbtest.db.4982274.hostedresource.com; Initial Catalog=jbtest; User ID=userid; Password=password;"
SqlConnection myConnection = new SqlConnection(strConn);
myConnection.Open();
SqlCommand selectCommand = new SqlCommand(selectWLQuery, myConnection);   
SqlDataReader myReader = selectCommand.ExecuteReader();

web.config code:

<connectionStrings>
<!-- Local  -->
<add name="TestConnectionString" 
connectionString="Data Source=BUSHKILL-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" providerName="System.Data.SqlClient" />

<!-- GoDaddy  -->
<!--<add name="TestConnectionString" connectionString="Server=jbtest.db.4982274.hostedresource.com; Database=jbtest; User  ID=userid; Password=password; Trusted_Connection=False" providerName="System.Data.SqlClient" />-->
</connectionStrings>

Answer 1

0

Can you post the error message, so that we can have a bit more insight?

One thing I will say straight away though, is that SqlConnection, SqlCommand and SqlDataReader all implement IDisposable - which means you should really structure the code like this:

using (SqlConnection myConnection = new SqlConnection(strConn))
{
  myConnection.Open();
  using (SqlCommand selectCommand = new SqlCommand(selectWLQuery, myConnection))
  {
    using (SqlDataReader myReader = selectCommand.ExecuteReader())
    {
      ...
    }
  }
}

The using clause ensures that the Dispose member is called on IDisposable objects, meaning that unmanaged resources are always released cleanly, no matter how your code exits the scope in which the connection is used.

answered Nov 02 '09 at 09:30 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Do make sure you read this article though Matt, http://msdn.microsoft.com/en-us/library/aa355056.aspx - there are scenarios where it can all go a bit haywire.

Nov 02 '09 at 11:24 AM Melvyn Harbour 1 ♦♦

Yeah certainly :) Exception handling is always a fine art, but I wouldn't avoid using personally, it just makes the scope of operation very clear, which I think is a definite plus...

Nov 02 '09 at 12:19 PM Matt Whitfield ♦♦

Sorry, the error I received was: Login failed for user 'jbtest'. 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: Login failed for user 'jbtest'.

Nov 02 '09 at 11:05 PM Joe

Ok, that's pretty explicit then - you're connecting to the server OK, but it just doesn't like the credentials you're giving it. Double check that the user ID and password you're putting in the connection string match up with what you use to connect in SSMS.

Nov 03 '09 at 06:51 AM Matt Whitfield ♦♦

Ok, thanks for making me look more closely. It was the password - being case sensitive in the .cs file. For whatever reason, it isn't case sensitive in the web.config file. Thanks.

Nov 03 '09 at 12:22 PM Joe

(comments are locked)

Cannot connect to website SQL Server database in code behind file..

Follow this question

Related Questions