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 Source=jbtest.db.4982274.hostedresource.com; 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:

<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

avatar image

11 1 1 3

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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 02, 2009 at 02:23 AM

Seen: 4502 times

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

Copyright 2016 Redgate Software. Privacy Policy