sql server store procedure issue

SWAMY YT

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

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted 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 Kumar8y ago
Thanks and happy to know that you have resolved your query.
SWAMY YT8y ago
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 YT8y ago
No sir, Still I am facing the same issue.
Suraj Kumar8y ago
Hi Swamy,

Have you resolved your query posted here..

Regard
Suraj
Suraj Kumar8y ago
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 YT8y ago
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 Kumar8y ago
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 YT8y ago
here the attachmnet has the schema of the table and store procedure
SWAMY YT8y ago
thanks for your quick response, 1 minute i wil send the store procedure and table structure
Code Alone8y ago
Hi Swamy,

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