An error occured: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

One simple solution to this error I often face

More often than not, I think everything should be working when this happens. I check to make sure that connections are allowed, I make sure my sql user has proper permissions and is mapped to the correct database and all that good stuff.

But one thing this fails to tell you is when you do not have sql server login enabled (windows only).

Simply go in to SQL Server Management Studio and right click on your instance and choose properties then click on security in the right side and there you will see your Server Authentication select the proper one and apply.

Now remember, many things can cause this error, this won't fix them all. I just thought I would share as this is not something you may think of when reading that error message.

Searching for multiple strings using LINQ

text searching using LINQ

In the past I had covered Record Search with LINQ and that worked well, but did not cover text searching. Since you can't nest if-thens inside of a LINQ query it can be tough to get a useful text search.  Here is a method using a string array to search through a field for matches.

 

First thing is to take a TextBox and Split the Text to put it into a string array, but, it is not that simple. Since Linq can not make dynamic queries, we are going to have to run through an array with a set size. For this example I will choose 5, which will limit our search to only the first 5 terms. You could easily make this more, but this is a demo damn it. Once we decide that, we want to fill the array with String.Empty values as nulls will error out anything.

 

int wordLimit = 5;

string[] keywords = new string[wordLimit];

for (int i = 0; i < wordLimit; i++) keywords[i] = string.Empty;

string[] inputKeywords = txtTitle.Text.Split(new char[] { ' ', ',', '' }, wordLimit + 1, StringSplitOptions.RemoveEmptyEntries);

int max = inputKeywords.Length > wordLimit ? wordLimit : inputKeywords.Length;

for (int i = 0; i < max; i++)

      keywords[i] = inputKeywords[i];

 

This may seem like a lot, but this makes sure we get an array of exacly 5 elements that there are no empties in.  That way, if the user does not fill it in, *every* string contains a String.Empty, so it will work fine.  Next we just go and run through all the strings comparing it in this example to the 'title' property


return from p in someIQueryable where

             p.title.Contains(keywords[0]) &&

             p.title.Contains(keywords[1]) &&

             p.title.Contains(keywords[2]) &&

             p.title.Contains(keywords[3]) &&

             p.title.Contains(keywords[4])

     select p;


Right... now this isn't the most beautiful and elegant solution, but it works! it can be expanded to whatever size you want and will be accurate. It may seem like a lot of lines of code, but without dynamic LINQ query production, this is the only way I found it works stock. Simple and to the point!

Extend an existing or custom class

Writing a extension for an existing class such as string.DoSomethingHere()

I usually run my database logic through Linq and a dbi.cs class for my database interaction. The dbml file will produce all of my custom classes which is great, but, I wanted to add some custom extensions. Looking at that code created by the dbml is overwhelming and I would rather not go poking around in there, so, I decided to make my own extensions, it's not a difficult process.


I had a class named ticket and I wanted to add an extension IEnumerable<ticket>.open() which would list all open tickets. Now, to get in to my calss structure and all of that would be a waste of time, so I will show how to do this with something that is a bit more familiar and likely useful, I am going to extend the string class with string.capitalizeFirstLetter() which would turn:

a senTENCE like this ONE hErE

into one like this

A Sentence Like This One Here

And yes, I know there is the CSS text-transform, but that isn't the point, it is the method I am showing.


First thing is first, make a new class called MyCustomExtensions.cs or something like that. Next, make sure to declare the class as a public static class. Now make a public static string method. normally in a method you would take a string input like this:

methodName(string someString)

For extensions, you do them slightly different like this:

methodName(this string someString)

Notice that this is now ahead of string, that will tell you program to 'look' for this extension whever a single string is used; it will also populate your intellisense in Visual Studio.


That is really the only big difference, now you just treat it like any other method. This is my full example class:

using System.Collections.Generic;
using System.Text;

/// <summary>
/// Custom Class Extensions
/// </summary>

public static class custom_extensions
{
  public static string CapitalizeFirstLetters(this string s)
  {
    string[] splitString = s.Split(new char[] { ' ' });
    List capitalized = new List();
    foreach (string word in splitString)
    {
      string add;
      if (word.Length == 1) add = word.ToUpper();
      else if (word.Length == 0) add = string.Empty;
      else add = word.Substring(0, 1).ToUpper() + word.Substring(1, word.Length - 1);
      capitalized.Add(add);
    }
    StringBuilder sb = new StringBuilder();
    foreach (string word in capitalized) sb.Append(word + " ");
    sb.Remove(sb.Length - 1, 1); return sb.ToString();
  }
}

CSS image map with rollovers :: map of the United States

A fully interactive image map of the united states using pure CSS; great rollover technique

I had actually written this in 2006, but I saw this technique used recently and apparently it's a 'new' technique. You can see this example here. Really the coding is pretty simple, though a little bit tedious.

First thing is to simply make a list of the items you want in the image map. I use dl->dt/dd here instead of a ul->li which would work just as well. Notice my dt element is just 'us', then I start with the states: 'al', 'ak', etc. Also notice that each dd has an 'img' id and each a holds an id and a span with the state name in it.
<dl id="imap">
   <dt><a id="us" href="#nogo" title="us"><span>United States</span></a></dt>
   <dd id="imgal">
     <a id="al" href="#al" title="Alabama"><span>Alabama </span></a>
   </dd>
   <dd id="imgak">
     <a id="ak" href="#ak" title="Alaska"><span>Alaska </span></a>
   </dd>
  ...
</dl>

Next we move on to the css, which is pretty simple to understand, it is just that getting the values is a pain. One of the most imporatant values is the position of the 'box' for each state. You can see if you look further into the css that all of these are relatively positioned in relation to the whole map - it is the upper-left corner of where the 'box' is. I say box because css only works in squares. It takes some planning, but most any shape can be achieved by overlapping squares, and that overlapping is controlled by z-index. Also you will see that the images are positioned negatively against the position so they sit int he right place. And finally, the height and width are just the height and width of the image.
/*ALABAMA*/
#imap #imgal { left:400px; top:220px; z-index:20; }
#imap a#al:hover { background:url(images/states/al.gif) -400px -220px; }
#imap a#al { width:45px; height:70px; }

Repeat this over and over, and you have a full 'image map' made with CSS only. Not the fastest method, but it works and is better than the old image map methods IMO.


Incorrect syntax near 'something' in SQL 2005 while doing a 'CREATE USER' or 'CREATE LOGIN'

Annoying little problem and the simple fix

I was entering the following into my SQL Server Management Express window:
CREATE LOGIN 160thUser
  WITH PASSWORD = 'some_password';
USE name_of_db_to_use;
CREATE USER 160thUser FOR LOGIN 160thUser;

And I kept getting the error:
Incorrect syntax near '160'

Now I often use the same LOGIN and USER, so that was not the problem. What I found it out to be is that SQL 2005 does not allow LOGIN and USER obejcts to start with a number... sooo I just flipped them around to:
CREATE LOGIN User160th
  WITH PASSWORD = 'some_password';
USE name_of_db_to_use;
CREATE USER User160th FOR LOGIN User160th;

And everything worked.

Creating a new limited user for your SQL 2005 instance

Simplified instructions to add a limited access user to your instance

To make a new login/user, run the sql:
CREATE LOGIN loginName
  WITH PASSWORD = 'some_password';
USE name_of_db_to_use;
CREATE USER userName FOR LOGIN loginName;

Often times, loginName is the same as userName.


  • Once that is made, right click on the login you just made is SQL Management Studio under Security->Logins and click properties.
  • Now click on User Mapping on the left and check the box next to what DBs you want the user to have access to.
  • Also, check the box next to what permissions you want the user to have, the following will give user ability to create/read/write/delete/edit tables:
    • db_datareader
    • db_datawriter
    • db_ddladmin

Always remember to give as limited a permissions set as possible!

AJAX Control Toolit CalendarExtender : Where did Saturday go?

How to get back the lost day(s) on your AJAX control

More than once I have come across this, and it isn't obvious where the problem lies.  This happens when your table cells inherit strange padding/margins.  The CalendarExtender is a great tool, but it makes a crazy table that is an html nightmare.  I figure at first I could simply assign the CssClass to something else, but that negates what it is normally set to, which apparently does a lot as there are all sorts of divs, trs, tds, etc in that calendar.  Instead, using FireBug I found the css class it was using and simply override it with this.

 

.ajax__calendar_days table tr td { padding:0; margin:0; }

 

And everything will be looking fine... seeing your Saturdays and everything.

 

Update - 19 October 2008

Turns out this didn't alone fix the problem, it only fixed the days... but, if you click on the month name, it brings up a menu of months, and if you click again, you get a menu of years... those are also broken.  So in order to fix them all, you will need this:

 

.ajax__calendar_days table tr td, .ajax__calendar_months table tr td, .ajax__calendar_years table tr td { padding:0; margin:0; }

 

And now... everything is fixed.

 

Anonymous Contact Web Part and Code

Allow your users to send an anonymous contact/comment through your SharePoint site

Recently I was asked to allow anonymous comments on a SharePoint site. Keep in mind that this site already had anonymous access enabled and I suggested just opening a discussion and recommending that users post on it anonymously... that was shot down. Soooo... I built this simple little web part that allows the administrator to set up the anonymous contact form. It requires that the admin set four things:

  • SMTP Server - umm... you need this to send email
  • Mailto Address - whoever is going to get these anonymous contacts
  • From address - the dummy address it is 'coming from'
  • Subject Line - what you want the subject line to be

Set those and you are all done -- pretty simple really. All of the code is included in the solution zip, or if you just want the web part, download the wsp. To deploy it, use the command:
stsadm -o addsolution -filename folder_you_put_the_wsp_in/anonymous_contact.wsp
Just be sure to deploy the solution via central admin and add it in your web part gallery with the 'new' button.





GUI css writer: change your page style and rewrite your css on the fly

I have seen a lot of theme pickers out there, but not one that gives you 100% control like this

While working on a recent project, I had a complaint about the colors... I hate colors and I am no good at picking them, and when I do find some colors that I think are nice, apparently everyone else thinks they are crap.  This is why I truly respect web designers, as that is a true skill.  But to my chagrin I do not have a web designer, so I did the next best thing: get the blame off my back.  That's right, if you think you are so good with colors, why don't you pick them Mr. User?

 

So I went ahead and used the Farbtastic jQuery color picker Plug-in that I recently fell in love with, along with some nice Linq, XML, some good old .Net IO and a dash of clever css writing, and I was able to put together a (almost) fool-proof theme-designer with a friendly GUI.

 

First thing first, I needed both jQuery and the Farbtastic jQuery color picker Plug-in I just mentioned to be downloaded and called in my <head>.  Once I ran the Farbtastic demo to make sure everything was working fine, I set out on my adventure.

 

Next, I had to seperate my css into two distinct sheets, one: main_style.css would be jsut that, the main style; this never changes and is static.  Then, I made another stylesheet: theme.css that will hold my changing data and be re-written.  By using some clever techniques like not relying on any css border properties, I will be able to minimize the amount of code i have to re-write and be able to produce what looks like borders without actually using borders.  So instead of

/*theme.css*/
div.border { border: solid 5px Black; }

<!--html-->
<div class="border">some stuff with a border</div>

 

I will instead use something like this:

/*main_style.css*/
.border { padding:5px; }

/*theme.css*/
.border_color { background-color:Black; }
.content_color { background:White; }

<!--html-->
<div class="border border_color">
  <div class="inner_color">some stuff with a border</div>
</div>

 

These both produce what looks to be the exact same, the difference being that the second one does not actually have a border, it just looks like it with the padding and background color.  Now I know that the second one is more code, but that is the price you pay to have less code in your css and have your website be maintainable.  Once you write teh css writer one time, you will never have to touch the code again.  You can easily incorporate borders into the re-written css if you would like, that is just not the path I am going.

 

Now that that is understood, I can put in the color picker and the related TextBox controls to correspond to what colors I will be changing.  To do that, simply put this isnot your page where you want the picker: <div id="picker"></div> and then add your TextBox controls where you need them; each TextBox is the class 'colorwell' that allows them to be accessed easier by jQuery.  Once that is done, add a Button to submit everything.  So far, mine looks like the picture at the top of the post.  You will have to bind everything with jQuery now:

[code:js]

 <script type="text/javascript" charset="utf-8">
     $(document).ready(function() {
         var f = $.farbtastic('#picker');
         var p = $('#picker').css('opacity', 0.25);
         var selected;
         $('.colorwell')
      .each(function() { f.linkTo(this); $(this).css('opacity', 0.75); })
      .focus(function() {
          if (selected) {
              $(selected).css('opacity', 0.75).removeClass('colorwell-selected');
          }
          f.linkTo(this);
          p.css('opacity', 1);
          $(selected = this).css('opacity', 1).addClass('colorwell-selected');
      });
     });
 </script>

[/code]

 

Now comes the code-behind.  First thing I decided is that I am going to hold the data in an xml file; you could easily do this in a SQL database as well.  I named the file theme.xml and it looks like this:

<?xml version="1.0" encoding="utf-8"?>
<theme>
  <text>#444444</text>
  <borders>#4a4a4a</borders>
  <body>#ffffff</body>
  <links>#ff7700</links>
  <link_hover>#ffa500</link_hover>
  <button_text>#ffffff</button_text>
  <headers>#ffffff</headers>
  <background>#a53c3c</background>
</theme>

 

I populated it with my default color values of what will be changing.  Next, I made a class that will help me interact with the xml using Linq, I named this file xmlHelper.cs and placed it in my App_Code folder:

public static XElement getElement(XElement x, string element)
{ return (from e in x.Descendants(element) select e).First(); }

public static void writeElement(XElement x, string element, string value)
{
    XElement xe = getElement(x, element);
    xe.Value = value;
}

 

Now in my Page_Load I made a Dictionary<string, TextBox> and populated it with all of my TextBox controls and their corresponding xml Element name.  Now each time the page loads fresh, I simply load my values into the TextBox controls like this:

if (!IsPostBack)
{
    foreach (var v in colors)
        v.Value.Text = xmlHelper.getElement(x, v.Key).Value;
}

 

Now every time the page loads, it pulls the values out of the xml file and populates the TextBox controls.  Now that I have that, I need a way to write to the css file and the xml file.  A iadd that to my btnSubmit_Click event.  Once again, I can look through the Dictionary I had set up to this time use my xmlHelper class and write to the xml file.  That is done like this:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    foreach (var v in colors) xmlHelper.writeElement(x, v.Key, v.Value.Text);
    x.Save(themePath);
    //writeCss(); //this will be used in a moment
}

 

Now the xml is written, we need to write the css.  For this I just use the old IO and StringBuilder to write my css and overwrite the old file:

protected void writeCss()
{
    string cssThemeFile = Server.MapPath("~") + "\\css\\theme.css";
    StringBuilder sb = new StringBuilder();
    sb.Append("html{background-color:" + txtBg.Text + ";}");
    sb.Append(".border_color{ background-color:" + txtBorders.Text + ";}");
    sb.Append(".content_color{ background-color:" + txtBody.Text + ";}");
    sb.Append(".header_text{color:" + txtHeader.Text + ";}");
    sb.Append("a{color:" + txtLink.Text + ";}");
    sb.Append("a:hover{color:" + txtLinkHover.Text + ";}");
    sb.Append(".button:hover{background-color:" + txtLinkHover.Text + ";}");
    sb.Append(".button{background:" + txtLink.Text + ";color:" + txtButtonText.Text + ";}");
    File.Delete(cssThemeFile);
    TextWriter tw = new StreamWriter(cssThemeFile);
    tw.WriteLine(sb.ToString());
    tw.Close();
}

 

That will write my theme.css file, and that is that.  Now on every click, the values will be written to the xml file (this is unneccessary, but it loads the previous settings up for your user).  After that, the css file is re-written and immediately used by your page.  This does require that you have write permissions to whatever directory/directories you house your theme.css and theme.xml files in.  This also is easy to implement with each user profile having their own saved style (easier with SQL).  I hope this comes in handy for someone, I love it and have used it on a few projects now.

 

...And now they see it's not soo easy picking colors that actually look good :)

 

 

Saving multiple files of the same name (C#)

Save multiple files of the same name without overwriting anything

I am sure I am not the only one to run into the problem of saving files with the FileUpload control.  The problem with this can be if a user has already uploaded a file someFile.txt and they are trying to upload another file someFile.txt.  Normally, you have 2 options:

  • Delete the old file and save the new one
  • Inform the user there is already a file there under that name


Recently, one of these options just wouldn't cut it, so I borrowed an idea from Microsoft Windows: if you save two identical files on your desktop, it simply changes the filename slightly.   For example, if there is already someFile.txt on my desktop, and I try to paste a file with the same name, Windows will just rename it Copy of someFile.txt and if I paste again, it will be named Copy (2) of someFile.txt.  Now that is a decent idea, but I hate the 'Copy of' part of the name, so I decided to make that part a little better.

 

What this snippet does, is take in the FileUpload control you are saving from and saves it to the directory you specify (saveTo).  It will first try to save the file as someFile.txt, but if that is already there, it will save as someFile[1].txt and if that is there it will save as someFile[2].txt and so on.  This is very handy if you need to keep mulitple versions of one file.  It return a FileInfo object so you can get the name it saved as, file size, etc..

 

[code:c#]

public static FileInfo saveFileWithoutOverwrite(FileUpload fu, string saveTo)
{
    int count = 1;
    string fileName = fu.FileName;
    string[] fileNameSplit = fileName.Split(new char[] { '.' });
    string ext = "." + fileNameSplit[fileNameSplit.Count() - 1];
    string prefix = fileName.Substring(0, fileName.Length - ext.Length);
    while (File.Exists(saveTo+ fileName))
    {
        fileName = prefix + "[" + count.ToString() + "]" + ext;
        count++;
    }
    fu.SaveAs(saveTo + fileName);
    return new FileInfo(saveTo + fileName);
}

[/code]

 

This has proved to be very useful in developing a Trouble Ticket System, where files are often updated and saved, but we do not want to lose the older records. This could be easily modified to take a Stream input and save it as well if you are not working directly with a FileUpload control.