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.