problem with sql query

Question

I have this code to query the database:

for (int kk = 1; kk < search.Length; kk++)
{
    where +=  " and keyword like '%"+search[kk]+";%'";
    OleDbCommand sqlcmd = new OleDbCommand(
      "select id,name,address,keyword from table1 where  keyword like '%"+search[0]+"%' " + 
      where + "  order by name", sqlconConnection);
    sqlcmd.CommandType = CommandType.Text;
    OleDbDataReader sdaRes = sqlcmd0.ExecuteReader();
    while (sdaRes.Read())
    {
        thumbnails_id[recordcount] = sdaRes.GetInt32(3);
        recordcount++;
    }

    sdaResult0.Close();

}

When i execute this query in access it return the result but when i run it in the application it run but does not show any result.

the structure of the table is:

The DB table look like this:

id   name   keyword  File  Fkey
1     a      yellow;  c:/   20
2     a      blue;    c:/   20
3     a      Pinky;   c:/   20
4     b      blue;  c:/   21
5     b      Redish;  c:/   21
6     c      yellow;  c:/   22
7     c      blue;    c:/   22
8     c      Pinky;   c:/   22
9     c      orange;  c:/   22
10    c      Redish;  c:/   22

what the search query is meant to be like this: select * from this table1 where name like variable and variable two and variable three and so on. So when user type suppose orange all result should come then when user type in after orange Pinky then orange and pinky result should comes on. but I do not know what is wrong in the code although i get no error or no Warning message.

Please, please, please read up on Sql Query Injection and Parameterized Queries, especially if this is going to be exposed on the internet. — Talljoe
– Talljoe, Commented Apr 15, 2011 at 11:20

Cristian Boariu · Accepted Answer · 2011-04-15 11:20:44Z

3

You need OR instead of AND because keyword cannot be at the same time 'red' and 'blue' and etc...

answered Apr 15, 2011 at 11:20

Cristian Boariu

9,62714 gold badges94 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Martin Smith Over a year ago

Also needs GROUP BY, HAVING and COUNT I think (if they are trying to bring back names that have both red and blue rows)

safi Over a year ago

@Christian Boariu, this and work fine in the access but in code do not work and if i put OR then i will get a 1000 of records. so i need and to shrink the result with the keyword.

Cristian Boariu Over a year ago

@Safi Can you put the query you run in access? I have doubts that it runs fine with AND

Martin Smith Over a year ago

@safi - I may have guessed wrong but think you might need something like

SELECT name, File, FKey FROM table1 WHERE  keyword like '%yellow%' or  keyword like '%blue%'  GROUP BY name, File, FKey HAVING COUNT(DISTINCT keyword)=2

safi Over a year ago

@Christian Boariu in access it look like this: select * from table1 where keyword like 'red;' And 'blue;' and the result is what i want. means run perfect.

|

BrokenGlass · Accepted Answer · 2011-04-15 11:26:06Z

0

while (sdaRes.Read())
{
    thumbnails_id[recordcount] = sdaResult0.GetInt32(3);
    recordcount++;
}

Either this is a typo or you are not really reading the results from the reader you are using from your query, also id is the first column, so imo it should be:

while (sdaRes.Read())
{
    thumbnails_id[recordcount] = sdaRes.GetInt32(0);
    recordcount++;
}

answered Apr 15, 2011 at 11:26

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

4 Comments

safi Over a year ago

I am reading the result from the reader you can see in the code,you mean to say i should also include this id i.e First COlumn of the table?

BrokenGlass Over a year ago

@safi: where is sdaResult0 defined in your code? Either your code samples is incomplete or wrong

safi Over a year ago

@BorkenGlass I just mistype the name from sdaRes to sdaResult0

Andriy M Over a year ago

@safi: You've mistyped it in another place (sdaResult0.Close();) as well as you've probably mistyped this: sqlcmd0.ExecuteReader(); (shouldn't it be sqlcmd.ExecuteReader();?).

Collectives™ on Stack Overflow

problem with sql query

2 Answers 2

11 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related