Well, yes. It would be:
len = Convert.ToInt32(textBox1.Text.Length);
string str = "select SUBSTRING(F_Name,1,len) from Opd_Detail where F_name like'" + textBox1.Text + "'";
len
in your SQL statement is part of a string, so the characters 'l', 'e', and 'n' are passed to SQL, not the value of the
len
variable in the line above.
There are two ways to fix it:
1) Add the value instead of the name:
len = Convert.ToInt32(textBox1.Text.Length);
string str = "select SUBSTRING(F_Name,1," + len + ") from Opd_Detail where F_name like'" + textBox1.Text + "'";
But that's a bad idea - it's inefficient, and very dangerous.
2) Add it as a parameter to your SQL query - and at the same time protect your database! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:
string str = "SELECT SUBSTRING(F_Name,1,@LEN) FROM Opd_Detail FROM F_name like '%' + @COMP + '%'";
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(str, Con_Class.con);
sda.SelectCommand.Parameters.AddWithValue("@LEN", textBox1.Text.Length);
sda.SelectCommand.Parameters.AddWithValue("@COMP", textBox1.Text);
You do realize that LIKE is a wild card match that only does anything useful if you include wildcards? (Which for SQL is a percent character)
[edit]Typos.[/edit]