Can Anyone Fix this search Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can Anyone Fix this search Query

Author

Topic

cloud 9
Starting Member

6 Posts

Posted - 2006-08-03 : 23:51:24

hi guys i am stuck with little bug while executing search query . Please help out .

ISSUE : while searching from db with more then one skills cause no results.

DB : [url]http://www.cidapsu.org.pk/db_diagram.jpg[/url] ( please have look on DB Diagram )

Search form : [url]http://www.cidapsu.org.pk/search_Form.jpg[/url] (Please have a look on Search form )

the code i am using : this is working fine if i search with only one skill check box selected .

/// importing selected skills IDs from search form

List<string> strSkillList2 = new List<string>();
strSkillList2 = (List<string>)Session["skillList"];

////

SqlConnection mycon = new SqlConnection();
mycon.ConnectionString = "Data Source=localhost;Initial Catalog=cvbank;Integrated Security=True";
mycon.Open();
SqlCommand mycommand = new SqlCommand();
string strQuery = null;
bool bWhere = false;
strQuery = "select * from consultants c";

if (Request["firstName"] != "")
{
if (!bWhere)
{
bWhere = true;
strQuery += " where ";
}
else
{
strQuery += " and ";
}
strQuery = strQuery + "c.First_Name LIKE '" + "%" + Request["firstName"] + "%" + "'"; }

if (strSkillList2.Count > 0)
{
if (!bWhere)
{
bWhere = true;
strQuery += " where ";
}
else
{
strQuery += " and ";
}

// i think something going wrong here

strQuery += " c.id IN (Select b.id from skill_cv_relation a, consultants b where a.CV_ID = b.ID ";

foreach (string str in strSkillList2)
{
strQuery += " and a.skill_ID = " + str;

}
strQuery += ")";
}

mycommand.CommandText = strQuery;
mycommand.Connection = mycon;
SqlDataReader myreader = mycommand.ExecuteReader();
List<Consultant> conList = new List<Consultant>();
int totalRow = 1;
while (myreader.Read())
{
Consultant mycons = new Consultant();
mycons.ID = Convert.ToInt16(myreader["ID"]);
conList.Add(mycons);
totalRow++;
}
myreader.Close();
mycon.Close();

//Load skills
LoadSkills( conList);

Repeater1.DataSource = conList;
Repeater1.DataBind();

}

private void LoadSkills(List<Consultant> myList)
{
foreach (Consultant cons in myList)
{
SqlConnection mycon = new SqlConnection();
mycon.ConnectionString = "Data Source=localhost;Initial Catalog=cvbank;Integrated Security=True";
mycon.Open();
SqlCommand mycommand = new SqlCommand();

mycommand.CommandText = "select * from skillname, skill_cv_relation where skillname.id = skill_cv_relation.skill_id and cv_id = " + cons.ID;

mycommand.Connection = mycon;
SqlDataReader myreader = mycommand.ExecuteReader();

while (myreader.Read())
{
Skill mySkill = new Skill();
mySkill.id = Convert.ToInt16(myreader["ID"]);
mySkill.SkillName = Convert.ToString(myreader["skillname"]);
cons.Skills.Add(mySkill);
}
mycon.Close();

}
}

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-04 : 00:25:18

can you paste a copy of the strquery variable output after
************
strQuery += " c.id IN (Select b.id from skill_cv_relation a, consultants b where a.CV_ID = b.ID ";

foreach (string str in strSkillList2)
{
strQuery += " and a.skill_ID = " + str;

}
strQuery += ")";
}

**********

it will be much easier to diagnose the issue if I can see what you are actually sending to the db server.

cloud 9
Starting Member

6 Posts

Posted - 2006-08-04 : 00:38:41

strQuery =

"select * from consultants c where c.id IN (Select b.id from skill_cv_relation a, consultants b where a.CV_ID = b.ID and a.skill_ID = 1 and a.skill_ID = 2"

That is the query when slected more 2 skills ids .. and it wont show any results ..

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-04 : 00:40:22

did you copy the whole thing? that query does not have the ")" to close out your select clause.

cloud 9
Starting Member

6 Posts

Posted - 2006-08-04 : 00:45:38

quote:
Originally posted by cloud 9

strQuery =

"select * from consultants c where c.id IN (Select b.id from skill_cv_relation a, consultants b where a.CV_ID = b.ID and a.skill_ID = 1 and a.skill_ID = 2"

That is the query when slected more 2 skills ids .. and it wont show any results ..

if i remove second skill id which is -- > " and a.skill_ID = 2 " this query work fine ..

cloud 9
Starting Member

6 Posts

Posted - 2006-08-04 : 00:51:18

[url]http://www.cidapsu.org.pk/query.jpg[/url]

that wht it shows .. and i think it missing ")" and i dont know why :(

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-04 : 00:56:28

There's a few minor issues;
Use the below query

select * from consultants c where c.id IN (Select b.id from skill_cv_relation a inner join consultants b on a.CV_ID = b.ID and a.skill_ID in (1,2))

Just change your code to
strQuery += " c.id IN (Select b.id from skill_cv_relation a inner join consultants b on a.CV_ID = b.ID ";
string str;
int count = 1;
if (!(strSkillList2 == string.empty)) {
strQuery += " and a.skill_ID in (";
foreach (int str in strSkillList2) {
if (count == 1) {
strQuery += str;
count += 1;
} else {
strQuery += "," + str;
}
}
strQuery += ")";
}

//I didn't test the code, so make sure the syntax is correct.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-04 : 01:02:02

The last post was just updated, make sure you are using the above syntax.

cloud 9
Starting Member

6 Posts

Posted - 2006-08-04 : 02:03:18

hey thanks .. i think this will work but .. again stuck with this syntex issue

if(! strSkillList2.Equals ("")){
strQuery += " and a.skill_ID in (";
foreach (string str in strSkillList2)
{
strQuery += "," + str ;
}
strQuery += ")";
}
strQuery += ")";

//////////////////// it suppose to work if choose 1 or 2 options .. if i remove "," it work fine with single option but not working with more then one ..
thanks in advance .. for littel more effort

cloud 9
Starting Member

6 Posts

Posted - 2006-08-04 : 02:15:36

OK Great its working ... :)

thanks lot dude ..

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-08-04 : 08:35:19

Just for the record, your real problem was that you used AND insted of OR. You now have a query that uses IN (), which is 'just' a shorthand for multiple ORs.
If you were asked to find a row where skill_ID were 1 AND 2 at the same time you (hopefully) would come up with none as well

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources