CODEWITHSUNDEEP
c#asp.netsqlsql-server
user1181942
user1181942

Reputation: 1607

Error converting data type varchar to bigint

I am retrieving values from listbox and formatting them in string as 

if (lbRaisedByLogin.SelectedIndex != -1)
    {
        foreach (ListItem item in lbRaisedByLogin.Items)
        {
            if (item.Selected == true)
            {
                sb.AppendFormat("{0},", item.Value);
            }
        }

        searchCase.raisedByLogin = sb.ToString().TrimEnd(Convert.ToChar(","));
        sb.Length = 0;
    }

I am passing these strings to store procedure as parameter (Datatype-- Varchar(Max))

in stored procedure I am comparing these values like

SELECT * FROM AUS_CID_Cases
WHERE
AddedBy IN ( @raisedByLogin )

Where @raisedByLogin is one of the parameter i passed. It has values like @raisedByLogin="4,2,1"

AddedBy has datatype bigint.

When I run code I get error as "Error converting data type varchar to bigint.".. I understand it is because AddedBy column has datatype bigint. I can not change that datatype.

However when i cast AddedBy like

SELECT * FROM AUS_CID_Cases
WHERE
CAST(AddedBy as VARCHAR) IN ( @raisedByLogin )

I dont get error, but nothing is selected i.e I dont get any result from select statement.

What can I do?

Upvotes: 1

Views: 3460

Answers (2)

Nalaka526
Nalaka526

Reputation: 11474

First create this view

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return

then change the query to this and try,

SELECT * FROM AUS_CID_Cases
WHERE
CAST(AddedBy as VARCHAR) IN ( select * from split(@raisedByLogin,',') )

found above Function here, and managed to solve this issue sometime back...

Upvotes: 1

Damith
Damith

Reputation: 63105

try to build SQL select query inside stored procedure and execute after that. 

DECLARE @raisedByLogin varchar(600)

SET @SQL = 
'SELECT * FROM AUS_CID_Cases
 WHERE
  AddedBy IN (' + @raisedByLogin + ')' 

EXEC(@SQL)

check How to pass a list of values or array to SQL Server stored procedure? for more information

Upvotes: 0

Related Questions