50 States in a control that will work in an ASCX file

Just in case you ever happen to need this (hey, some times the situation arises). Nothing else really to this post. Have a good day!

<div>
 <label>State</label>
 <%= Html.DropDownList(Model + "Region", new SelectList(new[] {
 new { Value = "AL", Text = "Alabama" },
 new { Value = "AK", Text = "Alaska" },
 new { Value = "AZ", Text = "Arizona" },
 new { Value = "AR", Text = "Arkansas" },
 new { Value = "CA", Text = "California" },
 new { Value = "CO", Text = "Colorado" },
 new { Value = "CT", Text = "Connecticut" },
 new { Value = "DE", Text = "Delaware" },
 new { Value = "FL", Text = "Florida" },
 new { Value = "GA", Text = "Georgia" },
 new { Value = "HI", Text = "Hawaii" },
 new { Value = "ID", Text = "Idaho" },
 new { Value = "IL", Text = "Illinois" },
 new { Value = "IN", Text = "Indiana" },
 new { Value = "IA", Text = "Iowa" },
 new { Value = "KS", Text = "Kansas" },
 new { Value = "KY", Text = "Kentucky" },
 new { Value = "LA", Text = "Louisiana" },
 new { Value = "ME", Text = "Maine" },
 new { Value = "MD", Text = "Maryland" },
 new { Value = "MA", Text = "Massachusetts" },
 new { Value = "MI", Text = "Michigan" },
 new { Value = "MN", Text = "Minnesota" },
 new { Value = "MS", Text = "Mississippi" },
 new { Value = "MO", Text = "Missouri" },
 new { Value = "MT", Text = "Montana" },
 new { Value = "NE", Text = "Nebraska" },
 new { Value = "NV", Text = "Nevada" },
 new { Value = "NH", Text = "New Hampshire" },
 new { Value = "NJ", Text = "New Jersey" },
 new { Value = "NM", Text = "New Mexico" },
 new { Value = "NY", Text = "New York" },
 new { Value = "NC", Text = "North Carolina" },
 new { Value = "ND", Text = "North Dakota" },
 new { Value = "OH", Text = "Ohio" },
 new { Value = "OK", Text = "Oklahoma" },
 new { Value = "OR", Text = "Oregon" },
 new { Value = "PA", Text = "Pennsylvania" },
 new { Value = "RI", Text = "Rhode Island" },
 new { Value = "SC", Text = "South Carolina" },
 new { Value = "SD", Text = "South Dakota" },
 new { Value = "TN", Text = "Tennessee" },
 new { Value = "TX", Text = "Texas" },
 new { Value = "UT", Text = "Utah" },
 new { Value = "VT", Text = "Vermont" },
 new { Value = "VA", Text = "Virginia" },
 new { Value = "WA", Text = "Washington" },
 new { Value = "WV", Text = "West Virginia" },
 new { Value = "WI", Text = "Wisconsin" },
 new { Value = "WY", Text = "Wyoming" },
 new { Value = "DC", Text = "District of Columbia" },
 }, "Value", "Text", Model), new { @class = "stateList"})%>
</div>

How to use Stored Procedures

I’ve met quite a few folks who knew what stored procedures were but never actually had to write code to call one themselves. So this is just a quick run-down of what you need to do to call a stored procedure from your database. You’ll see it’s not so bad once you get into it.

What we are going to do is create a simple button that when clicked will call a stored procedure and return results based of a parameter that we supplied through a textbox.

We have some basic elements on an aspx page like this:

<asp:TextBox ID="testData" runat="server"></asp:TextBox>

<asp:Button runat="server" ID="Submit" Text="Submit" 
    OnClick="CallStoredProcedure" />

In your C# file you need to remember to add these references at the top:

using System.Data;
using System.Data.SqlClient;

That way we have access to the SQL tools we need to do this.

Let me just throw out the entirety of the code we will be using and then we will go over each element.

public void CallStoredProcedure(Object sender, EventArgs e)
 {
 // Connection String to Test Database
 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();
 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

 // Add user variables to Stored Procedure Execution
 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

// Execute the Stored Procedure
 SqlDataReader rdr = myProcedureTest.ExecuteReader();
 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
 }
}
 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

So the first bit we are looking at is where we define our connection string to our database. These credentials will have to be provided to you if you are not in charge of your databases. Instead of localhost you might receive an IP address or a number of different ways to reach your database, followed by the credentials.

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();

The next section is where we create the SQL connection using the connection string we just created. It uses the credentials and information you provided to actually establish a connection to said database. The Open command establishes a connection to the database.

 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

Here we are defining the SQL query we want to run and then tell it which database connection we want to use (in case we have more than one). We then clarify that what we are using as a SQL command is actually a stored procedure on the server.

 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

In this example, we are assuming that our Stored Procedure takes a single parameter which we pass in from our text box. In a real application, we would want to be more careful with blindly accepting user input but since this is just an example, we will just roll with it.

 SqlDataReader rdr = myProcedureTest.ExecuteReader();

This creates an object to actually “listen” for data being returned from the stored procedure after we execute everything.

 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
    myConnection.Close();
 }

Finally we do something with the returned data. This bit of code essentially says “while there is data to work with, do whatever code we put here and then close the connection to the database.” We are assuming that the stored procedure is only returning a single piece of information (or that we only care about the first piece) based off the “rdr[0]” code. You are responsible for knowing what to do with the returned data and use it how you see fit.

Obviously this tutorial wasn’t exhaustive on the topic of stored procedures by any stretch of the imagination but if you need to begin working with them, this should at least help you get on the ground running.

If you have any questions or comments, feel free to leave them below!

The compiler failed with error code 1073741502

This is a fun error you may never see while working on a Windows Server. I came across the other day when I arrived at work and unfortunately I still can’t pinpoint what caused it to happen but I can talk about what I researched and what we ended up having to do to fix it.

We had a couple sites on our development server (thank goodness it wasn’t live) that started producing this error. The obvious first steps were to do things like recycle the application pool, restart the site, and even restart IIS. Some reports from people online said that this fixed their problem.

Alas I was not so lucky. The next step was to venture into┬áC:\Windows\Microsoft.NET\Framework\ and into the appropriate framework and then into the “Temporary ASP.NET Files” and clear this folder out. In this case the sites were all running on .NET Framework v2.0.50727 but it is safe to clear out this folder anyways. The files that reside in here are just the compiled files being served up to the end-user and if they don’t exist, they are simple recompiled and served up fresh!

At first I just deleted the files being referenced in my specific error but when that didn’t fix, I just cleared the folder. Definitely not something I would do on a production server in case what happened actually happens. After I did clear out the entire temporary folder none of the sited would load and I was getting the 1073741502 error across the board.

Terrible, but it did offer a clue. None of the sites could be recompiled. Some more searching suggested that the Identity in IIS might not have sufficient permissions to run the compiler. A good thought but since this wasn’t a new server and I know it had been compiling that this wasn’t the problem (though I checked anyways for the sake of being thorough).

I then went and checked csc.exe, which happened to be the C# Compiler. Wouldn’t you know it, Windows was reporting that the file was 0 bytes in size. Ultimately we ran the .Net repair tool because it seems that our installation became corrupted. After this finished the sites worked fine, everything recompiled, and all was right in the world again.

Doing follow-ups, some people reported different ideas of what could cause it and the most prevalent one was possible hardware failing. As this is our development server only and not our back-up or production server it doesn’t always have the shiniest of hardware. We ran a back-up of everything just in case and the problem will be further investigated by the powers that be.

So if you ever come across a 1073741502 error:

  1. Restart the website in IIS
  2. Recycle the Application Pool
  3. Restart IIS
  4. Try deleting the files within the Temporary ASP.NET Files folder
  5. Be sure that if you are using a custom identity that it has proper permissions to run things like csc.exe
  6. Check for signs of a corrupted installation (files with no size, etc)

There are probably more effective ways to check this information but solid suggestions about how to handle this error were few and far between so it made for an eventful morning!

Quick Tutorial on using Session Variables

This is a down and dirty lesson on how to use Session Variables in C#. It won’t take long in your programming life that you will want an easy way to pass data from one page to another. Instead of using query strings (which also have their place in the world) or other methods, we can use these variables.

In your code behind file, wherever you want to add to the current session, you can simple add the line:

Session["yourVariableName"] = valueOfYourVariable; 

The “valueOfYourVariable” can pretty much be whatever you want. Strings, integers, variables, and so many other kinds of data.

On the page that is going to receive the data, pulling that information is as simple as using the following code:

string loadedData = (string)Session["yourVariableName"];

Obviously you need to cast your variable on the receiving page as the same data type as you saved it on the previous page, but other than that, it really is that simple to pass data between pages.

Any other complex data types you would just treat as you would normally once you load them.