almost done but stuck - SQL Server Forums

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

Development Tools

ASP.NET

almost done but stuck

Author

Topic

quarinteen
Starting Member

40 Posts

Posted - 2007-10-17 : 19:16:32

Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so (there is not any C# or VB in this page or a .vb or .cs file)

created a new database with text indexing enabled and the following table

create table support
(problemId VARCHAR(50) NOT NULL PRIMARY KEY,
problemTitle varchar(50) NOT NULL,
problemBody text NOT NULL,
linkOne varchar(50),
linkTwo varchar(50),
linkThree varchar(50),
linkFour varchar(50), ftid int NOT NULL)

next

create fulltext catalog remoteSupportCatalog

create unique index ui_remotesupport ON support (ftid)

then

create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog

--------

I then populated some rows and issues a quesry

Select * from support where freetext(problemBody, 'test database')

it works pulls back all the data I expected it to pull back

In my asp page I created a database connection with the folling select command

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">

created the search parameter

<SelectParameters>

<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>

and it doesnt give me back an error or data it does nothing. What am I missing?????

here is the whole asp page

<%@ Page Language="C#" %>

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

</script>
<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>
</head>

<body bgcolor="#e4e4e4">

<form id="form1" runat="server">

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @srchBox ) ">

<SelectParameters>
<asp:Parameter Name="srchBox" />

</SelectParameters>

</asp:SqlDataSource>

<table style="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;" bgcolor="#000000">

<tr>

<td style="width: 475px; height: 125px">

<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"

CellPadding="4" DataKeyNames="problemId" DataSourceID="SqlDataSource1" ForeColor="#333333"

GridLines="None" Height="52px" Width="679px">

<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

<CommandRowStyle BackColor="#C5BBAF" Font-Bold="True" />

<EditRowStyle BackColor="#7C6F57" />

<RowStyle BackColor="#E3EAEB" />

<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

<Fields>

<asp:BoundField DataField="problemId" HeaderText="Id:" ReadOnly="True" SortExpression="problemId">

<ItemStyle Width="600px" BorderColor="White" BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="problemTitle" HeaderText="Description:" SortExpression="problemTitle" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="problemBody" HeaderText="Resolution:" SortExpression="problemBody" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="linkOne" HeaderText="Links:" SortExpression="linkOne" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="linkTwo" HeaderText="linkTwo" SortExpression="linkTwo" ShowHeader="False" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="linkThree" HeaderText="linkThree" SortExpression="linkThree" ShowHeader="False" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

<asp:BoundField DataField="linkFour" HeaderText="linkFour" SortExpression="linkFour" ShowHeader="False" >

<ItemStyle BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle BorderStyle="Solid" BorderWidth="1px" />

</asp:BoundField>

</Fields>

<FieldHeaderStyle BackColor="#D0D0D0" Font-Bold="True" />

<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</asp:DetailsView>

<asp:Label ID="Label1" runat="server" Font-Bold="True" ForeColor="White" Style="z-index: 103;
left: 13px; position: absolute; top: 59px" Width="521px"></asp:Label>

<asp:TextBox ID="srchBox" runat="server" Style="z-index: 101; left: 11px; position: absolute;
top: 31px" Width="520px"></asp:TextBox>

<asp:Button ID="Button1" runat="server" Style="z-index: 102; left: 555px; position: absolute;
top: 31px" Text="Search It" />

<asp:SqlDataSource ID="SqlDataSource2" runat="server"></asp:SqlDataSource>

</td>
</tr>

</table>

</div>

</form>
</body>

</html>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources