How to delete duplicate records in sql database

It took me deleting ALL the records from my database before I figured out how to do this! :( My solution to the problem of duplicate records in the database relies on the fact that there is at least ONE column that has duplicate information. In my case I only had two records where there should have been one. So the code I used to remove one of the two duplicated records is as follows:

DELETE A
FROM [emailsTable] A, [emailTable] B
WHERE A.MessageID = B.MessageID

AND A.GUID > B.GUID

IMAP is the devil

I spent the morning trying to figure out how to use IMAP. I want to write my own little asp.net function that just marks all e-mails in an inbox as read and couldn't believe how difficult it was to find this information. I finally stumbled upon bobpeers.com who's site had many GREAT examples of how to execute IMAP command via telnet. With the help of this example and the IMAP4rev1 paper I was able to accomplish what I needed.

<bobpeers.com /> posting

IMAP4rev1 paper

Mapping XML to SQL Tables

I was recently given an file that had over 14,000 lines of XML and was asked to get that XML data into three separate SQL Tables. After a little bit of googling I found this website - which give lots of great examples of how to map an XML file to a sql table.

http://msdn.microsoft.com/en-us/library/aa225754(SQL.80).aspx

One of the features I'd like to highlight is KeepIdentity. This allows you to insert into a table that has Identity Specification set to Yes.

Bind DropDownList with SqlDataSource and Custom ListItems

I ran into a small problem earlier today and wanted to make a note of it here in case I forget in the future. I have a dropdownlist that contained a list of about 30 locations. I originally hard coded these locations, but found later on that putting them in a sql table would be a good idea. After I did that I set up the dropdownlist to use the table I just created:

<asp:DropDownList ID="ddlUSERFacilityID" runat="server" AutoPostBack="true"  DataSourceID="dsFacilities" DataTextField="FacilityName" DataValueField="FacilityID" >

However I needed to add a "- Select One -" for the users that had either old and invalid facility ids or users who just didn't have one at all and I was stumped. Clicking around a google search landed me this dropdownlist property that solved my problem:

AppendDataBoundItems="true"

So the final control ended up looking like this:

<asp:DropDownList ID="ddlUSERFacilityID" runat="server" onChange="chgColor();" OnSelectedIndexChanged="updateStatus" AutoPostBack="true" DataSourceID="dsFacilities" DataTextField="FacilityName" DataValueField="FacilityID" AppendDataBoundItems="true">

<asp:ListItem Value="SelectOne" Selected="True">- Select One -" </asp:ListItem>

</asp:DropDownList>

Convert HEX color to System.Drawing color

I am working on a data grid at the moment - and one of the requirements of the grid was to have a clickable image as the remove button AND to have the whole row change colors when the image was clicked. In my googling I came across this method:

Protected Sub btnRemove_Click(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs)

e.Item.BackColor = System.Drawing.ColorTranslator.FromHtml("#ADD6FF")

End Sub

I added this to the codebehind for the click event and viola it works perfectly!

Setting TextBox Focus when using UpdatePanels

Try as I might TextBox1.Focus() would not give TextBox1 focus after an AsyncPostBack. Placing a breakpoint on that particular like of code showed that the code was executing, but alas... no blinking cursor. The solution to this problem is to use the ScriptManagers Focus()! ScriptManager1.SetFocus(TextBox1) gave me the results I was looking for.

Ways to fix PageRequestManagerParserErrorException

I created a web app that retrieves data from a binary column in sql server. In order to convert the data in that column I created an HttpHandler, but I had to pass an ID to the HttpHandler to pull the right row from the database. I decided to pass the ID via the session, but once I added the code for the Session variable I started getting this weird error the very first time I would run my app: "Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. comonn causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled."

I did a lot of looking around on the interwebs and found two sites that eventually landed me a solution I was happy with. This first blog suggested adding EnableEventValidation="False" to the top of the .aspx page, which did stop my error, but I wasn't comforatable doing this. I did some searching through the comments of this second blog and found out that all I needed to do was "initialize" my session variable, and that ended up working really well for me.