CODEWITHSUNDEEP
c#sqlasp.netsql-servergridview
krishnakumar
krishnakumar

Reputation: 94

Error when data is bind into data table or gridview

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();

Upvotes: 5

Views: 649

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460028

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;

Upvotes: 4

Related Questions