x

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

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>
more ▼

asked Nov 02, 2009 at 02:23 AM in Default

Joe gravatar image

Joe
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Nov 02, 2009 at 09:30 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2009 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, 2009 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, 2009 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, 2009 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, 2009 at 12:22 PM Joe
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x46
x23

asked: Nov 02, 2009 at 02:23 AM

Seen: 3863 times

Last Updated: Nov 02, 2009 at 09:25 AM