searching database through asp.net

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 2005 Forums

.NET Inside SQL Server (2005)

searching database through asp.net

Author

Topic

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 08:24:13

OK I have a search page and the query that is being send from the search box is

"SELECT * FROM [problems] WHERE ((problemBody LIKE '%' + @search_id + '%')OR @search_id IS NULL)"

Now say I have in the column for problemBody "Search the database" If i Type in the search field search the, or the database, or data, ot search, or even just s it will bring back records, But if I do not use exact keywords such as "search database" it will not bring back anything. How do I make it search all the keywords used?? like a normal search engine.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 08:47:15

use full text catalog search for this.
it's the best way.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 09:23:13

How do I do that?

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 09:35:50

Also I am using msde2000 with asp.net 2005

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 09:38:32

msde doesn't support full text search.

what you'll have to to then is split your input search string to a table and join to that table.
look here for a split function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 09:58:49

wow I dont under stand any of that is there any way you can break it down for me? I have one table in the database

witht he following columns
problemid
problemtitle
problrmbody
link1
link2
link3
link4

what would I have to split?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 10:02:19

the input string that is your search parameter.

SELECT *
FROM [problems]
join dbo.Split('yourSearchString', 'delimiter') on problemBody LIKE '%' + dataColumnFromSplitFunction + '%'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 10:47:03

that gives me invaluid object name dbo.split

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 10:50:22

here is the code for the aspx page

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Search.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RSDBConnectionString %>"
SelectCommand="SELECT * FROM [problems] WHERE ((problemBody LIKE '%' + @search_id + '%')OR @search_id IS NULL)" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:ControlParameter ControlID="searchBox" PropertyName="Text" Type="String" Name="search_ID" />
</SelectParameters>
</asp:SqlDataSource>

</div>

<asp:TextBox ID="searchBox" runat="server" Style="z-index: 100; left: 106px; position: absolute;
top: 140px" Width="488px"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Style="z-index: 101; left: 623px; position: absolute;
top: 140px" Text="Search" />
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2"
DataKeyNames="problemBody" DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="None"
Height="50px" Width="579px" style="z-index: 102; left: 107px; position: absolute; top: 198px">
<FooterStyle BackColor="Tan" />
<EditRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
<Fields>
<asp:TemplateField HeaderText="problem Id:" SortExpression="problemId">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("problemId") %>'></asp:Label>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("problemId") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("problemId") %>'></asp:Label>
</ItemTemplate>
<ItemStyle BorderStyle="Double" BorderWidth="2px" HorizontalAlign="Left" Width="325px" />
<HeaderStyle BorderStyle="Double" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:TemplateField>
<asp:BoundField DataField="problemTitle" HeaderText="problemTitle" SortExpression="problemTitle" >
<ItemStyle BorderStyle="Double" BorderWidth="2px" Width="325px" />
<HeaderStyle BorderStyle="Solid" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:BoundField>
<asp:BoundField DataField="problemBody" SortExpression="problemBody" >
<ItemStyle HorizontalAlign="Left" Width="325px" />
<HeaderStyle HorizontalAlign="Left" />
<FooterStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="link1" HeaderText="link1" SortExpression="link1" >
<ItemStyle BorderStyle="Solid" BorderWidth="2px" Width="325px" />
<HeaderStyle BorderStyle="Solid" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:BoundField>
<asp:BoundField DataField="link2" HeaderText="link2" SortExpression="link2" >
<ItemStyle BorderStyle="Double" BorderWidth="2px" Width="325px" />
<HeaderStyle BorderStyle="Solid" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:BoundField>
<asp:BoundField DataField="link3" HeaderText="link3" SortExpression="link3" ReadOnly="True" >
<ItemStyle BorderStyle="Solid" BorderWidth="2px" Width="325px" />
<HeaderStyle BorderStyle="Solid" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:BoundField>
<asp:BoundField DataField="link4" HeaderText="link4" SortExpression="link4" >
<ItemStyle BorderStyle="Solid" BorderWidth="2px" Width="325px" />
<HeaderStyle BorderStyle="Solid" BorderWidth="2px" />
<FooterStyle BorderStyle="Solid" BorderWidth="2px" />
</asp:BoundField>
</Fields>
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:DetailsView>
<asp:Label ID="Label2" runat="server" Style="z-index: 104; left: 110px; position: absolute;
top: 162px" Width="487px"></asp:Label>

</form>
</body>
</html>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 11:00:54

dbo.Split is the function you need to crete in your database. you get it at the link i gave you.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 11:17:09

ok so do I put it in the asp code or do I open up the osql interface and put it there? and is this what I put there? Sorry I am not good at any of this.

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 11:22:56

the function goes into the database.

download sql server management studio express.
you can connect to MSDE with it nad work with your database from gui

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 11:50:26

is there one for msde 2000?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 12:09:00

no

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 12:10:17

ok i dont get the dbo error anymore but if I use
SELECT *
FROM [problems]
join dbo.Split('yourSearchString', 'delimiter') on problemBody LIKE '%' + dataColumnFromSplitFunction + '%'

I get a new error invalid column name 'dataColumnfromsplitfunction'

SELECT *
FROM [problems]
join dbo.Split('yourSearchString', 'delimiter') on problemBody LIKE '%' + dataColumnFromSplitFunction + '%'

and if I use this it does the same as before I can search but it has to be in order so I can get restults from t, or , test , or database from the column "testing the database" but I cant use test database, or database test or how ever it might pick it up.

SELECT *
FROM [problems]
join dbo.Split('problemBody', 'delimiter') on problemBody LIKE '%' + @search + '%'

Witht he sql code I just copied it exactly how it was in the post and pasted it. I didnt see where I might need to change data

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 12:25:33

ok first of all you have to understand how the split function works.
what does this return you:
select * from dbo.Split(@search , '<delimiter>')

change <delimiter> to a character on which you want to split your string. most likely a space so you should put
' ' instead of '<delimiter>'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 12:50:46

it return what ever I type in as the string

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 12:59:01

provide some sample data and how you're using it.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 14:05:54

Well if it type in data it return 1 row with data in it. It I type in ioioio it comes back with ioioio

the set up I have it really basic

I have msde 2000
and I am using visual web designer 2005 express.
I have one database aside from the default install named rsdb
I have 1 table in the database names proglems
there is 7 items in the database
problemid
problemtitle
problembody
link1
link2
link3
link4

it connects with windows authentication

I am testing the statement through visual webs details view configure datasource.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 14:17:15

ok i feel like we're going in circles...

let's review once again.
you have a search string @SearchString = "This is My Search String"
% + @SearchString + % won't work because this assumes that @SearchString is a complete word.
It doesn't know about your spaces in there.
to search on all keywords you have to split your @SearchString with the split function you've found.
the split function will return a table in this case with rows:
id data
1 This
2 is
3 My
4 Search
5 String

now you have to join your table that you're searching through to the table returned by the split function.

What exactly don't you understand about this whole concept?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

quarinteen
Starting Member

40 Posts

Posted - 2007-10-12 : 14:32:45

I understand the concept but I dont understand how to do it. I know little sql, no vb and no asp. I know C# a bit but not at an advanced level I was just hoping this could be done in a very easy manner

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources