[pratyushakandala]

i need coding help:
select * from tablename where columnname like '%somevalue%'
The o/p what we get when executed in sql database is exactly that i need using textbox from asp.net page

here is my code

SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=PEmployeeData;Integrated Security=True");
//string text,x;
//text = TextBox1.Text;
//x = text;

// Label1.Text = x;
SqlCommand cmd = new SqlCommand("select * from Pempdata where skillset = '" + TextBox1.Text + "' ", con);

con.Open();

cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Pempdata");
GridView1.DataSourceID = null;
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
}

i'm not able use like or in keywords to get desired o/p

how to use "like" statement in above code using textbox.text ??

[Ninjato]

select * from Pempdata where skillset like '%" + TextBox1.Text + "%'

This you can use as your Command Text

[Nakor]

First, don't put your TextBox.Text directly into your command like that. This opens you up for possible SQL injection attacks. You need to be using SQL parameters. Change your sql command to look something like

string query = "SELECT * FROM Pempdata where skillset LIKE '%@input'";

SqlCommand command = new SqlCommand(query, con);

// Create the parameter, "input" being the name of the variable in your sql statement, 
// value being the value to give the parameter 
command.Parameters.AddWithValue("input", value);

[pratyushakandala]

Ninjato, on 11 March 2011 - 04:01 AM, said:

select * from Pempdata where skillset like '%" + TextBox1.Text + "%'

This you can use as your Command Text

[pratyushakandala]

Thanks a lot!! It is working!!!

But i've another problem,

sql database skillset = java,c,cpp,.net
If the data stored in column skillset is java,c,cpp,.net

if i execute java,c means i'm getting the data who have both
the problem is if i give c,java (reverse order)... it is not working
even if i execute at the sql query


How to solve ?? what query should be written!!!
pls help me!!!

[Ninjato]

Can you give me the code, I should have a look.


And one more thing : ---
Answer to your previous question that I gave might helped you but it was worth security risk, What risk ??? SQL Injection. Yeah! the answer

Nakor said:

First, don't put your TextBox.Text directly into your command like that. This opens you up for possible SQL injection attacks.

that Nakor gave you is deserving. Know them to learn.

[pratyushakandala]

SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=PEmployeeData;Integrated Security=True");

SqlCommand cmd = new SqlCommand("select * from Pempdata where skillset like '%" + TextBox1.Text + "%' ",con);
//string val = TextBox1.Text.ToString();
con.Open();

cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Pempdata");
GridView1.DataSourceID = null;
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();

[Nakor]

The thing with the like statement is that it is looking for a substring of the string contained in the database. Therefore if the value in the database is "java,c,c++,.net" then it would match on "java,c" because that exists within the string where "c,java" does not.

[Ninjato]

You can use Full Text Searching in SQL Server if you want to have a free text search, cause as per your requirement the like statement always searches for substring of string.

[pratyushakandala]

Can i use Dropdownlist box instead of textbox...??

Then how to add values in dropdownlist at run time and that should be stored in database!!!

Can u help me in coding... How should i write??

Nakor, on 11 March 2011 - 05:03 AM, said:

First, don't put your TextBox.Text directly into your command like that. This opens you up for possible SQL injection attacks. You need to be using SQL parameters. Change your sql command to look something like

string query = "SELECT * FROM Pempdata where skillset LIKE '%@input'";

SqlCommand command = new SqlCommand(query, con);

// Create the parameter, "input" being the name of the variable in your sql statement, 
// value being the value to give the parameter 
command.Parameters.AddWithValue("input", value);

This post has been edited by pratyushakandala: 14 March 2011 - 10:57 PM

[Nakor]

There's a number of ways you could handle this. You could keep the textbox, split the input and and check the items individually rather than altogether. You would need to make sure that the users always use the same character to separate the items, like a comma or forward slash or a space, whatever. Here's one way you might build the query string.

            // if input is java,c,c++
            string query = "SELECT * FROM string query = "SELECT * FROM Pempdata";

            // Make sure there is at least 1 item in the array
            if (splitItems.Length > 0)
            {
                query += " where skillset LIKE '%' + @input + '%'";

                cmd.Parameters.AddWithValue("input", splitItems[0]);

                // Start at one since we've already used the item at index 0
                for (int index = 1; index < splitItems.Length; index++)
                {
                     string param = "input" + index.ToString();
                     // You can play with AND or OR here to see which
                     // gives the results closest to what you want
                     query += " AND skillset LIKE '%' + @" + param + " + '%'";
                     cmd.Parameters.AddWithValue(param, splitItems[index]);
                }
            }";

You could also use a dropdownlist or listbox and limit the user input. This gives you greater control over what values are being sent to the database to be searched for. For the DropDownList you could set the AutoPostBack property to true and then use the onselectedIndexChanged event to get the value of the selected item and update the gridview. If you use a ListBox you could include a Button and update the gridview with the selected item or items on the Click event.

This post has been edited by Nakor: 15 March 2011 - 04:50 AM

[keakTheGEEK]

If this is MS SQL Server, there are actually a few more options available. To have a result set that contains "java,c,c++,.net" be returned when searching on either "c,java" or "java,c", you can use the Pattern Matching in Search Conditions. So, if you were to write your query like this:

SELECT *
FROM Pempdata
WHERE skillset LIKE '%[c,java]%'

This would return records in you table that have a string similar or equal to "java,c,c++,.net".

There is also the PATINDEX (Transact-SQL) function. If you are working with MS SQL Server 2005 or higher, then you can also create CLR User-Defined Functions or CLR Stored Procedures which allow you the ability to leverage the .net framework in your db objects (hint: System.Text.RegularExpressions).

[Nakor]

keakTheGEEK, on 15 March 2011 - 02:29 PM, said:

If this is MS SQL Server, there are actually a few more options available. To have a result set that contains "java,c,c++,.net" be returned when searching on either "c,java" or "java,c", you can use the Pattern Matching in Search Conditions. So, if you were to write your query like this:

SELECT *
FROM Pempdata
WHERE skillset LIKE '%[c,java]%'

This would return records in you table that have a string similar or equal to "java,c,c++,.net".

There is also the PATINDEX (Transact-SQL) function. If you are working with MS SQL Server 2005 or higher, then you can also create CLR User-Defined Functions or CLR Stored Procedures which allow you the ability to leverage the .net framework in your db objects (hint: System.Text.RegularExpressions).

That's pretty cool, I hadn't been shown that way of searching for results before. However from what I can tell after reading up on it and testing it out a little it seems the only problem with that in this situation is that '%[c,java]%' searches for each individual character (c,j,a,v,a) rather than the string values of "c" and "java" which may pull back more data than you're actually wanting. Unless I'm wrong, which could be the case since I've only been looking at it for about 5 minutes (Haven't even started looking at the patindex thing yet)

This post has been edited by Nakor: 15 March 2011 - 02:33 PM

[keakTheGEEK]

Nakor, on 15 March 2011 - 02:27 PM, said:

keakTheGEEK, on 15 March 2011 - 02:29 PM, said:

If this is MS SQL Server, there are actually a few more options available. To have a result set that contains "java,c,c++,.net" be returned when searching on either "c,java" or "java,c", you can use the Pattern Matching in Search Conditions. So, if you were to write your query like this:

SELECT *
FROM Pempdata
WHERE skillset LIKE '%[c,java]%'

This would return records in you table that have a string similar or equal to "java,c,c++,.net".

There is also the PATINDEX (Transact-SQL) function. If you are working with MS SQL Server 2005 or higher, then you can also create CLR User-Defined Functions or CLR Stored Procedures which allow you the ability to leverage the .net framework in your db objects (hint: System.Text.RegularExpressions).

That's pretty cool, I hadn't been shown that way of searching for results before. However from what I can tell after reading up on it and testing it out a little it seems the only problem with that in this situation is that '%[c,java]%' searches for each individual character (c,j,a,v,a) rather than the string values of "c" and "java" which may pull back more data than you're actually wanting. Unless I'm wrong, which could be the case since I've only been looking at it for about 5 minutes (Haven't even started looking at the patindex thing yet)

@Nakor,

You are right, good catch. If the skillsets are delimited by "," character, then '%[c,Java]%' would end up returning all records that have a comma in it. Stripping out the comma before the search would yield better results, but still would potentially return more records than desired. Example '%[cjava]%' world return records where skillset contains "Java,C,C++,Scala,etc..."

Pattern searching gives you limited regular expression like searching capabilities, but not quite up to par with pure regular expressions.

Using CLR udf/sp would be much better in this case because you could use .NET RegularExpressions...

Sounds like the OP has quite a few options presented to them to choose from...

This post has been edited by keakTheGEEK: 15 March 2011 - 03:16 PM

[pratyushakandala]

splititems is not available with the visual studio 2010 or 2008... I'm not able to split it as strings!!

Thanks a to all who helped me a lot!!!