sql server store procedure issue

Question

hello, i am trying to insert the data by using store procedure, where i am gettng error that is cann't convert varchar to int in ExecuteNonquery. and also i am trying to execute store procedure where the query executed succesfully but the row of data is not inserted. anybody please help me out and also help me that why the problem araises

Suraj Kumar · Answer

Hello Swamy, I request you to alter your procedure as suggested here. I want to inform you that after executing procedure you need to check number of rows affected using @@rowcount. If it is greater than or equal to 1 then return 1 else you need to return 0. It has been shown here. ALTER procedure [dbo].[usp_DEHUMIDIFIER_CALCULATION_TABLE] ( @strFlag varchar (50), @intCALCULATION_ID int , @intPROJECT_ID int , @strPROJECT_CODE varchar (50) , @strAMBIENT_DB int , @strAMBIENT_WB decimal , @strAMBIENT_HUMIDITY decimal , @strAMBIENT_ENTHALPY decimal , @strSORROUNDING_DB decimal , @strSORROUNDING_WB decimal , @strSORROUNDING_HUMIDITY decimal , @strSORROUNDING_ENTHALPY decimal , @strROOM_DB decimal , @strROOM_WB decimal , @strROOM_HUMIDITY decimal , @strROOM_ENTHALPY decimal , @strROOM_LENGTH decimal , @strROOM_WIDTH decimal , @strROOM_HEIGHT decimal , @strROOM_AREA decimal , @strDOORS int , @strDOORSONE_SIZE decimal , @strDOORSONE_AVGOPENING int , @strDOORSTWO_SIZEONE decimal , @strDOORSTWO_SIZETWO Decimal , @strDOORSTWO_AVGOPENING int , @strDOORSTHREE_SIZEONE decimal , @strDOORSTHREE_SIZETWO decimal , @strDOORSTHREE_SIZETHREE decimal , @strDOORSTHREE_AVGOPENING int , @strFIXEDOPENING int , @strFIXEDOPENING_ONE_AREA decimal , @strFIXEDOPENING_ONE_DEPTH decimal , @strFIXEDOPENING_TWO_AREA_ONE decimal , @strFIXEDOPENING_TWO_DEPTH_ONE decimal , @strFIXEDOPENING_TWO_AREA_TWO decimal , @strFIXEDOPENING_TWO_DEPTH_TWO decimal , @strNUM_OF_OCCUPANTS int , @strF5_FACTOR int , @intF5_FACTOR_TYPE int , @strPRODUCT_LOAD_TYPE int , @strIMC_FMC_QTY int , @strIMC_FMC_INITIAL_MOISTURE decimal , @strIMC_FMC_FINAL_MOISTURE decimal , @strIMC_FMC_DRYING_TIME_DAY int , @strIMC_FMC_DRYING_TIME_HOUR int , @strWEIGHT_WETWEIGHT decimal , @strWEIGHT_DRYWEIGHT decimal , @strWEIGHT_DRYING_TIME_DAY int , @strWEIGHT_DRYING_TIME_HOUR int , @strMOISTUREREMOVED decimal , @strMOISTUREREMOVED_DRYING_TIME_DAY int , @strMOISTUREREMOVED_DRYING_TIME_HOUR int , @strBY_QTY decimal , @strBY_PERCENTAGE decimal , @strBY_DRYING_TIME_DAY int , @strBY_DRYING_TIME_HOUR int , @strFRESH_AIR int , @strPRECOOLSELECTION varchar (50), @strMAKEUPAIR_REQUIREDCFM decimal , @strPRECOOL_DBT decimal , @strPRECOOL_WBT decimal , @strPRECOOL_ABSHUMIDITY decimal , @strPRECOOL_ENTHALPHY decimal , @strINFILTRRATION_LOAD decimal , @strOTHER_LOAD decimal , @strSENSIBLE_LOAD decimal , @strSAFETY_FACTOR decimal , @strPERMIATION_LOAD decimal , @strDOOR_LOAD decimal , @strFIXED_OPENING_LOAD decimal , @strPEOPLE_LOAD decimal , @strPRODUCT_LOAD decimal , @strINFILTRATION_LOAD decimal , @strCALCULATED_OTHER_LOAD decimal , @strSUBTOTAL decimal , @strCALCULATED_SAFETY_FACTOR decimal , @strINTERNAL_ROOM_LOAD decimal , @strCFM_REQUIRED decimal , @strFRESH_AIR_LOAD decimal , @strTOTAL_LOAD decimal , @strCFM_REQUIRED_TOTAL decimal ) as begin if(@strFlag = 'Save' ) begin --select 'You are at right place'; insert into DEHUMIDIFIER_CALCULATION_TABLE( PROJECT_ID,PROJECT_CODE,AMBIENT_DB,AMBIENT_WB,AMBIENT_HUMIDITY, AMBIENT_ENTHALPY,SORROUNDING_DB,SORROUNDING_WB,SORROUNDING_HUMIDITY, SORROUNDING_ENTHALPY,ROOM_DB,ROOM_WB,ROOM_HUMIDITY,ROOM_ENTHALPY,ROOM_LENGTH, ROOM_WIDTH,ROOM_HEIGHT,ROOM_AREA,DOORS,DOORSONE_SIZE,DOORSONE_AVGOPENING,DOORSTWO_SIZEONE, DOORSTWO_SIZETWO,DOORSTWO_AVGOPENING,DOORSTHREE_SIZEONE,DOORSTHREE_SIZETWO,DOORSTHREE_SIZETHREE, DOORSTHREE_AVGOPENING,FIXEDOPENING,FIXEDOPENING_ONE_AREA,FIXEDOPENING_ONE_DEPTH,FIXEDOPENING_TWO_AREA_ONE, FIXEDOPENING_TWO_DEPTH_ONE,FIXEDOPENING_TWO_AREA_TWO,FIXEDOPENING_TWO_DEPTH_TWO,NUM_OF_OCCUPANTS,F5_FACTOR,F5_FACTOR_TYPE, PRODUCT_LOAD_TYPE,IMC_FMC_QTY,IMC_FMC_INITIAL_MOISTURE,IMC_FMC_FINAL_MOISTURE,IMC_FMC_DRYING_TIME_DAY, IMC_FMC_DRYING_TIME_HOUR,WEIGHT_WETWEIGHT,WEIGHT_DRYWEIGHT,WEIGHT_DRYING_TIME_DAY,WEIGHT_DRYING_TIME_HOUR, MOISTUREREMOVED,MOISTUREREMOVED_DRYING_TIME_DAY,MOISTUREREMOVED_DRYING_TIME_HOUR,BY_QTY,BY_PERCENTAGE,BY_DRYING_TIME_DAY, BY_DRYING_TIME_HOUR,FRESH_AIR,PRECOOLSELECTION,MAKEUPAIR_REQUIREDCFM,PRECOOL_DBT,PRECOOL_WBT,PRECOOL_ABSHUMIDITY,PRECOOL_ENTHALPHY, INFILTRRATION_LOAD,OTHER_LOAD,SENSIBLE_LOAD,SAFETY_FACTOR,PERMIATION_LOAD,DOOR_LOAD,FIXED_OPENING_LOAD,PEOPLE_LOAD, PRODUCT_LOAD,INFILTRATION_LOAD,CALCULATED_OTHER_LOAD,SUBTOTAL,CALCULATED_SAFETY_FACTOR,INTERNAL_ROOM_LOAD,CFM_REQUIRED, FRESH_AIR_LOAD,TOTAL_LOAD,CFM_REQUIRED_TOTAL ) values ( @intPROJECT_ID ,@strPROJECT_CODE,@strAMBIENT_DB , @strAMBIENT_WB,@strAMBIENT_HUMIDITY ,@strAMBIENT_ENTHALPY , @strSORROUNDING_DB,@strSORROUNDING_WB , @strSORROUNDING_HUMIDITY,@strSORROUNDING_ENTHALPY, @strROOM_DB,@strROOM_WB,@strROOM_HUMIDITY, @strROOM_ENTHALPY, @strROOM_LENGTH,@strROOM_WIDTH, @strROOM_HEIGHT, @strROOM_AREA , @strDOORS , @strDOORSONE_SIZE , @strDOORSONE_AVGOPENING , @strDOORSTWO_SIZEONE , @strDOORSTWO_SIZETWO , @strDOORSTWO_AVGOPENING , @strDOORSTHREE_SIZEONE , @strDOORSTHREE_SIZETWO , @strDOORSTHREE_SIZETHREE , @strDOORSTHREE_AVGOPENING , @strFIXEDOPENING , @strFIXEDOPENING_ONE_AREA , @strFIXEDOPENING_ONE_DEPTH , @strFIXEDOPENING_TWO_AREA_ONE , @strFIXEDOPENING_TWO_DEPTH_ONE , @strFIXEDOPENING_TWO_AREA_TWO , @strFIXEDOPENING_TWO_DEPTH_TWO , @strNUM_OF_OCCUPANTS , @strF5_FACTOR , @intF5_FACTOR_TYPE, @strPRODUCT_LOAD_TYPE , @strIMC_FMC_QTY , @strIMC_FMC_INITIAL_MOISTURE , @strIMC_FMC_FINAL_MOISTURE , @strIMC_FMC_DRYING_TIME_DAY , @strIMC_FMC_DRYING_TIME_HOUR , @strWEIGHT_WETWEIGHT , @strWEIGHT_DRYWEIGHT , @strWEIGHT_DRYING_TIME_DAY , @strWEIGHT_DRYING_TIME_HOUR , @strMOISTUREREMOVED , @strMOISTUREREMOVED_DRYING_TIME_DAY , @strMOISTUREREMOVED_DRYING_TIME_HOUR , @strBY_QTY , @strBY_PERCENTAGE , @strBY_DRYING_TIME_DAY , @strBY_DRYING_TIME_HOUR , @strFRESH_AIR , @strPRECOOLSELECTION , @strMAKEUPAIR_REQUIREDCFM , @strPRECOOL_DBT , @strPRECOOL_WBT , @strPRECOOL_ABSHUMIDITY, @strPRECOOL_ENTHALPHY , @strINFILTRRATION_LOAD , @strOTHER_LOAD , @strSENSIBLE_LOAD , @strSAFETY_FACTOR , @strPERMIATION_LOAD , @strDOOR_LOAD , @strFIXED_OPENING_LOAD , @strPEOPLE_LOAD , @strPRODUCT_LOAD , @strINFILTRATION_LOAD , @strCALCULATED_OTHER_LOAD , @strSUBTOTAL , @strCALCULATED_SAFETY_FACTOR , @strINTERNAL_ROOM_LOAD , @strCFM_REQUIRED , @strFRESH_AIR_LOAD , @strTOTAL_LOAD , @strCFM_REQUIRED_TOTAL ) if @@rowcount = 1 return 1 else return 0 end end To test and execute the procedure run the below sql statements with test values. -- SELECT * FROM DEHUMIDIFIER_CALCULATION_TABLE DECLARE @return_value int EXEC @return_value = [dbo].[usp_DEHUMIDIFIER_CALCULATION_TABLE] @strFlag = N 'Save' , @intCALCULATION_ID = 50, @intPROJECT_ID = 50, @strPROJECT_CODE = N 'TestValue' , @strAMBIENT_DB = 50, @strAMBIENT_WB = 100.50, @strAMBIENT_HUMIDITY = 100.50, @strAMBIENT_ENTHALPY = 100.50 , @strSORROUNDING_DB = 100.50 , @strSORROUNDING_WB = 100.50, @strSORROUNDING_HUMIDITY = 100.50, @strSORROUNDING_ENTHALPY = 100.50, @strROOM_DB = 100.50, @strROOM_WB = 100.50, @strROOM_HUMIDITY = 100.50, @strROOM_ENTHALPY = 100.50, @strROOM_LENGTH = 100.50, @strROOM_WIDTH = 100.50, @strROOM_HEIGHT = 100.50, @strROOM_AREA = 100.50, @strDOORS = 50, @strDOORSONE_SIZE = 100.50, @strDOORSONE_AVGOPENING = 50, @strDOORSTWO_SIZEONE = 100.50, @strDOORSTWO_SIZETWO = 100.50, @strDOORSTWO_AVGOPENING = 50, @strDOORSTHREE_SIZEONE = 100.50, @strDOORSTHREE_SIZETWO = 100.50, @strDOORSTHREE_SIZETHREE = 100.50, @strDOORSTHREE_AVGOPENING = 50, @strFIXEDOPENING = 50, @strFIXEDOPENING_ONE_AREA = 100.50, @strFIXEDOPENING_ONE_DEPTH = 100.50, @strFIXEDOPENING_TWO_AREA_ONE = 100.50, @strFIXEDOPENING_TWO_DEPTH_ONE = 100.50, @strFIXEDOPENING_TWO_AREA_TWO = 100.50, @strFIXEDOPENING_TWO_DEPTH_TWO = 100.50, @strNUM_OF_OCCUPANTS = 50, @strF5_FACTOR = 50, @intF5_FACTOR_TYPE = 50, @strPRODUCT_LOAD_TYPE = 50, @strIMC_FMC_QTY = 50, @strIMC_FMC_INITIAL_MOISTURE = 100.50, @strIMC_FMC_FINAL_MOISTURE = 100.50, @strIMC_FMC_DRYING_TIME_DAY = 50, @strIMC_FMC_DRYING_TIME_HOUR = 50, @strWEIGHT_WETWEIGHT = 100.50, @strWEIGHT_DRYWEIGHT = 100.50, @strWEIGHT_DRYING_TIME_DAY = 50, @strWEIGHT_DRYING_TIME_HOUR = 50, @strMOISTUREREMOVED = 100.50, @strMOISTUREREMOVED_DRYING_TIME_DAY = 50, @strMOISTUREREMOVED_DRYING_TIME_HOUR = 50, @strBY_QTY = 100.50, @strBY_PERCENTAGE = 100.50, @strBY_DRYING_TIME_DAY = 50, @strBY_DRYING_TIME_HOUR = 50, @strFRESH_AIR = 50, @strPRECOOLSELECTION = N 'TestValue' , @strMAKEUPAIR_REQUIREDCFM = 100.50, @strPRECOOL_DBT = 100.50, @strPRECOOL_WBT = 100.50, @strPRECOOL_ABSHUMIDITY = 100.50, @strPRECOOL_ENTHALPHY = 100.50, @strINFILTRRATION_LOAD = 100.50, @strOTHER_LOAD = 100.50, @strSENSIBLE_LOAD = 100.50, @strSAFETY_FACTOR = 100.50, @strPERMIATION_LOAD = 100.50, @strDOOR_LOAD = 100.50, @strFIXED_OPENING_LOAD = 100.50, @strPEOPLE_LOAD = 100.50, @strPRODUCT_LOAD = 100.50, @strINFILTRATION_LOAD = 100.50, @strCALCULATED_OTHER_LOAD = 100.50, @strSUBTOTAL = 100.50, @strCALCULATED_SAFETY_FACTOR = 100.50, @strINTERNAL_ROOM_LOAD = 100.50, @strCFM_REQUIRED = 100.50, @strFRESH_AIR_LOAD = 100.50, @strTOTAL_LOAD = 100.50, @strCFM_REQUIRED_TOTAL = 100.50 SELECT 'Return Value' = @return_value Hope this will help you. At last if you are passing the values your code then you need to check where you are passing string values to the integer/decimal parameters. You can use int.TryParse and Decimal.TryParse to check string values before passing it to parameter to avoid string conversion error in csharp.net. @updated now.

Suraj Kumar · Answer

Thanks and happy to know that you have resolved your query.

SWAMY YT · Answer

thank you so much sir for your supporting, now the query is excecuted crrectly, what i am noted the points here are like 1) for the data type decimal we have to use the decimal data type only (before i assuming that varchar is the currect data type for the decimal) in the store procedure. 2)and i have to rectify the strting characters not to allow from users side only(validating that only allowing numeric values),then only the data has to be excecuted currectly.

SWAMY YT · Answer

No sir, Still I am facing the same issue.

Suraj Kumar · Answer

Hi Swamy, Have you resolved your query posted here.. Regard Suraj

Suraj Kumar · Answer

Hello Swamy, First of all thanks for sharing your output. I want to say you to format shared code with syntax highlighter. Now come to the point. You have declared several columns in table as integer datataype value and you are trying to insert values in all that column. You need to check all that columns. I am giving the lists of some which are as follows. @strF5_FACTOR = N'd', - It should be integer. @strIMC_FMC_QTY = N'f', - Same @strIMC_FMC_DRYING_TIME_DAY = N'f', - Same Check with other and replace varchar with desired length, default length of varchar is 30. You need to be specific as per your requiremen such as varchar(20), varchar(30), etc. as per your need. You have declared only 4 parameters as int but in table it has 21 column with int datatype. Hope this help you. Regard Suraj

SWAMY YT · Answer

hello Suraj Kumar, here is the excecuted code and it returns the value 0 USE [DEHUMIDIFIER] GO DECLARE @return_value int EXEC @return_value = [dbo].[usp_DEHUMIDIFIER_CALCULATION_TABLE] @strFlag = N'Save', @intCALCULATION_ID = 1, @intPROJECT_ID = 1, @strPROJECT_CODE = N'proj1111', @strAMBIENT_DB = N'fd', @strAMBIENT_WB = N'ddf', @strAMBIENT_HUMIDITY = N'df', @strAMBIENT_ENTHALPY = N'df', @strSORROUNDING_DB = N'df', @strSORROUNDING_WB = N'df', @strSORROUNDING_HUMIDITY = N'fd', @strSORROUNDING_ENTHALPY = N'df', @strROOM_DB = N'fd', @strROOM_WB = N'fd', @strROOM_HUMIDITY = N'f', @strROOM_ENTHALPY = N'f', @strROOM_LENGTH = N'ede', @strROOM_WIDTH = N'f', @strROOM_HEIGHT = N'd', @strROOM_AREA = N'dfd', @strDOORS = N'fdf', @strDOORSONE_SIZE = N'df', @strDOORSONE_AVGOPENING = N'fd', @strDOORSTWO_SIZEONE = N'fd', @strDOORSTWO_SIZETWO = N'fdf', @strDOORSTWO_AVGOPENING = N'fef', @strDOORSTHREE_SIZEONE = N'f', @strDOORSTHREE_SIZETWO = N'df', @strDOORSTHREE_SIZETHREE = N'df', @strDOORSTHREE_AVGOPENING = N'f', @strFIXEDOPENING = N'd', @strFIXEDOPENING_ONE_AREA = N'fd', @strFIXEDOPENING_ONE_DEPTH = N'f', @strFIXEDOPENING_TWO_AREA_ONE = N'df', @strFIXEDOPENING_TWO_DEPTH_ONE = N'ef', @strFIXEDOPENING_TWO_AREA_TWO = N'fd', @strFIXEDOPENING_TWO_DEPTH_TWO = N'f', @strNUM_OF_OCCUPANTS = N'ff', @strF5_FACTOR = N'd', @intF5_FACTOR_TYPE = 11, @strPRODUCT_LOAD_TYPE = N'd', @strIMC_FMC_QTY = N'f', @strIMC_FMC_INITIAL_MOISTURE = N'd', @strIMC_FMC_FINAL_MOISTURE = N'd', @strIMC_FMC_DRYING_TIME_DAY = N'f', @strIMC_FMC_DRYING_TIME_HOUR = N'sd', @strWEIGHT_WETWEIGHT = N'f', @strWEIGHT_DRYWEIGHT = N'd', @strWEIGHT_DRYING_TIME_DAY = N'fd', @strWEIGHT_DRYING_TIME_HOUR = N'f', @strMOISTUREREMOVED = N'd', @strMOISTUREREMOVED_DRYING_TIME_DAY = N'fd', @strMOISTUREREMOVED_DRYING_TIME_HOUR = N'f', @strBY_QTY = N'd', @strBY_PERCENTAGE = N'fd', @strBY_DRYING_TIME_DAY = N'd', @strBY_DRYING_TIME_HOUR = N'f', @strFRESH_AIR = N'f', @strPRECOOLSELECTION = N'df', @strMAKEUPAIR_REQUIREDCFM = N'fe', @strPRECOOL_DBT = N'f', @strPRECOOL_WBT = N'f', @strPRECOOL_ABSHUMIDITY = N'd', @strPRECOOL_ENTHALPHY = N'f', @strINFILTRRATION_LOAD = N'd', @strOTHER_LOAD = N'f', @strSENSIBLE_LOAD = N'f', @strSAFETY_FACTOR = N'dsdff', @strPERMIATION_LOAD = N'df', @strDOOR_LOAD = N'fd', @strFIXED_OPENING_LOAD = N'sdf', @strPEOPLE_LOAD = N'f', @strPRODUCT_LOAD = N'd', @strINFILTRATION_LOAD = N'fd', @strCALCULATED_OTHER_LOAD = N'f', @strSUBTOTAL = N'df', @strCALCULATED_SAFETY_FACTOR = N'd', @strINTERNAL_ROOM_LOAD = N'f', @strCFM_REQUIRED = N'f', @strFRESH_AIR_LOAD = N'df', @strTOTAL_LOAD = N'd', @strCFM_REQUIRED_TOTAL = N'fdff' SELECT 'Return Value' = @return_value GO

Suraj Kumar · Answer

Hello Swamy, You can enable SQL Server profiler and give us the output to check what exactly the error is coming. In this way you can also debug this procedure easily. As you have not given the sample values you are trying to insert with the procedure.

SWAMY YT · Answer

here the attachmnet has the schema of the table and store procedure

SWAMY YT · Answer

thanks for your quick response, 1 minute i wil send the store procedure and table structure

Code Alone · Answer

Hi Swamy, Will you share the query, And table structure. So that we can identify the issue...