how to use the keyword 'LIKE' in SQL query in C# code, what will be the sentax of C# statement

mavii

i am using ASP.net, C# anD SQL Server2005,in the following query: string q = "SELECT collapsed_building.b_name,collapsed_building.b_desc FROM collapsed_building WHERE collapsed_building.b_name LIKE '" + crimewithdate.text2 + "' "; I WANT TO USE % AFTER THE KEYWORD like SO THAT ALL THE BUILDING NAMES WHICH MATCH THE VALUE ENTERED BY THE USER ARE DISPLAYED WHEN I WRITE (LIKE '" + %crimewithdate.text2 %+ "'), IT GIVES ERROR, WHAT WILL BE THE CORRECT SENTAX

J4amieC

(LIKE '%" + crimewithdate.text2 + "%')

half life

string qQuery = "SELECT b_name,b_desc" + " FROM collapsed_building" + " WHERE b_name = '" + crimewithdate.text2 + "' "; maybe u should ty = it always work (just like an if) p.s : yuo do not need to write "collapsed_building." as a prefix.... :) :)

Have Fun Never forget it

Pete OHanlon

Please don't do this. You leave your code wide open to Sql Injection Attacks. Instead, consider using the techniques described here[^]. You'll end up a better developer for doing it.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Colin Angus Mackay

J4amieC wrote:

(LIKE '%" + crimewithdate.text2 + "%')

Don't encourage SQL Injection Attackable code, please.

Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog

Colin Angus Mackay

half-life wrote:

" WHERE b_name = '" + crimewithdate.text2 + "' ";

Please don't encourage SQL Injection Attackable code.

Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog

J4amieC

I didnt encourage it, I simply corrected his mistake. Frankly if the OP has this wrong then he's going to be bafffled by terms that he doesnt understand. Im 100% with you on the Sql Injection thing, but there is learning to walk before one can run.

Vasudevan Deepak Kumar

A few things: 1) Please spell-check the post before you submit. A continuous broken language embarrasses the readers. 2) A continuos all-caps also indicates yelling at the users. 3) Technically, your query is vulnerable for SQL Injection attacks. You may need to review your SQL Querying patterns. I would also advise you to have a read of Forum Posting Guidelines at http://www.codeproject.com/kb/scrapbook/forumguidelines.aspx [^]

Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson

Colin Angus Mackay

You should be using parameters to pass filterable stuff to the query. Also, there is absolutely no validation on the Text value in crimewithdate Where cmd is your SqlCommand object:

string value = string.Concat('%', crimewithdate.Text, '%');
cmd.CommandText = "SELECT b_name, b_desc "+
"FROM collapsed_building "+
"WHERE b_name LIKE @name";
cmd.Parameters.AddWithValue("@name", value);

Now, how big is your b_name column? Before you do anything with your query you should ensure that crimewithdate.Text does not exceed that size. Are there any other constraints? (e.g. only permitted to have alpha-numeric characters? Check for those also)

Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog

Colin Angus Mackay

J4amieC wrote:

there is learning to walk before one can run.

I teach a basic C# training course. I actually threw out the materials I'd been given on the subject of databases and rewrote it. I ensure that at no time is anyone encouraged to inject values and I go directly to parameterised queries. So far with good results. However, you might be right. If they already have the bad habit it might make it more difficult to break.

Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog

mavii

thnx alot it worked

J4amieC

Im glad it worked. Now please heed the warnings below about Sql Injection attacks. IT IS IMPORTANT!

half life

Colin Angus Mackay wrote:

Please don't encourage SQL Injection Attackable code.

Can u elaborate please? i decided not to take "Athics and Hacking" course and instead i took "advanced algorithms" in university :) :)

Have Fun Never forget it

Colin Angus Mackay

The OP supplied code that was (if it worked) susceptable to a SQL Injection Attack. Your "correction" is also susceptable to a SQL Injection Attack. For an article on what they are and how to prevent SQL Injection Attacks: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx[^]

Upcoming FREE developer events: * Developer Day Scotland Recent blog posts: * Follow up on hiring a software developer * The Value of Smaller Methods My website | blog

half life

THANKS :) vary infomative :)

Have Fun Never forget it

Le centriste

My 2 cents: you should never prepend with % or + in LIKE statement, it turns off indexes. It could hurt performance if you have lots of entries.

----- You seem eager to impose your preference of preventing others from imposing their preferences on others. -- Red Stateler, Master of Circular Reasoning and other fallacies If atheism is a religion, then not collecting stamps is a hobby. -- Unknown God is the only being who, to rule, does not need to exist. -- Charles Baudelaire