0

I have what I need to work except I want to make it more user-friendly. The states that get dynamically pulled into my dropdown list show up fine, but I would like the states to be fully spelled out. For example. instead of 'AL', I want Alabama. The database I pulling from only has state abbreviations. I'm not sure how to alter the state data before binding to my dropdown list. Any help will be appreciated. Thanks!

Here's my code:

       using (SqlConnection con = new SqlConnection(CS))
        {
           
            SqlCommand cmd = new SqlCommand("SELECT * FROM[MYTABLE] WHERE([UPN] LIKE '%' + @UPN + '%')", con);
                cmd.Parameters.AddWithValue("UPN", UPNCode);
                con.Open();

                DropDownList2.DataSource = cmd.ExecuteReader();
                DropDownList2.DataTextField = "State";
                DropDownList2.DataValueField = "State";

                DropDownList2.DataBind();
                DropDownList2.Items.Insert(0, new ListItem("Select your state", ""));
            
        }
sullivansg
  • 19
  • 7
  • Have you considered creating a table in your database to maintain the state abbreviations and full names? Then you can join to that table to obtain the full name to use in the dropdown list. Also, you should avoid AddWithValue, please see [AddWithValue is evil](https://www.dbdelta.com/addwithvalue-is-evil/) for more detail. – mason Jun 23 '21 at 20:52
  • I don't have access to the database I'm working on unfortunately. I could see if they can add another column, but very unlikely. – sullivansg Jun 23 '21 at 20:55
  • 1
    It really shouldn't take long to add something like that. Anyways if they can't do that, you could create a Dictionary in your code, and use the abbreviation as the key and the full name as the value, and use that for the dropdown list. – mason Jun 23 '21 at 20:56
  • It doesn't pull in all the states -- it depends on the parameter in the query string. So, depending on what is in the query string, different state combos can be dynamically in the dropdown menu from one state to multiple. Would Dictionary work for this situation? – sullivansg Jun 23 '21 at 21:29
  • Why wouldn't it? All you need is some form of lookup to grab the full name from the abbreviation. – mason Jun 23 '21 at 22:44
  • 1
    The right place to store state names is a resource file, since that will support [localization](https://stackoverflow.com/questions/1142802/how-to-use-localization-in-c-sharp) and avoids the DB overhead. In general you should not be hardcoding user-facing text in your c# code. – John Wu Jun 23 '21 at 22:48
  • Thanks Mason and John Wu for your help. I'll have to look into these, but found a quick and dirty solution. I'll have to revisit this again. Thanks! – sullivansg Jun 23 '21 at 23:10

1 Answers1

0

I figured it out. Not the most eloquent way, but works.

            SqlCommand cmd = new SqlCommand("SELECT * FROM[MYTABLE] WHERE([UPN] LIKE '%' + @UPN + '%')", con);
                cmd.Parameters.AddWithValue("UPN", UPNCode);
                con.Open();
                DropDownList2.DataSource = cmd.ExecuteReader();
                DropDownList2.DataTextField = "State";
                DropDownList2.DataValueField = "State";

                DropDownList2.DataBind();


                if (DropDownList2.Items.FindByText("AL") != null)
                {
                    DropDownList2.Items.FindByText("AL").Text = "Alabama";
                }

                if (DropDownList2.Items.FindByText("AK") != null)
                {
                    DropDownList2.Items.FindByText("AK").Text = "Alaska";
                }

                if (DropDownList2.Items.FindByText("AZ") != null)
                {
                    DropDownList2.Items.FindByText("AZ").Text = "Arizona";
                }

I'm just showing the first 3 states, but you would do this for all 50 states.

sullivansg
  • 19
  • 7
  • Sure that's not an elegant way. You've heard of loops right? You can add your state mapping to a collection, then loop over that collection to do this replacement. When you see patterns of repeated code like this, it usually means it can be simplified. – mason Jun 24 '21 at 13:41
  • Can you give an example? I never heard of loops. – sullivansg Jun 24 '21 at 18:01
  • for, foreach, while - those are all types of loops. Do those sound familiar? If not, they're basic things in C# for executing blocks of code multiple times. You can [read the documentation](https://learn.microsoft.com/en-us/dotnet/csharp/tour-of-csharp/tutorials/branches-and-loops-local), but if they don't sound familiar you might want to take the time to find some additional tutorials on these concepts, as they're crucial to writing quality code in C#. – mason Jun 24 '21 at 18:20