ASP VBSCRIPT ADO Insert into Access

Two strategies for getting data into Access from old-school ASP / VBScript

ASP VBSCRIPT ADO Insert into Access - With Named Parameters

        Dim objCgtCmd: Set objCgtCmd  = Server.CreateObject("ADODB.Command")
        with objCgtCmd
            .ActiveConnection = Conn
            .CommandText = "INSERT INTO tblCgtDisclosure (ConsId, TaxYear, AdditionalIncome, CgtAllowanceUsed, PartnerName, PartnerTaxCode, PartnerGrossIncome, PartnerCgtAllowanceUsed) VALUES (@ConsId, @TaxYear, @AdditionalIncome, @CgtAllowanceUsed, @PartnerName, @PartnerTaxCode, @PartnerGrossIncome, @PartnerCgtAllowanceUsed)"
            .Parameters.Append .CreateParameter("ConsId", 3, 1, , Session("ConsId"))
            .Parameters.Append .CreateParameter("TaxYear", 3, 1, , Request("TaxYear"))
            .Parameters.Append .CreateParameter("AdditionalIncome", 6, 1, , Request("AdditionalIncome"))
            .Parameters.Append .CreateParameter("CgtAllowanceUsed", 6, 1, , Request("CgtAllowanceUsed"))
            .Parameters.Append .CreateParameter("PartnerName", 200, 1, 255, Request("PartnerName"))
            .Parameters.Append .CreateParameter("PartnerTaxCode", 200, 1, 255, Request("PartnerTaxCode"))
            .Parameters.Append .CreateParameter("PartnerGrossIncome", 6, 1, , Request("PartnerGrossIncome"))
            .Parameters.Append .CreateParameter("PartnerCgtAllowanceUsed", 6, 1, , Request("PartnerCgtAllowanceUsed"))

            .Execute()
        end with
        Set objCgtCmd = Nothing

ASP VBSCRIPT ADO Combined Update / Insert into Access - With RecordSet

Dim rs_CgtDisclosure
Dim rs_CgtDisclosure_SQL
Set rs_CgtDisclosure=Server.CreateObject("ADODB.recordset")
rs_CgtDisclosure_SQL = "SELECT * FROM tblCgtDisclosure WHERE ConsId = " & Session("ConsId") & " AND TaxYear = " & Request("TaxYear")
rs_CgtDisclosure.LockType = 3
rs_CgtDisclosure.CursorType= 2
rs_CgtDisclosure.Open rs_CgtDisclosure_SQL, Conn

If rs_CgtDisclosure.BoF And rs_CgtDisclosure.EoF Then
    rs_CgtDisclosure.AddNew()
    rs_CgtDisclosure("ConsId")   = Session("ConsId")
    rs_CgtDisclosure("TaxYear")   = Request("TaxYear")
end if

dim additionalIncome: additionalIncome = 0: if (IsNumeric(Request("AdditionalIncome"))) then additionalIncome = Request("AdditionalIncome") end if
dim cgtAllowanceUsed: cgtAllowanceUsed = 0: if (IsNumeric(Request("CgtAllowanceUsed"))) then cgtAllowanceUsed = Request("CgtAllowanceUsed") end if
dim partnerGrossIncome: partnerGrossIncome = 0: if (IsNumeric(Request("PartnerGrossIncome"))) then partnerGrossIncome = Request("PartnerGrossIncome") end if
dim partnerCgtAllowanceUsed: partnerCgtAllowanceUsed = 0: if (IsNumeric(Request("PartnerCgtAllowanceUsed"))) then partnerCgtAllowanceUsed = Request("PartnerCgtAllowanceUsed") end if
dim partnerTaxCode: partnerTaxCode = "": if (Not IsNull(Request("PartnerTaxCode"))) then partnerTaxCode = Request("PartnerTaxCode") end if
dim partnerName: partnerName = "": if (Not IsNull(Request("PartnerName"))) then partnerName = Request("PartnerName") end if

rs_CgtDisclosure("AdditionalIncome")   = additionalIncome
rs_CgtDisclosure("CgtAllowanceUsed")   = cgtAllowanceUsed
rs_CgtDisclosure("PartnerName")     = partnerName
rs_CgtDisclosure("PartnerTaxCode")       = partnerTaxCode
rs_CgtDisclosure("PartnerGrossIncome")   = partnerGrossIncome
rs_CgtDisclosure("PartnerCgtAllowanceUsed")  = partnerCgtAllowanceUsed
rs_CgtDisclosure.Update()
ErrorMsg = "Contract details updated!"
    
rs_CgtDisclosure.Close

Linq "Sounds Like" queries on SQL using SoundEx / SoundCode

Using .NET 4 and Entity Framework it's quite easy to add "sounds like" query functionality using SQL's underlying SOUNDEX function.

The key is to use the SqlFunctions object found in System.Data.Objects.SqlClient, e.g.

var query = dataContext.Agencies.AsQueryable();

if (!string.IsNullOrEmpty(searchTerm))
{
   query = query.Where(agency => agency.Name.Contains(searchTerm) || SqlFunctions.SoundCode(agency.Name) == SqlFunctions.SoundCode(searchTerm));
}

When web-form submissions fail

When you submit information from a form on any website, there are several ways in which the submission may fail. 

• Client-script errors (bugs with the web-page code) or functioning client-side validation code may prevent the submission from being sent.
• Network problems at any point between the user and the server may physically stop the submission from being transmitted. 
• Server-side software errors, hardware problems, or functioning server-side validation code may prevent the submission from being processed.
• Temporary application configuration changes, database problems or application restarts may prevent the submission from being processed.

For all of these scenarios, it may still be possible, depending on the software on the web page or server, that the user may experience what seems to be a successful result, either by seeing an explicit success message, or by not seeing an explicit error message.

SQL Server Table / Database Compare Utility

A quick note if you're looking for a good SQL Server DB Diff / Comparison utility. I've used RedGate SQL Compare before which was good but a little slow.

Today I tried DBComparer which is free. Did a decent job but with the task I had, there were many Collation disparities which I could not find a way to filter out. So looking for the "real" differences was like looking for a needle in a haystack.

I tried some free TableDiff.exe based scripts but they generally sucked or fell over due to schema / collation problems.

Finally I used SQLDelta which costs about 200 quid (330 dollars) but has a 14-day trial. I was very impressed. It was fast, allowed me to filter out the collation conflicts, showed all changes between my DBs (table / user / sproc diffs), and automatically built an update script which synced my DBs in no time. Worth buying for the time it will save you.

How to upgrade Samsung Galaxy S3 LTE to Android 4.1.2 (manual update of UK Orange GT-i9305)

I've successfully upgraded my S3 LTE on the UK Orange network (EE / EverythingEverywhere) to Jelly Bean 4.1.2. The release features the funky multi-window mode from the Samsung Note 2, smart screen rotation feature, new Gallery layout, and even smoother Project Butter UI transitions, amongst other tweaks. I'm not 100% sure but I think this release may have improved the power consumption too, which is very welcome; I seem to have around 20% more battery available at the end of the day than I used to. But maybe I'm just playing with the phone less often ;)

Note my phone was already rooted with ClockWorkMod (CWM). See my earlier post for how I did that.

So, for JellyBean 4.1.2, I used the following EE ROM from SamMobile: http://www.sammobile.com/firmware/?page=8&view=10065

And in brief, here's how I did it.

• Backed up using CWM
• TriangleAway to reset custom binary counter
• Put into ROM download mode (VOL DOWN + POWER + HOME)
• USB hookup to my windows machine, ensure Kies is NOT running
• Use Odin3 to select the new Firmware ROM (the PDA section) and START
• Cross fingers!

The first 2 steps may not have been necessary, but they're what I did. Note the phone will no longer be rooted after update, and will be back to stock recovery mode (No CWM installed). Modem is still fine and makes calls, accesses VoiceMail, Internet etc. Also, all my apps, settings, customisations, data and media files remain unscathed. Woo, yay, hoopla.

Root the UK Orange/T-Mobile/EE Samsung Galaxy S3 LTE (GT-I9305)

Well, I'm a total Android newbie, having eschewed the little green robot in favour of my fruity 3GS which I've been rocking for 3 years now. But Apple really p1553d me off when they launched iOS6 with crippled maps, and my toleration levels for iTunes crappiness on Windows are at breaking point, AND the new lightning connector means none of my various expensive docks at home and in the car work any more, without a crappy adapter. So basically Apple have gone out of their way to make me try Android out.

My weapon of choice is the new Galaxy S3 LTE - GT-I9305. This international version sports a quad CPU and 2Gb of internal RAM, and Android 4.1 as we got to press, so I went for it over the old GT-I9300 which only has 1Gb RAM and Android 4.0 at this time. The LTE capability was a bonus, and should help resale value, but I don't care about 4G much as I barely get 3G at the best of times. The slinky butter UI on this phone finally convinced me that in terms of display slickness, android is now a match for iOS.

Of course, one of my motivators for breaking free of the iShackles was to be able to root my new phone, stick a custom ROM on it and so become the ULTIMATE GALAXY COMMANDER, whatever that is. Now, the thing I didn't consider when I got this version of the phone is that it's brand spanky new to the android development community, so most of the existing S3 ROMs won't work on it, and many of the tutorials about rooting it seemed GT-I9300 specific. And being a scaredy-cat, I was afraid at each step along this process that I was about to brick my phone / disable the SIM card / explode the modem and basically send me back to the Orange shop with my tail between my legs and 500 quid's worth of broken hardware in my hand.

So if you're like me in that regard, I can at least report that with the following information I rooted my phone without bricking it, and furthermore, all the tools are available that allowed me to test restoring the phone to its stock configuration in case I ever need to send it back. You should note that I'm a Windows user, so this guide isn't much use if you have a mac / linuxbox / oric atmos / whatever.

DISCLAIMER: While the following procedures worked for me, I accept no responsibility for any problems you may encounter. The following advice carries no guarantees.

STEP 1 - Backup to Kies

Kies is Samsung's answer to iTunes for the Galaxy S3, as far as syncing / backing up your phone goes. It's a bit crap to be honest. I was kinda disappointed that it doesn't backup your Apps or App Data. While your Apps are backed up on Google Play automatically, the App Data isn't backed up unless you do it yourself, and for that you need to be rooted.  But your first step along the safe path should be to download Kies and then use the Back Up option to backup everything you want to save to your computer, just in case.

STEP 2 - Install Odin3 on your PC

Odin3 is a fantastic tool that you use on your computer to upload and install special files onto your phone. You will need to have installed Kies to use it with your S3, cos it needs the USB drivers installed. But ensure Kies in not running whenever you use Odin, because they could interfere with each other. Anyway, get Odin here: http://forum.xda-developers.com/showthread.php?t=1738841

STEP 3 - Install ClockWorkMod Recovery on your phone and Root it!

Now, to root my phone I followed a great post on the XDA Developers forum here: http://forum.xda-developers.com/showthread.php?t=1914394 . The gist of it is, you flash (install) the ClockWorkMod (GT-I9305 version) recovery tools onto your phone, and then you flash the SuperSU package to root it.

However, it's worth bearing in mind the following.

1. As soon as you install CWM Recovery on your phone, it will no longer install OTA (over-the-air) OS updates from your mobile carrier, e.g. when Android 4.2 is finally released for the S3.  So when new ROMs are available you will have to grab the new ROM from somewhere and flash it from Odin instead.
2. As soon as you install CWM Recovery, it increments the S3's Custom Binary Counter. If you have to send your phone back, the Binary Counter will indicate that you've been tinkering with it. However, there is a solution: ChainFire's TriangleAway App which you can buy on Google Play. And at 2 quid it's a great donation to ChainFire who does a lot of great work for the community, including several of the tools we're using here. I can confirm it works BUT you have to be rooted to use it.
3. Before you install the root package or start using custom ROMs etc., take a backup of your entire phone and data using CWM's backup feature. Restart the phone and hold VOL UP + POWER + HOME to enter Recovery mode. Choose "backup and restore" and backup to your internal card or external sdcard if you have one. Reboot your phone, hook it up to your computer by USB and simply drag the backup files to your computer. Super useful if you want to restore your stuff later if you go back to the stock firmware.

ROLLING BACK

So here's the thing I was worried about: How to roll the phone back to stock settings from root. For this you'll need a copy of the stock firmware for your phone / country / carrier. I got mine from this amazing site: http://www.sammobile.com/firmware/?page=3&model=GT-I9305&pcode=EVR&os=1&type=1#firmware
and I can confirm that works for a UK Orange S3 LTE.

So, to revert to stock:

1. Use TriangleAway to reset the Binary Counter.
2. Put your phone into download mode (restart and hold down VOL DOWN + POWER + HOME)
3. Use Odin3 to install the stock firmware file you downloaded.

For more info see this good post: http://forum.xda-developers.com/showthread.php?t=1739426

jQuery UI AutoComplete source option - function callback

Just a quick one: if you have a jQuery UI AutoComplete and you want to use the source option to set a function that does a remote lookup, you'll be aware that the function signature is as follows:

function(request, response)

where request is what the user entered, and response is a function reference that accepts an array of results to display.

The prob we had today was that we wanted to call the response function from the success of a $.ajax() call. But it wouldn't work, and it took ages to figure out that this was because the call was in asynchronous mode. Once we set the $.ajax() async option to false, it worked fine.

HP 2311x Monitor - Blurry / Fuzzy on HDMI from AMD radeon HD 7570

Quick one - hooked this 2nd monitor up to my new desktop on the HDMI port of the AMD Radeon HD 7570. The picture was very blurry. It took a while to find the right setting in Windows 7, which was:

• Right click on desktop, go to Catalyst Control Center
• Desktop and Displays > My Digital Flat-panels > Scaling Options
• Turn Scaling down to 0%
• Return to My Digital Flat-panels
• Select Properties
• Click Enable GPU Scaling

Done! What a crappy default!

SQL Server Attach DB : failed to retrieve text for this error. Reason: 15105

Got this error in SSMS when trying to attach an old DB.

failed to retrieve text for this error. Reason: 15105

Quick answer - run Management Studio as Administrator. The error SSMS was giving on attach then displayed correctly, and in this case was a combination of two things:

• Inadequate permission on the MDF and LDF files
• Trying to attach a 2008 R2 DB on a plain 2008 server

Meh.

ASPNETDB ASP.NET Application Services Membership GUI

I'm not a big fan of the ASPNETDB framework - seems needlessly complicated while still not doing many things that you want from your membership system.

But if you have to use it, one thing that really bugs me is that it doesn't have a simple way to remotely manage the users and roles. Here's a tool to lighten the load, a WinForms GUI app: http://sourceforge.net/projects/aspnetdb/