Tuesday 18 November 2014

How to set start value of auto increment in MySQL?

The auto_increment is known as the seed value in MSSQL, if you need to change the sequence of your numbers then you can do so by running the following SQL command

ALTER TABLE tablename AUTO_INCREMENT=9999;

Change the table name to your actual table name and the 9999 to be your actual start value.

Alternatively if you are using phpMyAdmin then select your table and from the top menu select operations, under table options edit the AUTO_INCREMENT field.

This worked in phpMyAdmin version 4.2.7.1

Monday 20 October 2014

Office 2010 Activation error 0x80070190

An unspecified error has occurred. Your request cannot be processed at this time. Please try again later. (0x80070190)

I’ve had the above error message a bunch of times while trying to activate Office 2010, I’ve been able to work around the problem by using the telephone activation option provided by Microsoft but it’s a pain and takes about 5 minutes. I honestly can’t believe I’ve put up with problem for so long when the solution to my problem was fairly obvious and straight forward.

If you are dealing with this error try running the activation as Administrator (right click Word or Excel and select Run as Administrator)

Worked first time for me.


Tuesday 14 October 2014

Outlook 2013 New Feature

I’ve started upgrading a few people from Outlook 2007/2010 to 2013 and have come across a new feature that’s really going to help out some of my users.

Some of my users have mammoth mailboxes, one in particular is at 20GB. Outlook in cached mode routinely slows up and the only explanation that we can reach is that Outlook must be doing some routine maintenance on the OST. It became so unusable that we turned off cached mode for that user. The answer is to obviously delete and archive email but some users are stubborn.

We did switch off Outlook caching, but now we find that when the user is going in and out of folders etc that Outlook will hang for a few seconds, its quite frustrating.


Anyway onto Outlook 2013, under account settings there is a now a slider titled “Mail to keep offline” it ranges from 1 month, 3 months, 6 months, 12 months and all. 

Monday 13 October 2014

VB.NET: How to sort listview by clicked column

Ok, I claim no credit for this whatsoever, but just in case Fryan Valdez ever takes down his blog I'll repost the information in his blog post here:

Please click through to read the original blog, I provide a copy of it here purely for my information and future reference

http://www.fryan0911.com/2009/05/vbnet-how-to-sort-listview-by-clicked.html

To make your ListView application capable of column sorting, follow these steps:

1. On your existing project, add a new class with following code:

Public Class clsListviewSorter ' Implements a comparer
    Implements IComparer
    Private m_ColumnNumber As Integer
    Private m_SortOrder As SortOrder
    Public Sub New(ByVal column_number As Integer, ByVal sort_order As SortOrder)
        m_ColumnNumber = column_number
        m_SortOrder = sort_order
    End Sub
    ' Compare the items in the appropriate column
    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
        Dim item_x As ListViewItem = DirectCast(x, ListViewItem)
        Dim item_y As ListViewItem = DirectCast(y, ListViewItem)
        ' Get the sub-item values.
        Dim string_x As String
        If item_x.SubItems.Count <= m_ColumnNumber Then
            string_x = ""
        Else
            string_x = item_x.SubItems(m_ColumnNumber).Text
        End If
        Dim string_y As String
        If item_y.SubItems.Count <= m_ColumnNumber Then
            string_y = ""
        Else
            string_y = item_y.SubItems(m_ColumnNumber).Text
        End If
        ' Compare them.
        If m_SortOrder = SortOrder.Ascending Then
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_x).CompareTo(Val(string_y))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_x).CompareTo(DateTime.Parse(string_y))
            Else
                Return String.Compare(string_x, string_y)
            End If
        Else
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_y).CompareTo(Val(string_x))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_y).CompareTo(DateTime.Parse(string_x))
            Else
                Return String.Compare(string_y, string_x)
            End If
        End If
    End Function
End Class
2. Declare a private variable on the form where the listview you want to be sorted is located.

Private m_SortingColumn As ColumnHeader

3. Then on the listview's ColumnClick event, add the following code

Private Sub ListView1_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick
        ' Get the new sorting column.
        Dim new_sorting_column As ColumnHeader = ListView1.Columns(e.Column)
        ' Figure out the new sorting order.
        Dim sort_order As System.Windows.Forms.SortOrder
        If m_SortingColumn Is Nothing Then
            ' New column. Sort ascending.
            sort_order = SortOrder.Ascending
        Else ' See if this is the same column.
            If new_sorting_column.Equals(m_SortingColumn) Then
                ' Same column. Switch the sort order.
                If m_SortingColumn.Text.StartsWith("> ") Then
                    sort_order = SortOrder.Descending
                Else
                    sort_order = SortOrder.Ascending
                End If
            Else
                ' New column. Sort ascending.
                sort_order = SortOrder.Ascending
            End If
            ' Remove the old sort indicator.
            m_SortingColumn.Text = m_SortingColumn.Text.Substring(2)
        End If
        ' Display the new sort order.
        m_SortingColumn = new_sorting_column
        If sort_order = SortOrder.Ascending Then
            m_SortingColumn.Text = "> " & m_SortingColumn.Text
        Else
            m_SortingColumn.Text = "< " & m_SortingColumn.Text
        End If
        ' Create a comparer.
        ListView1.ListViewItemSorter = New clsListviewSorter(e.Column, sort_order)
        ' Sort.
        ListView1.Sort()
    End Sub





Tuesday 24 June 2014

Working with Regex

If you ever have to work with regular expression then I recommend testing your expressions on this website.

Friday 20 June 2014

PHP Upload Limits on GoDaddy PHP5.ini


I developed a health and safety document management website which has been running without a hitch for around a month now. I had intentionally set file size limits to be 10MB and decided I would increase this limit as and when larger documents appear. Today a user was hitting an error with a 17MB document. I made all the changes necessary to my source code and put the change live, only for the user to report back that they received an error. Straight away I tried a 12MB document and it worked, I asked how big the file the user was trying and they told me 17MB. I did some research on the error message.

Fatal error: Allowed memory size of 67108864 bytes exhausted”

A lot of results for that error message. I managed to find some tutorials on how to resolve the issue on GoDaddy. Unfortunately the information I found is outdated (or at least no longer applicable to hosting package I am on)

The articles suggest creating a php5.ini on the root directory adding the following configuration:

file_uploads = On
post_max_size = 128M
upload_max_filesize = 128M
memory_limit = 128M


However after speaking with GoDaddy support this is no longer correct (at least not for those on the Economy Linux Hosting with cPanel package)


To resolve the issue you must create a .users.ini file. 

Tuesday 3 June 2014

Download Office / Visio 2013 offline installer

The network connection at our office is horrible, it’s a long story with very little light at the end of the tunnel. I have purchased a number of copies of Visio 2013, however software these days doesn’t seem to come with an installation CD any more. After registering the product key with Microsoft I was a little miffed to find that the installer they provide you with is only 900KB in size, clearly it was going to download the rest of the application in the background. Not great when I will have to repeat the install several times over.

This took me a while to find but it is possible to download an offline install for your Microsoft Office products.

Browse to office.microsoft.com
Login to your account
Click Install Office
Click install from a disc
Click I want to burn a disc
Click Download





The download file is 2GB in size, I am not sure if this is JUST for Visio or if it includes other Office products that will be filtered by the product key. 

Friday 16 May 2014

SpaceSniffer - another useful application

 Stop the presses! I can’t believe in all the time that this blog has been going that I haven’t once mentioned or credited an application that I've used countless times.



SpaceSniffer (http://www.uderzo.it/main_products/space_sniffer/) is freeware so what’s not to love? SpaceSniffer provides you with a visual representation of folders / file sizes on your hard drive. Today I ran out of disk space, I ran SpaceSniffer and managed to recover 30GB that was hiding in SQL Express (a database I had restored for testing and a transaction log file). It’s simple and intuitive to use.

Wednesday 16 April 2014

Spam

I have a strange fascination with Spam, its something that really intrigues me. I felt like I just had to post the following bit of spam I received. It didn't ask me to click to verify my bank account details, there was no attachment (with a virus) and it doesn't seem to be trying to sell me anything. It appears to be a combination of random sentences taken from Wikipedia.

It makes so little sense that it actually made me smile.

"After we wrote it, we thought it sounds just like a Blake Shelton song. Also Puerto Cabello was at that time under Dutch control. It is simply a short length of material worn around the thighs rather like a sarong."

Tuesday 15 April 2014

MySQL migration to MSSQL

I use a mix of database servers depending on what application / project I am working on, normally it will be either SQL Server (inc Express) or MySQL.

I find working with SQL statements in SQL Server Management Studio to be much easier than creating statements on the fly, so its useful to have a copy of the MySQL databases on my MSSQL server, previously I have manually created a new database with the tables / structure that I need to create my SQL statements.

That all changed when I came across Intelligent Converters (http://www.convert-in.com/)  They have a great bit of software that will copy databases from just about anything to anything, in this case MySQL to MSSQL (http://www.convert-in.com/sql2mss.htm) The trial version of the software is limited to copying only 5 records per table, that's perfect if you just want the structure of the tables, for $49 you can have the full version of the software and for $99 you can get the MySQL Migration Toolkit which will convert any data source to or from MySQL.

SQL Backup And FTP

Since moving to SQL Server 2005/2008 (including SQL Express at home) I've been using SQL Server Management Studio to create a daily database backup. In my home environment my databases are very small (mostly development projects) so I place these backups into a folder than is configured to synchronize with the cloud. I'm not too worried about full server backups as the database server has minimal configuration done to it. In my business environments I have daily backups that backup the full system (including my SQL backup files). 

Despite using the Management Studio for nearly 9 years now I know very little about it, so perhaps it has the functionality built within it and I just don't process the knowledge (nor did my search results) on how to backup SQL databases to a network location, I also wasn't really in the mood to go fiddling with scripts etc.

I came across a great bit of software called SQL Backup And FTP (http://sqlbackupandftp.com/) 

The most basic (free) version of the software has a lot of functionality and setting up a job is easy.  

Friday 11 April 2014

Add Header logo / image to blogger

Not sure how I missed this when I created this new blog but adding an image to your blog header is a lot easier than I first thought. I searched for how to do it and a number of people suggested adding a image widget but it turns out Blogger has already provided the functionality to do this.

1) Select Layout
2) On the header click Edit
3) Select your header image and options

Thursday 10 April 2014

GoDaddy PHPMailer Relay problems

This has been very frustrating, if you search for GoDaddy and PHPMailer you'll find loads of posts about how PHPMailer needs to be configured in order to work with GoDaddy's hosting.

I tried using some of the configuration information I found online but I wasn’t having any luck. I kept getting “Error: The following From address failed:” despite trying several different addresses (including valid addresses that I had setup on GoDaddy’s cPanel)

I finally came across Aravind is Online blog which nudged me in the right direction (http://aravindisonline.blogspot.co.uk/2012/01/phpmailer-with-godaddy-smtp-email.html)

For completeness.
You cannot use an external host to relay email messages. You must use the following host: relay-hosting.secureserver.net  

I setup a mail user specifically for PHPMailer (I am hosting a subdomain so all my email services are elsewhere)

Use PORT 25 (numerous guides suggest 465)


Set SMTP Auth to true, Aravind’s guide suggests to set this to false, however this didn’t work for me.

Wednesday 9 April 2014

US / UK Date format reverse in Excel

This problem has been bugging me for 3 days now; I am retrieving a date time from SQL. I have verified that the date is in my required format dd-mm-yy, and I have tried a few different ways of selecting the date just to be sure DATEPART(day, fieldname) , DATEPART(month, fieldname), I have even tried dd-MMM-yy.

If I display the date on a form or in a messagebox it displays fine, the moment I put the date value into an Excel cell it flips the day and month around. Originally I was going to work around the issue by prefixing all my dates with a single quote ‘ so that Excel treats them as being strings, however the end user wants to sort on some of the date values and it won’t work quite right as a string.


After banging my head against the wall for 3 days I finally came across the DateValue(date) function! Figured I’d share the solution to this frustrating little problem.

Wednesday 2 April 2014

Excel 2010 .xlsm File hangs when opening

I've been writing some VBA macros to improve an Excel workbooks functionality. I was modifying a Sub Routine and just in case I made a mistake and need to revert back to it I made a copy and appended _backup to its name. As it happens I decided I preferred the original way the sub routine worked so I restored it (deleted the original routine) and then renamed the _backup. Turns out I had accidentally copied the routine twice (VBA didn't warn me of this). Thinking everything was fine I saved and closed my workbook, when I came back to it a few hours later I was unable to open it, Excel 2010 just sat at 100% without allow me into the workbook.

I found a few suggestions online to fix this and none worked, so here's what I did to get mine working.

Change the extension of the file from .xlsm to .xls and then open the file. I got prompted that there was a file type mismatch (or something similar) and it also gave me a complication error, something along the lines of problem with sub routine. As soon as I saw the sub routine error I knew where my coding problem was, corrected it and then saved the file back as .xlsm.

I hope this helps someone out, had me in a panic for a few moments.

Thursday 16 January 2014

Excel cannot complete this task with avaliable resources.

 
 
If You've ever come across the above error when trying to open an Excel workbook ("Excel cannot complete this task with available resources. Choose less data or close other applications") then you can resolve it by increasing its priority in Windows.
 
 
With Excel still running, open task manager and locate the Excel.exe process, right click on it and navigate to priority, normally it should be set to normal, if you increase this to "Above Normal" you should be able to open your problematic workbook. I am not sure of the underlying cause of this and I've only seen this problem when running Excel 2010, it is defiantly not a hardware/resource issue.

Monday 6 January 2014

Outlook 2010 - Finding a misfiled email

Every so often I get a phone about finding lost or misfiled emails. Normally the user has accidentally dragged the email to a folder and misfiled it.

Luckily the search facility in Outlook 2010 is pretty good.

Click on your Inbox and then into the "Search Inbox" field. The toolbar at the top of the screen should now display some search options, the default is to search "Current Folder", change this to "All Subfolders"

99% of the time this will find the email I am looking for. Sometimes the user will want to know what folder the email was misfiled in, simply double click on the email and then click on the File tab. Under the "Move to Folder" option it should display "Current Folder: "