How to make a search engine to search my database

hi there,

i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out

can anyone help?

thanks

When you say "help", do you mean provide assistance with errors that you are encountering on something you have already started, or do you mean "do it for you"? In other words, what have you done so far?

Also, what database are you using? Which language do you use?

|||

If you are using SQL Server or SQLEXPRESS both have FullText Search indexing built in. If there is no native FullText search in some other RDBMS that you are using then try DotLucene.

|||

i am using SQL Server 2005..

i used to study .asp and now i have to change to .aspx and i find tat everything is very different..

i am just starting to do the search engine thing..

and i need some guide... =/

|||

The principals are the same in terms of the SQL. Generally, you will use LIKE and wildcard characters %.

Beyond that, drag a textbox and button onto a form, then a GridView and a SqlDataSource. The GridView needs to be bound to the datasource, which should be set up to connect to your database, and run the SQL that will fetch the search results. Follow some of the Quick Start tutorials or videos to get the hang of working with the new controls.http://www.asp.net/learn/

|||

i manage to make everything and when i test query in the sql server it works..

but when i type in the keyword in the text box and i press submit it dint work.. i have done before the dropdown list where they have this autopostback thing.but for the text how do i code the submit button?

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

Dim searchAsString = TextBox1.Text

SqlDataSource1.Select("search")

GridView1.DataBind()

|||

try this, worked for me

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"

SelectCommand="SELECT * FROM [table] WHERE ([field] LIKE '%' + @.field1 + '%')

<SelectParameters>

<asp:ControlParameterControlID="TextBox2"DefaultValue="%"Name="field1"PropertyName="Text"/>

</SelectParameters>

</asp:AccessDataSource>

How to make a Fuzzy Search Engine

When i search for Peter.
Peter,Pan was successfully retrieved from the Database

But when i Search for Peter Pan.
It found no results.

How can i make it such tat it can find results for Peter Pan too?
In another words, how to make my search engine more powerful,fuzzy and intelligent?

My current Code:
<asp:SqlDataSource ID="dsSearch" runat="server" ConnectionString="<%$ ConnectionStrings:csHPDB %>"
SelectCommand="SELECT * FROM [employee_table] WHERE ([employee_name] LIKE '%' + @.employee_name + '%')
<SelectParameters>
<asp:QueryStringParameter Name="employee_name" QueryStringField="Search" Type="String" />
</SelectParameters>
</asp:SqlDataSource

Jabalang:

SelectCommand="SELECT * FROM [employee_table] WHERE ([employee_name] LIKE '%' + @.employee_name + '%')

If you want to use like searches only, then you'll have to separately code for each combination of words and/or phrases for your search. You can rather use Full Text Search for this and any other kind of fuzzy searches. Read BOL for this feature.

|||

what you mean? what is BOL? any links for this? thx

|||

ello?

|||

My apologies for the late reply. BOL means Books On-Line. What I meant was you can use a feature called Full Text Search to implement full text search. Like matches and other functions e.g. soundex have their own limitations. By setting Full Text search for your table and/or any of the columns, you can allow the users to search for any word or phrase ( just like google) and the results will be generated based on the full text index scan. Below are some links that can be useful for setting up full text search:

http://msdn2.microsoft.com/en-us/library/ms142571.aspx

http://msdn2.microsoft.com/en-us/sqlserver/aa336289.aspx

|||

oh.. cool.. so how do i implement it? any examples?

|||

Follow the links. Everything is in there explained very well.

|||

I'm using a Access Database .mdb format.

I tried using FREETEXT , but it didnt work.

Anyone has any idea to fix this problem?thx

|||

Jabalang:

I'm using a Access Database .mdb format.

You mean you're using Access as the database ? For this far, I was explaining you the feature of SQL Server.

|||

?yeah... i am using Microsoft Access As Database. So is there anyway to solve the problem?

|||

anyone help please... thx

|||

Sorry, but I don't use Access regularly so I don't have any idea as to whether its having this kind of feature or not.

|||

any body know how to make a fuzzy search engine? thx*2

|||

Hi Jabalang,

Based on my understanding, you want to build a search engine on your access database. However, since Full-Text-Index is not supported in access (I think it is only supported at Sql Server Enterprise Edition), you cannot use full-text index. If I've understand you wrong, please feel free to tell me, thanks.

I think in your case, if your bussiness logic does not involve too much data, you can use LIKE comparison. For example, if you want to search one record whose name is(or contains) "abc", you can write you select query like this:

select * from tbl_name where name LIKE '%abc%' For more information, click this link: http://msdn2.microsoft.com/en-us/library/ms179859.aspx

If your bussiness need a lot of data, or you have performance needs, I would suggest you use sql server enterprise instead (or at least standard version).

Hope my suggestion helps

|||

Bo - see here:http://forums.asp.net/t/1198035.aspx

How to login to SQL Server Web Data Administrator?

I have installed Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) and the SQL Server Web Data Administrator. After I run the SQL Server Web Data Administrator it
is asking me for login and password. How can I determine my login and password? Thank you.Try using "sa" for login (without the quotes) and a blank password. After you login, make sure you change the password to something non-blank!

How to limit amount of results in a search?

I'm busy writing a local site search engine that searches through a sqlserver database and I want to know how or what is the correct sqlsyntax to use in order to limit the amount of results a page loads at atime? The idea is obviously similar to something like google where youonly see a certain amount of results first and then click at the botomfor the next eg. 10 results.
The second question is how do I, after the first page with the firstset of results that were shown, "clear" the second page ofthe previous html in order to show the next set of results? To give you an idea what my code looks like at the moment. Please don'tkill me if the code is done a bit a lame, because I'm still learning.
<%@. Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<%@. import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;

strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();

strQuery = strProvince + " " +strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "+ strWantChildren;

SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(@."Server=THALION\THALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROMclient," + "FREETEXTTABLE( client, * , @.searchphrase ) searchTable "+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@.searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();

//start display of results
lblResults.Text = "<tablewidth='100%' style='border-style:solid; border-width:thin;border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();

// read the text file's info into a variable for display
if (File.Exists( MapPath( "text files" +"\\" +resTextDescription ) ) )
{

objStreamReader = File.OpenText( MapPath( "text files" +"\\"+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{

resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
resTextDescription2 = "myFile.txt does not exist!";
}
//determine whether male or female in order to display correct sign
if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correctwords for display
if ( resHaveChildren == "have kids" )
resHaveChildren = "Yes";
else
resHaveChildren = "No";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%' bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><tdcolspan='2'><div align='center'class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'bgcolor='#C7C9BE'><p align='center'><img src='images/" +resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><pclass='text'><br></p><p class='text'>" +resTextDescription2 + "</p><pclass='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'valign='top'><img src='images/hotlist_1.jpg' alt='Add ToFavorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><imgsrc='images/email_1.jpg' alt='Email this profile' width='42'height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'class='text_bold'>Location :</td><td width='37%'bgcolor='#E1E2DC' class='text'>" + resProvince +"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Age :</td><td bgcolor='#E1E2DC'class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Height : </td><td bgcolor='#E1E2DC'class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Home Language : </td><tdbgcolor='#E1E2DC'class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Relationship Status : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Realtionship Interest : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Have Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Want Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><tdbgcolor='#497792'> </td><td colspan='2'bgcolor='#678BA1'> </td><td colspan='2'bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><tdcolspan='2'> </td><tdcolspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;

//create a connection
conClient = newSqlConnection( @."Server=THALION\THALION;IntegratedSecurity=SSPI;database=DateGame" );
//select statement
strSelect = "Select userNameFrom [client] Where userName=@.username and userPassword=@.userpassword";
cmdSelect = new SqlCommand( strSelect, conClient );
cmdSelect.Parameters.Add( "@.username", txtNickName.Text );
cmdSelect.Parameters.Add( "@.userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>

Actually, you code is quite nice! quick suggestion, use the ? operator for stuff like:

code wrote:

if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg"

w/ the question mark operator it becomes

code wrote:

resGender = (resGender=="Male"?"Male_sign_1.jpg":"Female_sign_1.jpg");

as for how to impelment pagination, here's a linkHow to Implement a Repeater Control with Pagination