Stored Procedure in Classic ASP

K

klasik

New Member
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 from 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.\[code\]ALTER PROCEDURE [dbo].[insertLicenseFee]\[code\]-- 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) ASBEGINSET 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\[/code\]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"))\[/code\]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 to correctly, as I can see all the elements and the correct values.
 
You must log in or register to reply here.
Top