SQL Query and .Net code for Advanced search in my application ??

[rajendrsedhain]

Hi,

I have 1 checkboxlist, 5 dropdownlits and three textboxes.I have to write the SQL query and c# code for that advanced search.

<asp:CheckBoxList ID="reposotoryCheckBoxList" runat="server" Style="z-index: 106; left: 226px;position: absolute; top: 18px"> 
asp:ListItem Text="computer" Value="2"></asp:ListItem>
 
<asp:ListItem Text="math" Value="3"></asp:ListItem>
 
<asp:ListItem Text="science" Value="1"></asp:ListItem>--%>
 
 
</asp:CheckBoxList><asp:DropDownList ID="DropDownList1" runat="server"> 
<asp:ListItem>Anywhere</asp:ListItem>
 
<asp:ListItem>Title Words</asp:ListItem>
 
<asp:ListItem>Creator Name Words</asp:ListItem>
 
<asp:ListItem>Subject Words</asp:ListItem>
 
</asp:DropDownList>
 
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
 
<asp:DropDownList ID="DropDownList2" runat="server">
 
<asp:ListItem>and</asp:ListItem>
 
<asp:ListItem>or</asp:ListItem>
 
<asp:ListItem>not</asp:ListItem>
 
</asp:DropDownList>
 
<asp:DropDownList ID="DropDownList3" runat="server">
 
<asp:ListItem>Anywhere</asp:ListItem>
 
<asp:ListItem>Title Words</asp:ListItem>
 
<asp:ListItem>Creator Name Words</asp:ListItem>
 
<asp:ListItem>Subject Words</asp:ListItem>
 
</asp:DropDownList><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
 
<asp:DropDownList ID="DropDownList4" runat="server">
 
<asp:ListItem>and</asp:ListItem>
 
<asp:ListItem>or</asp:ListItem>
 
<asp:ListItem>not</asp:ListItem>
 
</asp:DropDownList>
 
<asp:DropDownList ID="DropDownList5" runat="server">
 
<asp:ListItem>Anywhere</asp:ListItem>
 
<asp:ListItem>Title Words</asp:ListItem>
 
<asp:ListItem>Creator Name Words</asp:ListItem>
 
<asp:ListItem>Subject words</asp:ListItem>
 
</asp:DropDownList><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
 

The dropdown list items are from different table, Title from tblTitle,Subject from tblSubject and createrName from tblName.

Any help will be highly appreciated.

Thanks

[Frinavale]

It's wonderful that you have to write an advanced search feature. You've created the interface for it but it appears that you haven't attempted to implement any functionality for searching.

The experts on this site are more than happy to help you with your problems... but they cannot do your assignment/program for you.

Attempt the program yourself first and post questions regarding any difficulties you have or about a particular function of the code that you don't know how to achieve.

-Frinny

[rajendrsedhain]

Hi Frinny,

Thanks for the reply. I did this and I have problem to select the different table from dropdownlist.

here is my sql query:(inside the App_Code)

public static DataSet GetReport(String repository, String name, String title, String subject)
{


String sql = " Select distinct col.ID,col.CollectionAbstract,col.Title,col.SortTi tle,col.Scope,col.CollectionIdentifier from tblCollections_Collections col" +
" LEFT JOIN tblCollections_CreatorIndex colCI " +
" ON col.ID = colCI.CollectionID " +
" LEFT JOIN tblCreators_Creators cr" +
" ON colCI.CreatorID = cr.ID " +
" LEFT JOIN tblCollections_SubjectIndex sbI " +
" ON col.ID = sbI.CollectionID " +
" LEFT JOIN tblSubjects_Subjects sub " +
" ON sbI.SubjectID = sub.ID " +
" where ( 1=1 )" + //
(repository == null || repository.Trim() == "" ? "" : " and col.RepositoryID in( " + repository + " )") +
(title == null || title.Trim() == "" ? "" : " and col.Title like '%" + title + "%'") +
(name == null || name.Trim() == "" ? "" : " and cr.Name like '%" + name + "%'") +
(subject == null || subject.Trim() == "" ? "" : " and sub.Subject like '%" + subject + "%'")+
" order by col.Title";




return SqlHelper.ExecuteDataset(GetConnectionString(), CommandType.Text, sql);
}



aspx.cs file contains:

protected void Button1_Click(object sender, EventArgs e)

{

try
{

String repository = "";
Boolean first = false;


if (reposotoryCheckBoxList.Items[0].Selected == true)
{
repository = " 1,3,5";
}

else
{
first = false;
for (int i = 0; i <= reposotoryCheckBoxList.Items.Count - 1; i++)
{
if (reposotoryCheckBoxList.Items[i].Selected == true)
{
if (first == false)
{
repository = reposotoryCheckBoxList.Items[i].Value.ToString();
first = true;
}
else
{
repository = repository + " , " + reposotoryCheckBoxList.Items[i].Value.ToString();

}
}
}

}

......My questions .....how to implement that 5 dropdownlists here..............???.......

string str = TextBox1.Text.ToString();
string str1 = TextBox2.Text.ToString();
string str2 = TextBox3.Text.ToString();
str = str.Trim();
str1 = str1.Trim();
str2 = str2.Trim();


//throw new Exception();
DataSet ds;
ds = DataAccess.GetReport(repository, str1, str, str2);
Repeater1.DataSource = ds;
Repeater1.DataBind();

}