To search the data from database using asp.net(C#)

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 48334 Views - Last Post: 18 March 2011 - 07:06 PM Rate Topic: ****- 1 Votes

#1 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

To search the data from database using asp.net(C#)

Posted 11 March 2011 - 12:49 AM

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 ??

Is This A Good Question/Topic? 0
  • +

Replies To: To search the data from database using asp.net(C#)

#2 Ninjato  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 6
  • View blog
  • Posts: 29
  • Joined: 24-February 11

Re: To search the data from database using asp.net(C#)

Posted 11 March 2011 - 04:01 AM

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



This you can use as your Command Text
Was This Post Helpful? 0
  • +
  • -

#3 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: To search the data from database using asp.net(C#)

Posted 11 March 2011 - 05:03 AM

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


Was This Post Helpful? 1
  • +
  • -

#4 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

Re: To search the data from database using asp.net(C#)

Posted 13 March 2011 - 11:55 PM

View PostNinjato, on 11 March 2011 - 04:01 AM, said:

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



This you can use as your Command Text

Was This Post Helpful? 0
  • +
  • -

#5 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 12:03 AM

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!!!
Was This Post Helpful? 0
  • +
  • -

#6 Ninjato  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 6
  • View blog
  • Posts: 29
  • Joined: 24-February 11

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 12:16 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 01:12 AM

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();
Was This Post Helpful? 0
  • +
  • -

#8 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 03:14 PM

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.
Was This Post Helpful? 0
  • +
  • -

#9 Ninjato  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 6
  • View blog
  • Posts: 29
  • Joined: 24-February 11

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 10:23 PM

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.
Was This Post Helpful? 0
  • +
  • -

#10 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

Re: To search the data from database using asp.net(C#)

Posted 14 March 2011 - 10:57 PM

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??

View PostNakor, 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

Was This Post Helpful? 0
  • +
  • -

#11 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: To search the data from database using asp.net(C#)

Posted 15 March 2011 - 04:42 AM

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

Was This Post Helpful? 1
  • +
  • -

#12 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: To search the data from database using asp.net(C#)

Posted 15 March 2011 - 12:29 PM

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).
Was This Post Helpful? 1
  • +
  • -

#13 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: To search the data from database using asp.net(C#)

Posted 15 March 2011 - 02:27 PM

View PostkeakTheGEEK, 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 :P (Haven't even started looking at the patindex thing yet)

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

Was This Post Helpful? 1
  • +
  • -

#14 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: To search the data from database using asp.net(C#)

Posted 15 March 2011 - 03:13 PM

View PostNakor, on 15 March 2011 - 02:27 PM, said:

View PostkeakTheGEEK, 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 :P (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

Was This Post Helpful? 0
  • +
  • -

#15 pratyushakandala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 23-March 10

Re: To search the data from database using asp.net(C#)

Posted 18 March 2011 - 12:21 AM

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!!!
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2