Main Points

This is the testing Page for establishing MS SQL Server 2005 Express connection. We're seeing if we can get the configuration correct for accessing something a little more powerful than an Access database. Access should "probably" be OK for the immediate term, but it doesn't give us as much to program against as SQL Server, including stored procedures and extra options for XML. We'd like to be able to access SQL Server .mdf file the same way we've connected to an Access database .mdb file.

Local Development Connection

First we needed to set up a simple test on the page to display information. That will tell us if we're up and running or not. Given the work we did in Access, this should be pretty fool proof - cross your fingers. We added a SqlDataSource to the page with a very basic select statement and a GridView control to throw that information onto the page. Then we configured the SqlDataSource to connect to the database, and here's the connection string it threw into the Web.config file.

<connectionStrings>
<add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\root\App_Data\Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

While this is not brain surgery, the appearance of the table below demonstrates that success is more powerful than beauty at this stage of testing. :D You should see a small table below which indicates the Author ID 1 is attached to Book ID 1 and Author ID 2 is attached to Book ID 2; just like we do on the development box.

While this worked gorgeously on the dev box it bombed on the production server despite all efforts to save it. (see below) Therefore, we've copied the XHTML output for the table directly into the page. So although I can't run the actual gridview, you can still see the "live" results of the dev box testing. In short, I cheated. ;)

[chroniclemaster1, 2009/04/16]

Author ID Book ID
1 1
2 2

This table is the result of this code which looks fundamentally the same as our Access database tests.

<asp:SqlDataSource ID="sdsAuthorBooks" runat="server" ConnectionString="<%$ ConnectionStrings:SqlTestingDb %>" SelectCommand="SELECT * FROM [AuthorsBooks]"></asp:SqlDataSource>
<asp:GridView ID="gvAuthorBooks" runat="server" AutoGenerateColumns="False" DataSourceID="sdsAuthorBooks">
<Columns>
<asp:BoundField DataField="AuthorID" HeaderText="Author ID" SortExpression="AuthorID" />
<asp:BoundField DataField="BookID" HeaderText="Book ID" SortExpression="BookID" />
</Columns>
</asp:GridView>
Skip to Main Points

Production Server Connection

Again we are displaying data to the same small table, but this time on the production server. All the code on the page we know is fine between the dev box and the Access database pages which work fine. All we should need to tweak is the connection string in the Web.config.

  1. <connectionStrings>
    <add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\webroot\App_Data\Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />
    </connectionStrings>

    For better or for worse this diverted us to Error.aspx, one of the first times I've actually gotten to this page - one of the reasons I'm recording all these steps is to create a page on the error pages in the web.config. I also realized that I did not upload the new web.config for the root directory. So even though the error doesn't tell me, this was obviously because .NET couldn't find a connection string with the name SqlTestingDb.

  2. <add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=~/ECBeta/App_Data/Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

    I've adjusted the AttachDbFilename attribute to reflect correctly for the production server and - for kicks - uploaded it. ;) We'll see how that works. I also renamed the web.config in the ECBeta folder which should turn off the custom error pages and let me see the errors from the server. The <customErrors mode="Off"> already, and I turned <compilation debug="true"> from false so I get more details. I received the error "Server Error in '/' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server."

  3. <add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=~/ECBeta/App_Data/Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;Initial Catalog=brinkster" providerName="System.Data.SqlClient" />

    I've added an Initial Catalog as recommended on www.asp.net forums. We'll see how that works. I still received the error "Server Error in '/' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server."

  4. <add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;Initial Catalog=brinkster" providerName="System.Data.SqlClient" />

    I've respecified the AttachDbFilename attribute. I still received the error "Server Error in '/' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server." I then removed the Initial Catalog attribute but there was no change.

  5. <add name="SqlTestingDb" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\mysite\webroot\App_Data\Testing.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

    I've respecified the AttachDbFilename attribute along the lines of our Access database. I still received the error "Server Error in '/' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server." I contacted Brinkster to see if there was anything strange about the account. There is. They don't support SQL Server Express. They have dedicated SQL Server accounts for about x3 what I pay now, but that's it, so I'm throwing in the towel.

I restored the web.config to the ECBeta folder so the Error pages fire again. I then reran the test on the page to check, and it's pulling up the error page just fine - as it did on the first Production server test. I also changed back the <compilation debug="false"> in the root web.config file so that everything is working "normally". I also removed the .NET code from the webpage, otherwise the page is not viewable, so the only reference to the code used in this testing is what you see in XHTML markup for you to read.

Skip to Main Points
← Back to 2. GridView CSS Control
Continue to 4. Data Access Layer (DAL) →
← ← Jump back to ASP.NET Database Development