Avatar of amruthanet
amruthanet
 asked on

Urgent - Error -2147217900 - not able to save to the database - Please help

The program and store procedure works fine, when hsaplan has values.  

Whenever the session variable does not exist or empty, it is throwing this error.  Please help.

This is the error we received:
spConfirmEmp "W0000012206"," "," "," "," YNNBNNNN"," "," ",34,0,,' 'Error -2147217900. Line 1: Incorrect syntax near ','.
Information was not saved.
___________________________________________________________________________________________________
ASP code:

<%
err.number=0
on error resume next
'Update database with session variables
Dim strCmd
Dim intErrorNum
Dim strErrorDesc
Dim hcamt
Dim dcamt
Dim strMPCP

conn.BeginTrans

'Employee and FSA update
'Create command string
hcamt = Session("fsahcamount")
dcamt = Session("fsadcamount")
hsaAmount = Session("Hsa_Amount")
hsaPlan = session("HSA_Plan")' HSA single or dependent
If not isnumeric(hcamt) AND not isnull(hcamt) then hcamt = 0
If not isnumeric(dcamt) AND not isnull(dcamt) then dcamt = 0
If not isnumeric(hsaAmount) AND not isnull(hsaAmount) then hsaAmount = 0


strMPCP = Session("medicalpcp")

if session("MemberNewPlan") = "      "      then
hsaPlan = "      "
end if

if session("MemberNewPlan") = "      " then
hsaAmount =0
end if


If strMPCP = "" Then strMPCP = " "

strCmd = "spConfirmEmp " & QT & Session("emplid") & QT & "," & QT & _
            Session("medicalplan") & QT & "," & QT & Session("dentalplan") & _
            QT & "," & QT & Session("visionplan") & QT & "," & QT & Session("empflags") & _
            QT & "," & QT & strMPCP & QT
strCmd = strCmd & "," & QT & " " & QT & "," & hcamt & "," &  dcamt & _
            "," & hsaAmount & ",'" & hsaPlan & "'"


Response.Write strcmd
'Response.End


conn.execute(strcmd)


If Err.number <> 0 then
      conn.RollbackTrans
      Response.Write("Error " & Err.number & ". " & Err.Description)
      Response.Write("<BR>Information was not saved.<BR>")
      Response.End
End If

_____________________________________________________________________________________________
Microsoft SQL Server

Avatar of undefined
Last Comment
amruthanet

8/22/2022 - Mon
amruthanet

ASKER
Store procedure:

Store Procedure:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spConfirmEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spConfirmEmp]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spConfirmEmp
     @Emplid      char(11),
     @MedicalPlan     char(6),
     @DentalPlan     char(6),
     @VisionPlan     char(6),
     @EmpFlags     char(14),
     @EmpMedicalPCP     char(10),
     @EmpDentalPCP     char(10),
     @FSAHCAmount     dec(6,2),
     @FSADCAmount     dec(6,2),
     @HSAAmount          dec(6,2),
     @HSA_Plan     char(6)

AS
/*************************
This updates the session variables to the Employee and FSA tables
*************************/
/*
Declare     @FSAHCAmount     dec(6,2)
Declare     @FSADCAmount     dec(6,2)
*/
/*Declare @DrugPlan     char(6)*/
Declare @medicalelect     char(1)
Declare @dentalelect     char(1)
Declare @visionelect     char(1)
Declare @medicallevel     char(1)
Declare @dentallevel     char(1)
Declare @visionlevel     char(1)
Declare @fsahcelect     char(1)
Declare @fsadcelect     char(1)
Declare @tobacco     char(1)
Declare @tax          char(1)
Declare @ee16cycle     char(1)

Declare @medicalcode     char(3)
Declare @dentalcode     char(3)
/*Declare @drugcode     char(3)*/
Declare @visioncode     char(3)
Declare @hsasingleplan   char(6)
Declare @hsasingleelect  char(1)
Declare @hsasinglelevel   char(1)
Declare @hsasinglecode char(3)
Declare @hsasingleamt   dec(6,2)
Declare @hsadepplan   char(6)
Declare @hsadepelect  char(1)
Declare @hsadeplevel   char(1)
Declare @hsadepcode char(3)
Declare @hsadepamt   dec(6,2)

Declare @temp          char(6)
Declare @fsaexists     char(1)
Declare @hsaFlag     char(1)

Set @medicalelect = SubString(@EmpFlags,1,1)
Set @dentalelect = SubString(@EmpFlags,2,1)
Set @visionelect = SubString(@EmpFlags,3,1)
Set @medicallevel = SubString(@EmpFlags,4,1)
Set @dentallevel = SubString(@EmpFlags,5,1)
Set @visionlevel = SubString(@EmpFlags,6,1)
Set @fsahcelect     = SubString(@EmpFlags,7,1)
Set @fsadcelect     = SubString(@EmpFlags,8,1)
Set @tobacco = SubString(@EmpFlags,9,1)
Set @tax = SubString(@EmpFlags,10,1)
Set @ee16cycle = SubString(@EmpFlags,11,1)

/*
Set @FSAHCAmount = Cast(@HCAmt As dec(6,2))
Set @FSADCAmount = Cast(@DCAmt As dec(6,2))
*/


/* Update Employee  for HSA records*/
If @hsaAmount > 0 Set @HsaFlag = 'Y'
else Set @HsaFlag = null

 If (@hsa_plan <>'HSASGL' OR @hsa_plan <> 'HSASGX' or @hsa_plan <> 'HSADEP' OR @hsa_plan <>'HSADEX') begin     --If no HSA  
set @hsasingleamt = 0
set @hsasingleelect = null
set @hsasingleplan=NULL
set @hsadepamt = 0
Set @hsadepelect =null
Set @hsadepplan=NULL

end


 If (@hsa_plan = 'HSASGL' OR @hsa_plan =  'HSASGX' ) begin
Set @hsasingleamt = @HsaAmount
Set @hsasingleelect =  'E'
Set @hsasingleplan = @hsa_plan
end

 If (@hsa_plan = 'HSADEP' OR @hsa_plan = 'HSADEX')  begin
Set @hsadepamt = @Hsaamount
Set @hsadepelect =  'E'
Set @hsadepplan=@hsa_plan
end





UPDATE Employee SET
     MedicalPlan = @MedicalPlan,
     DentalPlan = @DentalPlan,
     VisionPlan = @VisionPlan,
     MedicalCode = @medicalcode,
     DentalCode = @dentalcode,
     /*DrugCode = @drugcode,*/
     VisionCode = @visioncode,
     TaxResponse = @tax,
     SmokerResponse = @tobacco,
     EmpMedicalPCP = @EmpMedicalPCP,
     EmpDentalPCP = @EmpDentalPCP,
     MedicalElect = @medicalelect,
     /*DrugElect = @medicalelect,*/
     DentalElect = @dentalelect,
     VisionElect = @visionelect,
     MedicalLevel = @medicallevel,
     /*DrugLevel = @medicallevel,*/
     DentalLevel = @dentallevel,
     VisionLevel = @visionlevel,
     hsasingleplan=@hsasingleplan,
     hsasingleelect=@hsasingleelect,
     /*hsasinglelevel = @hsasinglelevel,*/
     /*hsasinglecode=@hsasinglecode*/
     hsasingleamt=@hsasingleamt,
     hsadepplan=@hsadepplan,
     hsadepelect=@hsadepelect,
     /*hsadeplevel = @hsadeplevel,*/
     /*hsadepcode=@hsadepcode,*/
     hsadepamt=@hsadepamt,
                 UpdateFlag='C',
     changedate = GetDate(),
     changeby = Current_User

WHERE Emplid = @Emplid

/*********************   FSA     and   HSA **********************/

/*If @hsaAmount > 0 Set @HsaFlag = 'Y'
else Set @HsaFlag = null*/

Set @fsaexists = 'N'
Select top 1 @fsaexists = UpdateFlag FROM FSA WHERE Emplid = @Emplid AND UpdateFlag = 'A'

If @fsahcelect <> 'Y' Begin
     Set @FSAHCAmount = NULL
End
If @fsadcelect <> 'Y' Begin
     Set @FSADCAmount = NULL
End

/*If @HsaFlag <> 'Y' begin          --If no HSA
     Set @hsaAmount = null
     Set @HSA_Plan = null
End  */


/*If (@fsahcelect = 'Y' OR @fsadcelect = 'Y' OR @HsaFlag = 'Y') Begin     */
If (@fsahcelect = 'Y' OR @fsadcelect = 'Y' ) Begin    
     /* HAVE FSA for next year */
     /* If record type A exists, update it, else insert one */
     
     If (IsNull(@fsaexists,'N') = 'N') Begin     --no record, creat one
          INSERT INTO FSA (Emplid, UpdateFlag, HCareElect, HCareAmount,
          DCareElect, DCareAmount,  EE16Cycle)
          VALUES (@Emplid, 'A', @fsahcelect, @FSAHCAmount, @fsadcelect, @FSADCAmount,
           @ee16cycle)
     End
     Else If (IsNull(@fsaexists,'N') = 'A') Begin     --record exist, update it
          UPDATE FSA SET
          Emplid = @Emplid,
          HCareElect = @fsahcelect,
          HCareAmount = @FSAHCAmount,
          DCareElect = @fsadcelect,
          DCareAmount = @FSADCAmount,
          changedate = GetDate(),
          changeby = Current_User
          WHERE Emplid = @Emplid AND UpdateFlag = 'A'
     End
End          /* HAVE */
Else Begin     /* DON'T HAVE FSA or HSA for next year */
     
     /* If record type A exists, delete it  */
     If (IsNull(@fsaexists,'N') = 'A') Begin
          DELETE FROM FSA  WHERE Emplid = @Emplid AND UpdateFlag = 'A'
     End

End           /* DON'T HAVE */
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ASKER CERTIFIED SOLUTION
Hypnochu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
imran_fast

change this
spConfirmEmp 'W0000012206',' ',' ',' ',' YNNBNNNN',' ',' ',34,0,,' '
to
spConfirmEmp 'W0000012206',' ',' ',' ',' YNNBNNNN',' ',' ',34,0,' ',' '

imran_fast

Change sp code



ASP code:

<%
err.number=0
on error resume next
'Update database with session variables
Dim strCmd
Dim intErrorNum
Dim strErrorDesc
Dim hcamt
Dim dcamt
Dim strMPCP

conn.BeginTrans

'Employee and FSA update
'Create command string
hcamt = Session("fsahcamount")
dcamt = Session("fsadcamount")
hsaAmount = Session("Hsa_Amount")
hsaPlan = session("HSA_Plan")' HSA single or dependent
If not isnumeric(hcamt) AND not isnull(hcamt) then hcamt = 0
If not isnumeric(dcamt) AND not isnull(dcamt) then dcamt = 0
If not isnumeric(hsaAmount) AND not isnull(hsaAmount) then hsaAmount = 0


strMPCP = Session("medicalpcp")

if session("MemberNewPlan") = "      "     then
hsaPlan = "      "
end if

if session("MemberNewPlan") = "      " then
hsaAmount =0
end if


If strMPCP = "" Then strMPCP = " "
strCmd = "spConfirmEmp " & QT & Session("emplid") & QT & "," & QT & _
          Session("medicalplan") & QT & "," & QT & Session("dentalplan") & _
          QT & "," & QT & Session("visionplan") & QT & "," & QT & Session("empflags") & _
          QT & "," & QT & strMPCP & QT
strCmd = strCmd & "," & QT & " " & QT & "," & hcamt & "," &  dcamt & _
          ",'" & hsaAmount & "','" & hsaPlan & "'"

Response.Write strcmd
'Response.End


conn.execute(strcmd)


If Err.number <> 0 then
     conn.RollbackTrans
     Response.Write("Error " & Err.number & ". " & Err.Description)
     Response.Write("<BR>Information was not saved.<BR>")
     Response.End
End If

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
amruthanet

ASKER
Hi  Hypnochu,

The solution works great.

Thanks.