Category: Web Design


This is information that exists all over the place- the problem is that it is spread around forum comments and the solutions are as plentiful as the questions. I am going to explain how to create connections strings in an ASP.NET web application. The app is hosted on IIS 7.5, Server 2008 R2, and created in Microsoft Visual Web Designer 2010 Express (VWD). For the back-end I use SQL 2008 R2 Standard. VWD uses SQL 2008 Express natively, but I am not going to downgrade for this app.

There is also a little issue of server architecture. I was running into all sorts of problems with IIS and VWD being x64, while the connections drivers were running as x32.

A couple of key points.

  • In order for your application to connect to a database of any sort, it needs a connection string, which is a bit of code inserted into the web.config file in the root of your site.
  • The connections string tells the app: the source of the data, the driver to use, and the data location.
  • Each connection to a database needs to use a specific driver.

The first situation in which you need a connection string is for your app to contain membership information. Most of this stuff is done through wizard-like windows in VWD, but you will often need to change the default settings. This database is created by default when you Configure ASP.NET from within VWD. But I would rather create my own database, and hook up my application to it.

So I first create my database. Open up SQL Server Management Studio. connect to the instance you wish to have your app use. You can create a new instance, but I had an old one not being used, named MyServer\SQLEXPRESS. Do not be confused- this is not actually and Express database, just the name of the instance.

Connect to the instance, use Windows Authentication– this is of course your choice.

Connect to SQL Instance

Connect to SQL Instance

I’m going to glaze over the rest, but here’s what has to happen. You need to create a database, the default settings are alright. You need to grant the account that runs your application pool access to the database. My apppool runs under its own identity, and that identity needs Connect, Read, Select, Update, and Delete rights on the database.

For your ASP.NET security database, it needs to be prepared with a tool for ASP.NET to access it. Make sure you run the right tool, there are many.

C:\Windows\Micrososft.NET has a Framework and a Framework64 folder. Choose the one which is the same architecture as your OS.

So C:\Windows\Micrososft.NET\Framework64\ and then choose the folder that corresponds to the app Framework Version. This can be found many different ways, but if you right-click on the address of your app in solution explorer, and select property pages, you can get an idea.

Framework

Framework

 

So  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ and click on the application named aspnet_regsql.exe. This will run you through a wizard that prepares the database to hold site membership information.

When you use the wizard in your ASP.NET configuration, it will create all the required connections strings and providers for you, I had to tweak mine a bit.

Here is an example of the connection string I used.

<add name=ApplicationServices connectionString=data source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=aspnet; providerName=System.Data.SqlClient/>

 The name is only the name of the connection, this san be whatever you wish. The connectionstring is important. this has more arguments such as username, password, security, etc.

 
Important: data source=.\SQLEXPRESS;
This is the source instance. .\ means the local box. You could also do:
 
localhost\instancename
servername\instancename
remoteservername\instancename
  
Then there is Initial catalog=aspnet
This is the database name that this connection uses. There are other ways to format this, but Initial catalog=databasename without prefix works the best for my app.
With the ASP.NET membership security comes some other entries in your web.config, and these need to point at the connectionstrings NAME, in this case AplicationSecurity.
Here are two more examples of connection strings:
<add name=TheDatabaseSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=TheDatabaseSQL;Integrated Security=True providerName=System.Data.SqlClient;

/> 

 

 <add name=ContactSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=ContactSQL;Integrated Security=True providerName=System.Data.SqlClient/>

<add name=ContactConnectionString connectionString=Data Source=C:\TheDatabase\thedatabase2\App_Data\Contact.mdb;User Id=Admin;Password=; providerName=Microsoft.Jet.OLEDB.4.0;Data/>

You will notice that the data sources on the second one points to a file location, .mdb. this is because it is a Microsoft Access database, which is not hosted, only a file. you also see the provider name is actually the driver that is used for the connection, Jet in this instance.

You can get these connections strings from inside of VWD.

Click Tools>Connect to Database.

Add Data Connection

Add Data Connection

 

The Data Source is the driver you will use for your connection. I use SQL Server and Microsoft Access.
Click the drop down box and select the server\instancename where your database is located.
 
Select your authentication, mine is Windows.
Select or enter database name is used mostly with SQL databases, pick the database you created or have for the link here. With Access,  you can use Attach a Database file.
 
Now Test Connection, if everything is configured and set up, you should get a success message.
Now click Advanced.
 
There are more things to customize here. In the bottom is a box- this is your connectionstring. You can copy this information and paste it into your web.config, then edit the formatting to match the examples above.
Advanced Properties

Advanced Properties

With these connectionstrings in your web.config, you can drag AccessDatasource and SqlDataSource controls from the VWD toolbox onto your application, and then point them at the correct databases.
 

<asp:SqlDataSource ID=”ListDataSource” runat=”server”ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>SelectCommand=”select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Contact'”></asp:SqlDataSource>

 

 

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>

SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>

 

Note: For Access database files, make sure to add them into your App_Data folder in the application, then point to them there.There is another thing to mess around with when talking about application connections. Well drivers and all that- but I won’t go into that here.

I was having one hell of a time getting my application to talk to my databases. that is because when you design in IIS 7.5 x64, and VWD x64, your application is ment for x64 machines. It does not talk well to x32 drivers (in theory it should, with Microsoft’s backwards compatible drivers). So I changed my application to x32. This can be done using the Build>Configuration Manager… menu in VWD. This can also be set on your application pools in IIS. I changed the apppool serving my web application to support x32, and everything is working great with my older databases and .JET drivers. Right click on your application pool, select Advanced Properties, then change the value for enable 32-bit Applications to True.

Enable 32-bit Applications

Enable 32-bit Applications

I am designing a simple HTML site for my intranet. this site will host our policies and procedures, which have been split up into roughly 600 PDF files. The pages are written in HTML with CSS 2 completely from scratch. I use NTFS permissions and URLAuthorization to control who can visit the Windows Authentication website.

The site consists of five static pages, a little CSS trickery, and now, a bit of PHP. I decided that due to the sheer volume of the PDF file’s (which are linked from within the pages, and each file name is a slew of very specific policy numbers) that it would be difficult to pin point a broken link myself.

So I came up with the idea of a broken link (or feedback) page. A very simple page, with two input fields and a textarea field (Name, Email, Comment, respectively). A submit button finishes it off.

The problem with this is that when you hit submit and the mailto: runs, it opens up your default email program and populates a new message. this is ok, but what about people without an email client, or without one configured by default?

So I decide to use a bit of PHP to get my IIS to send my mail for me, in the background.

Here it goes.

First, my setup. I have a SBS 2008 running IIS 7.0, Exchange 2007, Sharepoint, pretty much everything. I then have a very low resourced Windows Server 2008 R2 member. This server does very little, but it does have IIS 7.5 installed, in order to run Lync Server 2010.

First thing we will do is install SMTP for IIS. Open up Server Manager, expand Features.

Click Add Features on the right, then select SMTP Server. Click Install.

Install SMTP

Install SMTP

 

Once that completes, you will have a new tool under Administrative Tools: Internet Information Services 6.0 Manager. Make sure it is 6.0, the other IIS Manager will not let you do this.

IIS 6.0 Manager

IIS 6.0 Manager

You might get a popup about 32 or 64-bit, choose 64 and click ok.

Choose 32 or 64 Bit

Choose 32 or 64 Bit

You will see Internet Information Services in the left window, with a sub-tree of your server name. Expand that node, and you will see VirtualSMTP. Right click that and select rename, and give it a good name- in this case I named mine MemberSMTP (It’s on my domain MEMBER).

Rename SMTP Virtual Server

Rename SMTP Virtual Server

Now right-click the renamed server, and select properties.

Click the Access Tab, then select Connections.

You want to enable the server that is hosting your website (the one that needs to sendmail) to use that server. I used the radio button Only The List Below (less access is good access).

Click Add, and add the loopback or localhost IP address of the server hosting the IIS if it is local. For good measure, I also added the true IP address of the server.

Connections

Connections

Click OK, and select the button Relay…

Set this up exactly as the other list.

Click Ok, Apply, and Ok.

Right click on the VirtualServer and select Start. If it is already started, click Stop, then Start again.

Thats it for the IIS part (roughly), now on to the PHP and the form itself.

Well, before we get into it, we need to have three web pages in our site. the form itself, named feedback.htm. We need a thank you page, named thanks.htm, and an error page, named error.htm. Put these in the root of your website. Here is the HTML from one of my pages.

Note that it will not work for you perfectly lacking my CSS.


<!--pan class="hiddenSpellError" pre=-->DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>">
<html xmlns="<a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>">
<head>
<title>Feedback</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1" />
<meta name="Robots" content="NOINDEX" />
<meta http-equiv="PRAGMA" content="NO-CACHE" />
css"
href="PolicyStyleSheet.css" />

 
    <style type="text/css">
        .style1
        {
            color: #FFFFFF;
}

</style>

    <!--[if IE 6]>
<style type="text/css">
/* some css fixes for IE browsers */
html {overflow-y:hidden;}
body {overflow-y:auto;}
#bg {position:absolute; z-index:-1;}
#content {position:static;}
    .style2
    {
        color: #FF0000;
    }
    .style3
    {
        color: #999999;
    }
</style>
<!--span class="hiddenSpellError" pre=-->endif]-->

</head>
<body>
<div id="bg"><img src="images/gradient.png" width="100%" height="100%" alt=""></div>
<div id="content">
<!-- Header -->
<div id="#hdr">
<table>80px; width: 80%;">
<tbody></tbody>
</table>
        <tr>
       
<td> </td>
<img src="images/small logo no background.jpg"
        style="height: 75px; margin-left: 10px;"  />
        <td align="center"><h1><a name="top">Report a Problem<br />
        or Broken Link</a></h1>
        </td>

        <td>
        <a href="feedback.htm" title="Feedback">Feedback</a>
        </td>
       
        </tr>
        </table></div>
<div>
    <table border="0" cellpadding="0" cellspacing="0"
        style="height: 55px; width: 48%; margin-left: 50px;">


    </table>
</div>

<div id="lh-col1">
</div>

<div id="rh-col"><br />
</div>


</body>
</html>

 You pretty much just need three basic pages, with names you can remember, that have a head and body. you will customize them more later, or take the time to do so now. the error page should say something like “Sorry, you did not fill out the form correctly. Click here to return.” The thanks.htm should say something like “Hey thanks for taking the time! Go back to the site.”

Anyhow, the HTML/CSS lessons will have to come from someone else another time, if you can’t program HTML at this point, stop reading this post.

Now let’s install PHP. All of the romantics and programmers will tell you to manually install it. I don’t have time for all that, so I download the Windows binary here- VC9 x86 Non Thread Safe (2011-Jan-05 21:37:35). Once it downloads, double-click the msi file to start the install.

I selected to install all of the options. you can customize this the way you wish. Make sure to select IIS FastCGI when you get to that point.

IIS FastCGI

IIS FastCGI

Once that completes, we need to make a file in the root of our website. Open up Notapad and save the file as feedback.php. How you get it into your website is up to you. I actually use MS Visual Web Developer 2010, and just copied it into the file directory.

Now we need to make our script, and add some code to feedback.htm and feedback.php. there is a website that will generate it for you- go HERE. Thanks SiteWizard for such a great tool!

Scroll down and select Create a PHP feedback form, and select go to step 2.

PHP Feedback Form

PHP Feedback Form

Fill in your email address, or whoever you want to form results to be mailed to. Enter in the web address of your feedback page, thank you page, and error page. these were the feedback.htm, thanks.htm, and error.htm pages we already created. To get the correct address click on View in Browser from whatever program you are using to author your site, then copy the address for each page.

Page URL's

Page URL's

Skip over all of the optional selections. You can change these if you wish, but you don’t have to. Select to agree to the conditions, and click Generate Script.

The screen has two text areas. the top one is your feedback.PHP contents. Select all of the text, copy it, and paste it into your feedback.php file, using Notepad or your site editor.

Feedback.PHP content

Feedback.PHP content

 

Select the text from the second box and insert this into your feedback.htm page. It should go in the body of the page, in between <div></div> tags.

Feedback.HTM content

Feedback.HTM content

 

Now browse to your feedback.htm. Enter some information, and hit send. The email should arrive quickly.

If you have any problems with the HTML, post a comment and I will help out. If you have problems with IIS 7.5, ask away. If you have problems setting up PHP, view their documentation. If you need to change your PHP.ini settings, I can point you at an entry, but that’s it:


[mail function]
; For Win32 only.
; http://php.net/smtp
SMTP = localhost
; http://php.net/smtp-port
smtp_port = 25

; For Win32 only.
; http://php.net/sendmail-from
;sendmail_from = me@example.com

; For Unix only.  You may supply arguments as well (default: "sendmail -t -i").
; http://php.net/sendmail-path
;sendmail_path =

And of course thanks again to TheSiteWizard.

%d bloggers like this: