SQL Filter to perform left, right, full outer or any combination of

SQL is very good at performing joins on data and there are endless ways to optimise the performance when it comes to joining tables and filetring the data when an item is one thing or the other. But how is it possible to take all of the records from one table and some from another?

I was given such a task, to return data from one table that existed in the second table, which was a simple left join. But then there was an additional requirement to return all data from table one that didn’t exist in table to (and vice versa).

Drawing this was a case of a Venn Diagram. The first circle being table one, the second circle being table two. Returning any combination of one, two or three of the parts.

<Image 1: Simple Venn diagram>

Returning a Random SQL Record

There may be occasions during testing that you want to test against a random record, the following statement will return a random record form the Cars table.

SELECT TOP 1 intID, strName FROM [dbo].[tblCars] ORDER BY NewID()

This would return the following:

intID     strName
----------------------------------
17     VW Golf

Change all SQL databases recovery model (Full, Simple or Bulk-Logged)

If you ever have the need to change every databases recovery model, it can take a while to do this manually for each one. I had this need, basically to reduce the space required for backups as the individual transactions weren’t required to be ever rolled-back, only that that the databases were backed up regularly.

Set Crystal Reports Datasource at Run-Time

If you have a database, you more than likely want to query it and present the data. Right? Well,the choice of many is to go for Crystal Reports. This is great for doing the simple things with little hastle at all and also doing the really complicated stuff without too much referring to a help manual either.

But what happens when you find that you have created dozens of them in your development environment and now need to move this to production? I had this trouble and started on the manual process of using the “Set Datasource Location…”, but why do the same action multiple times, because remember that you will have to do the same in reverse when you want to go back to developing. So I thought lets do this at run-time.

Convert C# to VB.Net and vice versa

Ever wanted to convert some code from C# to Vb.net, I know I do on a daily basis. Why is it that the world writes the code I am looking for in C# (I know the answer to that, but play along with me for a second). I know the web is cluttered with these sites, but here’s my suggestion.

http://www.developerfusion.com/tools/convert/csharp-to-vb

Handle access denied in ASP.Net using Global.asax

Due to the way ASP.net processes requests to display a page the user is denied access to, this needs to be entered in your Global.asax file.

In the example below, we get the failed status code and if this is equal to 401, then we clear the content and then forward the user to the Access Denied page.

Private Sub Application_EndRequest(ByVal sender As Object, ByVal e As EventArgs) 
	'if login failed then display user friendly error page
	If Response.StatusCode = 401Then
		Response.ClearContent()
		Server.Transfer("~/AccessDenied.aspx")
	End If
End Sub

This way, the user doesn't get a standard (not user friendly) debug message.

VB.Net function to save data

The following function allows saving data and handles any errors gracefully.

Good practice to save data – Windows Forms

The Setup

I’m going to explain about data entry in VB.Net Windows form, retrieving data, manipulating it, validating it and saving it back to your database.