Select * from table like..%... with And

Question

1.00/5 (2 votes)

See more:

hi all,

i have two field in table city,country.
i have two textboxes in form.
i want to use all textboxes as search like...

if i write

city : new Country : unite

or
city : york Country : uni

in textbox and then click on search so result should be...

new york, united state.

I tried...

VB

ss = "select * from Master where city like'%" + txtCity.Text + "%' and country like'%" + txtCountry.Text + "%'"

Posted 29-Jul-14 1:36am

Bhatt Aniruddh

Updated 29-Jul-14 18:07pm

v2

Add a Solution

Comments

OriginalGriff 29-Jul-14 7:54am

And? What happened?

[no name] 29-Jul-14 8:14am

not search proper, it's display only 2 data but there are more then 2

OriginalGriff 29-Jul-14 8:20am

Which ones did it show, and which did it ignore?
BTW: You shouldn't do it like that - you leave yourself wide open to SQL Injection.

[no name] 29-Jul-14 8:28am

i have seven data with new york city and united state country.
it shows only 4 data.

OriginalGriff 29-Jul-14 8:36am

No, that doesn't help.
Show me the actual rows complete with data and indicate which it finds.
Remember, I can't see your screen! :laugh:

[no name] 29-Jul-14 8:53am

Nirav Prabtani's code works

Nirav Prabtani 29-Jul-14 9:07am

Thanks.. :)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-07-29T02:31:00

Use parameterized query to prevent sql injection and it will also save you a lot of syntax mess:

ss = "select * from Master where city like @city and country like @country"; 
cmd.Parameters.AddWithValue("@city", "%" + txtCity.Text +"%");
cmd.Parameters.AddWithValue("@country", "%" + txtCountry.Text +"%");

Solution 1

try this.. :)

C#

"select * from Master where city like '%" + txtCity.Text + "%' and country like '%" + txtCountry.Text + "%'";

Posted 29-Jul-14 1:54am

Nirav Prabtani

Updated 30-Jul-14 0:33am

v2

Comments

OriginalGriff 29-Jul-14 8:08am

Reason for my vote of one: your "solution" is the same (baring a few irrelevant spaces) as the sample text the OP provided.

Not a solution.

[no name] 29-Jul-14 8:51am

it's works

Nirav Prabtani 29-Jul-14 8:56am

Than why don't you mark as solved??

Nirav Prabtani 29-Jul-14 8:57am

nopp i think you should look into it.. :)

CHill60 30-Jul-14 6:25am

Reason for my vote of 3 - it works but you would have been better off explaining to the OP that they needed that space after each "like". You might have avoided the downvotes as well!
I also can't see the point of the extra string concatenation when "select * from Master where city like '%" + txtCity.Text + "%' and country like '%" + txtCountry.Text + "%'"; works just as well.

Nirav Prabtani 30-Jul-14 6:34am

yes , you are right ,i have updated it.

CHill60 30-Jul-14 6:58am

I'm still intrigued though - OP says it works, but the spaces don't make any difference in SQL

Bhatt Aniruddh · Accepted Answer · 2014-07-29T18:07:00

VB

ss = "select * from Donator where sname like @sname and city like @city and atpo like @atpo and taluka like @taluka and dist like @dist and pin like @pin" & _
     " and letter like @letter and notes like @notes and other like @other" ' and email like @email"
com = New OleDbCommand(ss, con)
com.Parameters.AddWithValue("@sname", "%" + TextBox12.Text + "%")
com.Parameters.AddWithValue("@city", "%" + TextBox13.Text + "%")
com.Parameters.AddWithValue("@atpo", "%" + TextBox14.Text + "%")
com.Parameters.AddWithValue("@taluka", "%" + TextBox15.Text + "%")
com.Parameters.AddWithValue("@dist", "%" + TextBox16.Text + "%")
com.Parameters.AddWithValue("@pin", "%" + TextBox17.Text + "%")
com.Parameters.AddWithValue("@letter", "%" + TextBox19.Text + "%")
com.Parameters.AddWithValue("@notes", "%" + TextBox20.Text + "%")
com.Parameters.AddWithValue("@other", "%" + TextBox21.Text + "%")

con.Open()
dr = com.ExecuteReader()
While dr.Read
    x = DataGridView1.Rows.Add(+1)

    DataGridView1.Rows(x).Cells(0).Value = dr(0)
    DataGridView1.Rows(x).Cells(1).Value = dr(2)
    DataGridView1.Rows(x).Cells(2).Value = dr(1)
    DataGridView1.Rows(x).Cells(3).Value = dr(3)
    address = dr(4) + ", " + dr(5) + ", " + dr(6) + ", " + dr(7) + ", " + dr(25) + ", " + dr(8) + ", " + dr(9) + ", " + dr(10)
    DataGridView1.Rows(x).Cells(4).Value = address

    DataGridView1.Rows(x).Cells(5).Value = dr(11)

    address1 = dr(12) + ", " + dr(13) + ", " + dr(14) + ", " + dr(15) + ", " + dr(26) + ", " + dr(16) + ", " + dr(17) + ", " + dr(18)

    DataGridView1.Rows(x).Cells(6).Value = address1

    DataGridView1.Rows(x).Cells(7).Value = dr(19)
    DataGridView1.Rows(x).Cells(8).Value = dr(20)
    DataGridView1.Rows(x).Cells(9).Value = dr(21)
    DataGridView1.Rows(x).Cells(10).Value = dr(22)
    DataGridView1.Rows(x).Cells(11).Value = dr(23)
End While
con.Close()

Select * from table like..%... with And

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0