Joe avatar image
Joe asked

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


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;";
String strConn = "Data; Initial Catalog=jbtest; User ID=userid; Password=password;"
SqlConnection myConnection = new SqlConnection(strConn);
SqlCommand selectCommand = new SqlCommand(selectWLQuery, myConnection);   
SqlDataReader myReader = selectCommand.ExecuteReader();

web.config code:

<!-- 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="; Database=jbtest; User  ID=userid; Password=password; Trusted_Connection=False" providerName="System.Data.SqlClient" />-->
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Matt Whitfield avatar image
Matt Whitfield answered

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))
  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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.