.Net ramblings
# Wednesday, 28 February 2007
SQL Control Flow in stored procedures
coming from the c# world of strict control flow and built-in bail-out for unhandled exceptions, i was caught out by something in SQL 2005 recently.
many of my stored procedures take this form:
    update sometable set whatever = @whatever
    exec SP_History 'joe bloggs', 'order taken', '€500'
i was assuming that if the update statement failed, the subsequent SP_History SP would not be executed.  however i was wrong, and i now have the following code where i want the SP to stop executing if something goes wrong:
if @@ERROR <> 0 
    RETURN @@ERROR    -- bail out
the exception is still caught by .net, any handling code you may have will be unaffected there by the return statement.

Wednesday, 28 February 2007 15:54:09 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database

# Friday, 23 February 2007
Fix: VS 2005 compile error: The type 'xyz' exists in both X and Y
The full error i got was below:
Error    35    The type 'CrystalDecisions.Shared.ExportFormatType' exists in both 
'c:\Windows\assembly\GAC\CrystalDecisions.Shared\9.1.5000.0__692fbea5521e1304\CrystalDecisions.Shared.dll' and
The reason is because version 9 and 10 of crystal reports are installed on my dev box and VS needed help deciding which one to use.  the fix was to specify the exact assembly binding to use in web.config, as follows:
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<assemblyIdentity name="CrystalDecisions.CrystalReports.Engine" publicKeyToken="692fbea5521e1304" />
<bindingRedirect oldVersion="9.1.5000.0" newVersion="10.2.3600.0"/>
<assemblyIdentity name="CrystalDecisions.CrystalReports.Shared" publicKeyToken="692fbea5521e1304" />
<bindingRedirect oldVersion="9.1.5000.0" newVersion="10.2.3600.0"/>
<assemblyIdentity name="CrystalDecisions.Shared" publicKeyToken="692fbea5521e1304" />
<bindingRedirect oldVersion="9.1.5000.0" newVersion="10.2.3600.0"/>
thanks to rick ersek for the solution.

Friday, 23 February 2007 17:51:46 (GMT Standard Time, UTC+00:00)  #    Comments [2]  .Net General | Asp.Net

# Thursday, 22 February 2007
HowTo: Get Date Part Only from SQL DateTime value
CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS SmallDateTime)

Cheers to this blogger, he explains why it works.

Thursday, 22 February 2007 17:30:08 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database

# Sunday, 18 February 2007
VSS: listing deleted files
my Visual Source Safe database had grown very large and i couldn't see why.  digging around in the aaaaaaamb.a files revealed an episode of robin-hood that had accidentally been checked in to VSS.  the thing was i had deleted it through VS but it hadn't been purged from VSS.  with the VSS graphical interface, you can right-click any folder and it will tell you in the deleted items tab if there are any deleted (but not yet purged) items.  however this is very time-consuming.
thanks to a post on a newsgroup, i discovered the command line interface, which has an option to list deleted files, and you can then purge them. you still have to scan through the output, which is presented in a very crude way to say the least.  if there are deleted files, you would think it should just list them.  but no, it lists every directory and says 'no items found under ...' after it, which makes for a lot of noise when you are trying to scan for directories that actually contain deleted files. 
anway, here's the commands:

set SSDIR=C:\Data\VSS                                        ** the folder containing of your srcsafe.ini file **
cd "C:\Program Files\Microsoft Visual SourceSafe\"
ss dir -R -D $/*.*

Sunday, 18 February 2007 11:42:07 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | General

# Tuesday, 23 January 2007
HowTo: Correct Login Username Case
using Forms auth in an asp.net web site, with a standard Login control, i found a problem today where user 'Joe Bloggs' can log in with 'JOE BLOGGS' as his username.  this messes up my database a little because User.Identity.Name yields 'JOE BLOGGS' and i use this value in the application database. for consistency purposes i only want to use the case-correct version of the username, as it was created.

to fix this, i added the following code to my login
    protected void Login1_LoggedIn(object sender, EventArgs e)
        // correct the case of the username
        foreach(MembershipUser u in Membership.GetAllUsers())
            if(u.UserName.ToLower() == this.Login1.UserName.ToLower())
                // fix the username case
                FormsAuthentication.SetAuthCookie(u.UserName, true);

Tuesday, 23 January 2007 15:05:27 (GMT Standard Time, UTC+00:00)  #    Comments [4]  Asp.Net

# Monday, 15 January 2007
LIKE problems with oledb query
if you're querying an MS access database from within access, you can use ? and # as wildcard single character/digit placeholders.
however, if you're querying via OleDb, then you have to use _
this took me ages to figure out. thanks to this useful entry in msdn2.

Monday, 15 January 2007 12:33:47 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General | Database

# Friday, 05 January 2007
Code 39 error on DVD drive in Vista...
all of a sudden my dvd drive stopped working, and i couldn't figure out why.  there was no drive listed in explorer and the device was not working, code 39.  i eventually found this ms kb article that gets you to delete 2 registry settings to fix the problem.  they said it could be caused by installing Easy CD Creator.  i tried to uninstall AsusDVD and that may have done it.  in any case, it works now.

Friday, 05 January 2007 17:27:30 (GMT Standard Time, UTC+00:00)  #    Comments [1]  General

# Tuesday, 19 December 2006
Global hotkeys with Windows Media Player / Vista
WMP 11 isn't so bad, but the big criticism of WMP all along is that it doesn't support global hot keys like winamp does.
enter "WMP Keys" from sourceforge.  It runs like a champ and does the job, using Ctrl-Alt-Right Arrow for next track, Left for previous track etc.  you need to register the plugin dll, and when i ran it from the command prompt first of all, it didn't succeed.  you need to register it with admin privilege, to do this right click the command prompt in the start menu and run it as administrator. 

Tuesday, 19 December 2006 12:29:37 (GMT Standard Time, UTC+00:00)  #    Comments [4]  General

# Wednesday, 29 November 2006
Problems with the AJAX AutoCompleteExtender (arrgh)
I was interested to see that Atlas/AJAX has now made it to Beta 2.  I looked at it earlier in the year but didn't have time to really get stuck in.  So today i eventually got it working in conjunction with LINQ.  I wanted to get a basic AutoComplete thing working off my database, with which i am hopefully going to use LINQ throughout.

First of all, go to http://ajax.asp.net/ and download and install the 3 bits they recommend. 

Getting the darn thing to work

Brad Adams has a very good getting-started example on this post, i used his article as a starting point, but i had to sort out a few other things first.  i couldn't get any documentation on the AutoCompleteExtender so i scavenged some crucial facts off the net after a lot of fumbling around, i've listed these below.  One mistake i made was to start testing the AutoComplete control before making sure that the web service was returning correct data, my LINQ query was chucking exceptions and the AJAX control doesn't give you any feedback on exceptions, which is understandable and probably desirable.
  • you must mark your web service class with the [ScriptService] attribute, you'll need the Microsoft.Web.Script.Services namespace for this.
    [WebService(Namespace = "http://whatever")]
    public class AjaxService : System.Web.Services.WebService
  • you need two parameters in your web service method, the naming of the parameters is crucial:
    public string[] GetList(string prefixText, int count)
  • if you're using Linq, and if you're new at it like me, you might have trouble getting your LINQ query results into a string array for the web service.  I tried returning IEnumerable<TableName> and List<string> etc. but it didn't like that, i ended up using the ToArray() extended method that is part of System.Query.  Here is the query i used which concatenates some columns from the table into a collection of strings. 
    IEnumerable<string> ds =
                from r in MyDataBase.Restaurants
                where r.RestaurantName.Contains(prefixText)
                select (r.RestaurantName + "," + r.Address1 + "," + r.County); // join the name + address in a string
    string[] results = ds.ToArray<string>();
    return results;

Formatting the AutoCompleteExtender

The control has some nasty hard-coded values, but where there's a will there's a way.  Thanks to the Firefox dom inspector and the CSS !important modifier, all is not lost. 

The control does have a property called CompletionListElementID which specifies the container element where the results are inserted.  I put a simple DIV next to the textbox:
<div id="AutoComplete" runat="server"> </div>
Then in my stylesheet i have the following values to override the hard-coded styles set by the control:
    width: auto !important;   
    overflow: visible !important;
#AutoComplete div
    font-size: x-small !important;   
In case you're wondering, the control renders each result as a DIV inside your specified element.  The control applies the following styles to your specified element:
border: 1px solid buttonshadow; 
overflow: hidden;
visibility: visible;
background-color: window;
color: windowtext;
cursor: default;
width: 130px;
position: absolute;
left: 184px;
top: 27px;
display: inline;

You can override whichever ones you want by applying the !important attribute inside the #AutoComplete entry in the style sheet.  The individual items then can also be styled via the #AutoComplete div entry, i prefered a slightly smaller font size because the default setting looked too big.

Wednesday, 29 November 2006 16:54:25 (GMT Standard Time, UTC+00:00)  #    Comments [4]  Asp.Net

# Tuesday, 10 October 2006
A generic Swap function
if i ever forget how to write this function then... well, let's hope i never do.  i was really surprised not to be able to find it in the SDK.  i'm posting it here for reference anyway.  note: this can only be used in .Net 2.0 which supports generics.
/// <summary>
/// Swap 2 objects
/// </summary>
public static void Swap<T>(ref T first, ref T second)
T tmp = first;
first = second;
second = tmp;

Tuesday, 10 October 2006 16:55:31 (GMT Daylight Time, UTC+01:00)  #    Comments [3]  .Net General