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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 |
|
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 databasewitht he following columnsproblemidproblemtitleproblrmbodylink1link2link3link4what 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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)) ReturnEND |
 |
|
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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 2000and I am using visual web designer 2005 express.I have one database aside from the default install named rsdbI have 1 table in the database names proglems there is 7 items in the databaseproblemid problemtitleproblembodylink1link2link3link4it connects with windows authenticationI 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 data1 This2 is 3 My 4 Search 5 Stringnow 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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
|