I am trying to execute a stored proc from a classic asp page and I am running into several issues with how it is inserting. I am trying to map posted form data to params in the stored procedure, which doesn't seem to be working. I had to convert everything to a varchar just for the insert to complete, and somehow there are "." being inserted at the end of some fields. I have looked at this for hours, and can't seem to find the issues.
ALTER PROCEDURE [dbo].[insertLicenseFee]
-- Add the parameters for the stored procedure here
@uid VARCHAR(255),
@phoneNumber VARCHAR(255),
@accountNumber VARCHAR(255),
@fedID VARCHAR(255),
@dateEnd VARCHAR(255),
@dateDue VARCHAR(255),
@nature NVARCHAR(255),
@dateStarted VARCHAR(255),
@dateDisc VARCHAR(255),
@saleName NVARCHAR(255),
@saleAdd NVARCHAR(255),
@empCounty NVARCHAR(255),
@basisReturn NVARCHAR(255),
@bType NVARCHAR(255),
@bTypeOther NVARCHAR(255),
@grossFedReturn VARCHAR(255),
@busiDeduction VARCHAR(255),
@netBusiness VARCHAR(255),
@notDeductable VARCHAR(255),
@total VARCHAR(255),
@deductItems VARCHAR(255),
@adjustedNet VARCHAR(255),
@netProfits VARCHAR(255),
@licenseFee VARCHAR(255),
@interest VARCHAR(255),
@penalty VARCHAR(255),
@finalTotal VARCHAR(255),
@lessCredits VARCHAR(255),
@lessCreditRadio NVARCHAR(255),
@balanceDue VARCHAR(255),
@stateTax VARCHAR(255),
@capitalGain VARCHAR(255),
@operatingLoss VARCHAR(255),
@partnerSalary VARCHAR(255),
@otherItems VARCHAR(255),
@royalties VARCHAR(255),
@dividends VARCHAR(255),
@capLoss VARCHAR(255),
@otherNotSub VARCHAR(255),
@totalDeductions VARCHAR(255),
@countyWages VARCHAR(255),
@totalWages VARCHAR(255),
@signature NVARCHAR(255),
@title NVARCHAR(255),
@subDate VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
SET @subDate = GETDATE();
INSERT INTO dbo.LicenseFeeForm
( userID ,
phoneNumber ,
accountNumber ,
fedID ,
dateEnd ,
dateDue ,
nature ,
dateStarted ,
dateDisc ,
saleName ,
saleAdd ,
empCounty ,
basisReturn ,
bType ,
bTypeOther ,
grossFedReturn ,
busiDeductions ,
netBusiness ,
notDeductable ,
total ,
deductItems ,
adjustedNed ,
netProfits ,
licenseFee ,
intrest ,
penalty ,
finalTotal ,
lessCredits ,
lessCreditRadio ,
balanceDue ,
stateTax ,
capitalGain ,
operatingLoss ,
partnerSalary ,
otherItems ,
royalties ,
dividends ,
capLoss ,
otherNotSub ,
totalDeductions ,
countyWages ,
totalWages ,
signature ,
title ,
subDate
)
VALUES ( @uid , -- userID - VARCHAR(255)
@phoneNumber , -- phoneNumber - VARCHAR(255)
@accountNumber , -- accountNumber - VARCHAR(255)
@fedID , -- fedID - VARCHAR(255)
@dateEnd , -- dateEnd - date
@dateDue , -- dateDue - date
@nature , -- nature - nvarchar(255)
@dateStarted , -- dateStarted - date
@dateDisc , -- dateDisc - date
@saleName , -- saleName - nvarchar(255)
@saleAdd , -- saleAdd - nvarchar(255)
@empCounty , -- empCounty - nvarchar(255)
@basisReturn , -- basisReturn - nvarchar(255)
@bType, -- bType - nvarchar(255)
@bTypeOther, -- bTypeOther - nvarchar(255)
@grossFedReturn , -- grossFedReturn - VARCHAR(255)
@busiDeduction , -- busiDeductions - VARCHAR(255)
@netBusiness , -- netBusiness - VARCHAR(255)
@notDeductable , -- notDeductable - VARCHAR(255)
@total , -- total - VARCHAR(255)
@deductItems , -- deductItems - VARCHAR(255)
@adjustedNet , -- adjustedNed - VARCHAR(255)
@netProfits , -- netProfits - VARCHAR(255)
@licenseFee , -- licenseFee - VARCHAR(255)
@interest , -- VARCHAR(255)rest - VARCHAR(255)
@penalty , -- penalty - VARCHAR(255)
@finalTotal , -- finalTotal - VARCHAR(255)
@lessCredits , -- lessCredits - VARCHAR(255)
@lessCreditRadio , -- lessCreditRadio - nvarchar(255)
@balanceDue , -- balanceDue - VARCHAR(255)
@stateTax , -- stateTax - VARCHAR(255)
@capitalGain , -- capitalGain - VARCHAR(255)
@operatingLoss , -- operatingLoss - VARCHAR(255)
@partnerSalary , -- partnerSalary - VARCHAR(255)
@otherItems , -- otherItems - VARCHAR(255)
@royalties , -- royalties - VARCHAR(255)
@dividends , -- dividends - VARCHAR(255)
@capLoss , -- capLoss - VARCHAR(255)
@otherNotSub , -- otherNotSub - VARCHAR(255)
@totalDeductions , -- totalDeductions - VARCHAR(255)
@countyWages , -- countyWages - VARCHAR(255)
@totalWages , -- totalWages - VARCHAR(255)
@signature , -- signature - nvarchar(255)
@title , -- title - nvarchar(255)
GETDATE() -- subDate - date
)
END
And the HTML for the execution and parameter building is as follows:
Set cmdInsert = Server.CreateObject("ADODB.Command")
Set cmdInsert.ActiveConnection = Conn
cmdInsert.CommandText = "insertLicenseFee"
cmdInsert.CommandType=adCmdStoredProc
cmdInsert.Parameters.Append cmdInsert.createParameter("@userID", adInteger, adParamInput, 255, cint(Session("uid")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@phoneNumber", adVarChar, adParamInput, 255, (Request.Form("phoneNumber")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@accountNumber", adVarChar, adParamInput,255 ,Request.Form("accountNumber"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@fedID", adVarChar, adParamInput,255 , (Request.Form("fedID")))
IF Request.Form("dateEnd") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateEnd", adVarChar, adParamInput, 255, (Request.Form("dateEnd")))
Else
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateEnd", adVarChar, adParamInput,255 , "-")
End If
If Request.Form("dateDue") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateDue", adVarChar, adParamInput, 255, (Request.Form("dateDue")))
ELSE
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateDue", adVarChar, adParamInput,255 , "-")
End If
cmdInsert.Parameters.Append cmdInsert.createParameter("@nature", adVarChar, adParamInput, 255, (Request.Form("nature")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateStarted", adVarChar, adParamInput, 255, (Request.Form("dateStarted")))
If Request.Form("dateDisc") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateDisc", adVarChar, adParamInput,255 , (Request.Form("dateDisc")))
Else
cmdInsert.Parameters.Append cmdInsert.createParameter("@dateDisc", adVarChar, adParamInput, 255, "-")
End If
If Request.Form("saleName") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@saleName", adVarChar, adParamInput, 255, (Request.Form("saleName")))
ELSE
cmdInsert.Parameters.Append cmdInsert.createParameter("@saleName", adVarChar, adParamInput, 255, "-")
End If
If Request.Form("saleAdd") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@saleAdd", adVarChar, adParamInput, 255, Request.Form("saleAdd"))
ELSE
cmdInsert.Parameters.Append cmdInsert.createParameter("@saleAdd", adVarChar, adParamInput, 255, "-")
End If
cmdInsert.Parameters.Append cmdInsert.createParameter("@empCounty", adVarChar, adParamInput, 255,Request.Form("empCounty"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@basisReturn", adVarChar, adParamInput,255 , Request.Form("basisReturn"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@bType", adVarChar, adParamInput,255, Request.Form("bType"))
If Request.Form("bTypeOther") <> "" THEN
cmdInsert.Parameters.Append cmdInsert.createParameter("@bTypeOther", adVarChar, adParamInput,255 , Request.Form("bTypeOther"))
Else
cmdInsert.Parameters.Append cmdInsert.createParameter("@bTypeOther", adVarChar, adParamInput, 255, "-")
End If
cmdInsert.Parameters.Append cmdInsert.createParameter("@grossFedReturn", adVarChar, adParamInput,255 , (Request.Form("grossFedReturn")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@busiDeductions", adVarChar, adParamInput,255 , (Request.Form("busiDeductions")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@netBusiness", adVarChar, adParamInput,255 , (Request.Form("netBusiness")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@notDeductable", adVarChar, adParamInput, 255,(Request.Form("notDeductable")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@total", adVarChar, adParamInput,255 , (Request.Form("total")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@deductItems", adVarChar, adParamInput,255 , (Request.Form("deductItems")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@adjustedNet", adVarChar, adParamInput,255 , (Request.Form("adjustedNet")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@netProfits", adVarChar, adParamInput,255 , (Request.Form("netProfits")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@licenseFee", adVarChar,adParamInput,255 , (Request.Form("licenseFee")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@interest", adVarChar, adParamInput,255 , (Request.Form("interest")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@penalty", adVarChar, adParamInput,255 , (Request.Form("penalty")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@finalTotal", adVarChar, adParamInput,255 , (Request.Form("finalTotal")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@lessCredits", adVarChar, adParamInput,255 , (Request.Form("lessCredits")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@lessCreditRadio", adVarChar, adParamInput, 255, Request.Form("lessCreditRadio"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@balanceDue", adVarChar, adParamInput,255 , (Request.Form("balanceDue")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@stateTax" , adVarChar, adParamInput,255 , (Request.Form("stateTax")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@capitalGain", adVarChar, adParamInput,255 , (Request.Form("capitalGain")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@operatingLoss", adVarChar, adParamInput,255 , (Request.Form("operatingLoss")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@partnerSalary", adVarChar, adParamInput,255 , (Request.Form("partnerSalary")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@otherItems", adVarChar, adParamInput,255 , (Request.Form("otherItems")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@royalties", adVarChar, adParamInput,255 , (Request.Form("royalties")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@dividends", adVarChar, adParamInput,255 , (Request.Form("dividends")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@capLoss", adVarChar, adParamInput,255 , (Request.Form("capLoss")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@otherNotSub", adVarChar, adParamInput,255 , (Request.Form("otherNotSub")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@totalDeductions", adVarChar, adParamInput,255 , (Request.Form("totalDeductions")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@countyWages", adVarChar, adParamInput,255 , (Request.Form("countyWages")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@totalWages", adVarChar, adParamInput,255 , (Request.Form("totalWages")))
cmdInsert.Parameters.Append cmdInsert.createParameter("@signature", adVarChar, adParamInput,255 , Request.Form("signature"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@title", adVarChar, adParamInput, 255, Request.Form("title"))
cmdInsert.Parameters.Append cmdInsert.createParameter("@title", adVarChar, adParamInput, 255, Request.Form("title"))
If I take out the append for @title, which appears twice, I get an error message stating "Procedure or function 'insertLicenseFee' expects parameter '@subDate', which was not supplied".
I am just lost as to what the error could be here. Would someone please help me out before I go nuts? I know all the form data is being passed over correctly, as I can see all the elements and the correct values.
INSERT INTOstatement, "interest" is spelled as "intrest". Is that a copy-paste error, or an actual typo? Have you tested the stored procedure in Management Studio? - Cheran Shunmugavelvarcharfor dates? Bad idea - LittleBobbyTables - Au Revoir