23 Apr 2013

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

No comments:

Post a Comment

Comments are very welcome unless you're a spammer, in which case you should probably kill yourself.

If I helped you out today, you can buy me a beer below. Cheers!