Error when data is bind into data table or gridview

Question

Conversion failed when converting the varchar value 'DADE' to data type int.

SQL command which i use is this:

SELECT * FROM p_details 
WHERE LOWER(name) LIKE '%has%ln%' 
AND  
  CODE = 13 
AND 
  T_CODE='H' 

My code is:

SqlDataReader drr = com4.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(drr); <--error line
GridView7.DataSource = dt;
GridView7.DataBind();

Answer 1

In a comment you state that all columns are varchar and you don't convert anything to int but your sql is WHERE CODE = 13.

So code is also a varchar. By comparing it with the int 13 you don't convert 13 to varchar but vice-versa, you are converting every value in code to an int.

Why? Because of the data type precedence rule:

• the data type with the lower precedence is converted to the data type with the higher precedence

int has a higher precedence so all varchar values are converted to int. I assume you have a value 'DADA' in that column which cannot be converted to int.

So the solution is simple, make it a varchar by using '13' instead of 13:

SELECT * FROM p_details 
WHERE LOWER(name) LIKE '%has%ln%' 
AND  
  CODE = '13' 
AND 
  T_CODE = 'H' 

Apart from that you should always use sql parameters to prevent sql-injection:

SELECT * FROM p_details 
WHERE LOWER(name) LIKE @lowname
AND  
  CODE = @code 
AND 
  T_CODE = @tCode

com4.Parameters.Add("@lowname", SqlDbType.VarChar).Value = "%" + name.ToLower() + "%");
com4.Parameters.Add("@code", SqlDbType.VarChar).Value = code;
com4.Parameters.Add("@tCode", SqlDbType.VarChar).Value = tCode;