c# - Invalid length parameter passed to LEFT or SUBSTRING function -

i got error mess "invalid length parameter passed left or substring function" below. can give me hint cause this, , how can solve it?

string cmdtext = @"declare  @sqlstring nvarchar(2500) , @areadelimiter char(1) , @areafilter nvarchar(max); select @areadelimiter = ','     if (@areaname not '*') begin     set @areaname = @areaname + @areadelimiter     set @areafilter = ''      while len(@areaname) > 0     begin         if (len(@areafilter) > 0)         begin             set @areafilter = @areafilter + ' or area ''' +                 ltrim(substring(@areaname, 1,                     charindex(@areadelimiter, @areaname) - 1)) + '%'''         end         else         begin             set @areafilter = 'area ''' +                 ltrim(substring(@areaname, 1,                     charindex(@areadelimiter, @areaname) - 1)) + '%'''         end          select @areaname = substring(@areaname, charindex(@areadelimiter,              @areaname) + 1, len(@areaname))     end end" 

this code above continues else statement , others operations works :)

and c# code:

string area = "area1,area2,area3";  using (sqlconnection conn = new sqlconnection(domain.getdecryptedsqlconnectionstring(domain.name + passphrase)))   {     using (sqlcommand cmd = new sqlcommand(cmdtext, conn))     {        system.globalization.cultureinfo cinfo = new system.globalization.cultureinfo("pt-br");       datetime dt = datetime.parse(startdate, cinfo);       datetime da = datetime.parse(enddate, cinfo);       cmd.parameters.add("interval", sqldbtype.int).value = interval;       cmd.parameters.add("intervalunit", sqldbtype.varchar).value = intervalunit;       cmd.parameters.add("startdate", sqldbtype.datetime).value =  dt.toshortdatestring();       cmd.parameters.add("enddate", sqldbtype.datetime).value = da.toshortdatestring();       cmd.parameters.add("tbname", sqldbtype.varchar).value = domain.alarmtablename;       cmd.parameters.add("areaname", sqldbtype.varchar).value = area;        using (sqldataadapter adapter = new sqldataadapter(cmd))       {         using (datatable tb = new datatable())         {           try           {             conn.open();             adapter.fill(tb);             return tb;           }           catch (exception ex)           {             return tb;           }                     {             conn.close();           }         }       }     }   } 

solved, change

cmd.parameters.add("areaname", sqldbtype.varchar).value = area; 

for

cmd.parameters.add("areaname", sqldbtype.varchar, -1).value = area; 

i can reproduce error with

declare @areaname varchar(20); declare @areadelimiter char(1); declare @areafilter varchar(100);  set @areaname='area1,area2,area3,area4,area5'; set @areadelimiter=','; 

notice declaration of @areaname not long enough data - check declaration.

also, if string @areaname has come user input, code vulnerable sql injection attacks.

edit:

all sql parameter names need start @, e.g. @areaname, , must match sqldbtype between c# code , sql code.

you shouldn't using

cmd.parameters.add("startdate", sqldbtype.datetime).value =  dt.toshortdatestring(); 

instead should be

cmd.parameters.add("startdate", sqldbtype.datetime).value =  dt; 

because dt datetime.