Click here to Skip to main content
15,881,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Error:- "Conversion failed when converting the varchar value '5,' to data type int"



My Store-P

SQL
ALTER proc [dbo].[procCheckDataStatus]  @MasterTableName varchar(200),@ColValue varchar(20),@WorkFlowID int,@ModuleID int,@ScriptID int,@UserID int,@ipaddress varchar(100),@Result int output
 as
DECLARE @TableName NVarchar(150), @TableColName NVarchar(150)
DECLARE @ValueExist int
DECLARE @SQLString NVarchar(max)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @CountSQLQuery varchar(30);
 set @Result = 0;
SET @ParmDefinition = N'@result varchar(30) OUTPUT';


DECLARE MyCursor CURSOR static FOR

select b.TableName,a.TableColName,c.TableName MasterTable
from dbo.SystemTableFeldMapping a,systemtables b,systemtables c where a.tableid=b.id and c.Id = a.ParentTableID
and c.TableName = @MasterTableName  and c.Activestatus = 1
OPEN MyCursor FETCH NEXT FROM MyCursor
into @TableName,@TableColName,@MasterTableName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
   set  @SQLString ='select @result =count(*)  from ' +@TableName+' where '+@TableColName+ '='+@ColValue;
   EXECUTE sp_executesql  @SQLString,@ParmDefinition, @result=@CountSQLQuery OUTPUT;

   if (convert(int,@CountSQLQuery)>0)
   begin
       insert into SystemLogs (WorkFlowID,ModuleID,ScriptID,LogDesc,UserID,IpAddress,Systemdate)
       values (@WorkFlowID,@ModuleID,@ScriptID,'Value Found in Child //  Child Table ='+@TableName+' // Key Field='+@TableColName+' // Master Table='+@MasterTableName  ,@UserID,@ipaddress,GETDATE())
       set @Result = @Result + 1;
   end

   FETCH NEXT FROM MyCursor INTO @TableName,@TableColName,@MasterTableName
END

CLOSE MyCursor;
DEALLOCATE MyCursor;

--return @Result



And My Code Here :-

C#
public static Int32 GetDeleteStatus(string TableName, Int32 ColValue, Int32? WorkFlowID = 0, Int32? ScriptID = 0, Int32? ModuleID = 0, Int32 UserID = 0, string ipaddress = "")
        {
            IEConnection connection = ConnectionFactory.GetIEConnection();
            DatabaseObject objDataNew = new DatabaseObject();
            EParameter[] param1 = new EParameter[8];
            param1[0] = new EParameter("MasterTableName", TableName);
            param1[1] = new EParameter("ColValue", ColValue);
            param1[2] = new EParameter("WorkFlowID", WorkFlowID);
            param1[3] = new EParameter("ScriptID", ScriptID);
            param1[4] = new EParameter("ModuleID", ModuleID);
            param1[5] = new EParameter("UserID", UserID);
            param1[6] = new EParameter("ipaddress", ipaddress);
            Int32 Result = 0;
            param1[7] = new EParameter("Result", "");
            param1[7].Direction = EParameterDirection.Output;
            connection.UseDBObject = EUseDBObject.StoredProcedure;
            connection.StoredProcedure = "procCheckDataStatus";
            connection.Parameters = param1;
            bool value = Convert.ToBoolean(connection.ExecuteNonQuery());// <b>Error Here </b>
            if (Convert.ToInt32(param1[7].ReturnValue) > 0)
            {
                Result = Convert.ToInt32(param1[7].ReturnValue);
            }
            return Result;
        }
Posted
Updated 13-Nov-14 19:06pm
v5
Comments
PIEBALDconsult 14-Nov-14 0:10am    
Well, there's a comma in there.

I'm also hoping you'll be able to eliminate that cursor and dynamic SQL.
Nishant.Chauhan80 14-Nov-14 0:15am    
where sir.. plz tell me
PIEBALDconsult 14-Nov-14 0:27am    
It's right in your title. I don't know how it go there, but it's there.

Shouldn't @CountSQLQuery be delared as integer anyway? To avoid converting in the first place?
Nishant.Chauhan80 14-Nov-14 0:37am    
sir look my code with store-p again i hope you understand store-p
PIEBALDconsult 14-Nov-14 0:44am    
connection.ExecuteNonQuery())

I expect that returns an integer; not a boolean. And that doesn't appear to match the title of your question; the problem is in the SQL code, not in the C# code.
You might need to tell us what the ConnectionFactory and its classes are. I've never heard of them.

1 solution

change
SQL
DECLARE @CountSQLQuery varchar(30);
to
SQL
DECLARE @CountSQLQuery int;  
and change
SQL
if (convert(int,@CountSQLQuery)>0)
to
SQL
if (@CountSQLQuery>0)
Quote:
bool value = Convert.ToBoolean(connection.ExecuteNonQuery());// Error Here

ExecuteNonQuery returns the number of rows affected, but why you trying to convert it for boolean value? if you want to check whether any row affected, do as below
C#
bool value = connection.ExecuteNonQuery() > 0;
 
Share this answer
 
v3
Comments
Nishant.Chauhan80 14-Nov-14 0:24am    
Again Error sir after Change this solution
PIEBALDconsult 14-Nov-14 0:45am    
That's only part of the solution; there's still something else wrong in the SQL, or are you saying the SQL is fixed now?
DamithSL 14-Nov-14 1:02am    
what is the new error?
Nishant.Chauhan80 14-Nov-14 1:20am    
again same error..
PIEBALDconsult 14-Nov-14 1:24am    
Same _exact_ error? Or just very similar?
And you made the suggested changes to the SQL?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900